Excel Calculation issues with UDF

[nikki333]

Hi folks

I wrote some User-defined-functions (UDF) that work well in xlCalcualtionAutomatic Mode, however, things are getting too slow and it's not needed to recalculte all the time.

So I'm trying to switch to xlCalculationManual for the whole workbook.

The problem is that the UDF's won't be recalculated most of the time.

So far I've been trying with:

   - Application.CalculateFullRebuild (That works apparently only one time while opening the file in the File Open event)

   - wsMyWorksheet.ForceFullCalculation (Force full calculation on the worksheet in question...nothing happens)

   - wsMyWorksheet.CalculateFull (Nothing happens)

   - wsMyWorksheet.SpecialCells(xlCellTypeFormulas).dirty
     wsMyWorksheet.specialCells(xlCellTypeFormulas).CalculateFull (nothing happens)

   - Application.Calculate
     Do 
     Doevents
     Loop Until Application.CalculationState = xlDone (nothing happens, xlDone will never be achieved???)

     With debug.print .... xlDone will always be 0 (ie. Calculating) or 2 (ie. Pending)

The actual issue in my case is that i'm trying to loop through a matrix of Items (eg. Produkts 1-20 in rows, and the same Produkts 1-20 in columns)

In this case there must be 400 recalculations, and for each of them the columns in question must be recalculated before the result value is stored in an array.

However, the code runs faster than the calculation, so the values stored in my array are not up-to-date/or simply wrong.

Therefore, I was trying with Doevents, but that seems to be ignored as well.


Hope that is not too confusing.

cheers and thanks for any help

[nikki333]

FYI i've found a workaround that doesn't involve any of the Application.Calculation... options and it's much faster in my case

myRange.replace What:="=", Replacement:="="

This acts like entering each formula in myRange again

cheers

[offthelip]

I have had the same probelm and i got around by putting this code inthe VBA, it forces a recalculation:

Worksheets("Sheet1").EnableCalculation = False

Worksheets("Sheet1").EnableCalculation = True

This should be faster than reentering oll the formulae

[nikki333]

Thanks for the reply...i've tried that too. But the macro became reaaaallyyy slow, and the outcome values were a bit random