UDF, dependency sequence, IsEmpty() & Multi-areas ranges

[Thomas]

Hi there,
I have a UDF that takes a long time to execute and I need to make sure
it is not evaluated more than required per workbook calculation.
I found on www.decisionmodels.com/calcsecretsj.htm that I can use
IsEmpty(arg) to detect that my inputs arguments are 'dirty' (i.e. have
not been recalculated yet) and therefore that it is not worth
performing the full procedure and rather exit early, and wait to be
called again when the argument is 'clean':

Public Function myfunction(arg1 As Range, arg2 As Range)
Debug.Print "entry"
If IsEmpty(arg1) Or IsEmpty(arg2) Then
Debug.Print "exit on dirty"
Exit Function
End If
' ...proper procedure there
Dim inc As

It seems to work fine on simple inputs arguments, yet it seems to be
ineficient if the input argument is a multi areas range: IsEmpty
returns false although I'm sure some of the cells have not been
recalculated yet.
Would it be enough to test all the areas in the input? What about
multi-cell ranges, do I need to test every single cell?

Is there a more efficient way to optimise excel recalculation
sequence?

Thanks

Thomas

 

[Charles Williams]

Hi Thomas,

I would not recommend trying to handle multi-area ranges with UDFs, there is
a serious excel calculation bug.

If your ranges are multi-cell ranges then to be safe you need to look at all
the cells in the range.
(also decide whether to exit if ANY of the cells are uncalculated, or only
if ALL the cells are uncalculated)

For multi-cell ranges COUNTA can be an efficient way of counting the number
of non-empty/uncalculated cells in a range, or you can loop through each
cell in the range testing with ISEMPTY.

If your input ranges could refer to cells that really are empty as opposed
to uncalculated then you cannot use COUNTA, you have to use ISEMPTY and
Len(cell.formula)>0 (suggested by David Cuin).

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Melanie Breden]

Hi Thomas,

I have a UDF that takes a long time to execute and I need to make sure
it is not evaluated more than required per workbook calculation.
I found on www.decisionmodels.com/calcsecretsj.htm that I can use
IsEmpty(arg) to detect that my inputs arguments are 'dirty' (i.e. have
not been recalculated yet) and therefore that it is not worth
performing the full procedure and rather exit early, and wait to be
called again when the argument is 'clean':

Public Function myfunction(arg1 As Range, arg2 As Range)
Debug.Print "entry"
If IsEmpty(arg1) Or IsEmpty(arg2) Then
Debug.Print "exit on dirty"
Exit Function
End If
' ...proper procedure there
Dim inc As

It seems to work fine on simple inputs arguments, yet it seems to be
ineficient if the input argument is a multi areas range: IsEmpty
returns false although I'm sure some of the cells have not been
recalculated yet.
Would it be enough to test all the areas in the input? What about
multi-cell ranges, do I need to test every single cell?

Is there a more efficient way to optimise excel recalculation
sequence?

try this:

If Application.Min(arg1) = 0 And Application.Max(arg1) = 0 And _
Application.Min(arg2) = 0 And Application.Max(arg2) = 0 Then
Exit Function
End If


--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Thomas]

Thanks to both of you
I read about the multi area range argument problems, I'll have to
investigate further...From the decision models page, the desription
suggest that Ctrl Alt F9 will trigger the issue, whereas Shift F9
should be fine. We recommend only Shift F9 to our users.
Not ideal, but how can I pass 150 parameters of mixed types and sizes
and benefit from the recalculation power of excel?


Not sure about the Min Max thing as I may have arrays of
strings...wouldn't min max always return 0 for these? Yet the cells
might be dirty.

What is the effect of exiting the function if all the arguments are
clean? I suppose Excel would not call you again.

Thanks again

Thomas

 

[Charles Williams]

Hi Thomas,

Using Shift F9 only calculates the activesheet and ignores any intersheet
dependencies: basically this is likely to give the wrong answer unles you
have no references from any worksheet to any other worksheet.
I would not recommend this unless your workbook is setup extremely carefully
and your users cannot enter any formulae.

If you exit the function when all the input arguments have been calculated
then the function will not be called again.

Pass your 150 parameters in a small number of contiguous ranges (in the
extreme you could pass one range per input worksheet) in a defined structure
which may also include cells you dont need and then read the pieces you want
into your 150 parameters internally. If the unneccessary cells could contain
formula or changed data (ie could be dirtied) then I think you would need to
store your results in a static variable/array that you returned if all your
parameters had been calculated but some of the unneccessary cells had not.
Its probably worth reorganising your worksheets a little to simplify this.

Using Min/Max will also be more time expensive than Counta.

Also you can substantially reduce the number of repeated calls to the
function by optimising the worksheet calculation sequence.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm