Excel conversion order - why does the dependent cell calculate before the previous cell?

I have a strange problem with a calculator that depends on the order of Excel calculations, which is calculated before the previous cell with an empty parameter value when setting the range. Is there any way to detect these “fake” calls? This is very important for me, as I run some kind of smart business logic in a real application, and these “fake” calls break the whole logic. In addition, these “fake” calls, if registered in RTD, do not attach it to the topic correctly and, therefore, do not report the topic being disabled.

Excel 2010/32-bit latest.

To illustrate the problem, I created a simple function:

Function Fnc(param2 As String) As String
    Fnc = Application.ThisCell.Address
    Debug.Print (Application.ThisCell.Address & " - " & param2)
End Function

And the procedure:

Sub SetDirty()
    Range("B1:C1").Dirty
End Sub

And a simple sheet:

$ A $ 1 = fnc (A1) = fnc (B1)

into cells A1, B1, C1.

:

$A $1 $B $1 $C $1

(CTRL + Alt + Shift + F9), , :

$B $1 - $A $1

$C $1 - $B $1

, . SetDirty:

$C $1 - & ---- ---- ?

$B $1 - $A $1

$C $1 - $B $1

. , , , .

+5
1

: Excel , LIFO ( ) . - .
UDF, IsEmpty
. http://www.decisionmodels.com/calcsecretsj.htm UDF http://www.decisionmodels.com/calcsecretsc.htm. Excel.

BTW Range.Dirty -

+6

All Articles