## Volatile Dependencies, Indirect Dependencies, False Dependencies – When Dependencies Don’t Work the Way You Think They Should

Its always a convenient shorthand to say that UDFs and formulas are recalculated when one of their arguments (or a precedent further upstream in the calculation chain) changes.

But in fact that turns out to be a bit of an oversimplification of how Excel works.

## The Test Setup

I have 2 UDFs in a standard VBA module: I have used F9 in the VBE to switch to debug mode whenever either of these UDFs execute.
The first UDF (Depends) has 2 arguments (Arg1 and Arg2), but only the first of them (Arg1) is actually used by the UDF. The second UDF (Depends2) uses both the arguments.

The Excel sheet has 2 sets of data for Arg1 and Arg2 and then calls both the UDFs. Calculation is set to Automatic. The result of Depends is 6, and of Depends2 is 24.

When you press F9 nothing happens because nothing has changed to cause a recalculation.

## Changing Upstream Precedents

• When you change cell A2 from 1 to 2 the Depends2 UDF calculates first and then Depends calculates second (assuming you entered the Depends formula in D3 before the Depends2 formula in D6 – Excel calculates formulas last entered first calculated unless this sequence gets changed by dependencies or other factors).
The values change from 24 to 25 and from 6 to 7.
• If you change cell A2 from 2 to 2 nothing happens – Excel recognises that nothing has changed.
• When you change B2 from 5 to 50 both UDFs recalculate, even though Depends does not need to since its result is not dependent on B2.

I call the Arg2 dependency in the first UDF (Depends) a False Dependency since its not actually needed.

## Volatile Dependencies

Things work differently if you make one of the dependencies volatile. Lets change cell B2 to =RAND()*100

As expected both UDFs recalculate.

Now press F9 to recalculate again without changing anything else.

Depends2 recalculates, but Depends does NOT recalculate even though a value in Arg2 of the Depends UDF has changed.

In other words if the False Dependency is Volatile it is ignored in a recalculation.
This also happens with built-in Excel functions like INDEX().
If A1 contains =NOW(), and A2:A5 contain the numbers 2 to 5 then

• =INDEX(A1:A5,1,1) is directly dependent on volatile cell A1 and will always be recalculated.
• =INDEX(A1:A5,3,1) is only indirectly dependent on volatile cell A1 and will NOT always be recalculated, but it will be recalculated once if for example cell A5 is changed even though the answer will not change

I call Volatile False Dependencies Indirect Dependencies.

## EVALUATE and Volatile Dependencies

Stephen Gersuk discovered what looks like another bug with the EVALUATE method and volatile dependencies.

If you have a UDF like this:
``` Function MySum2(r As Range) As Double MySum2 = Evaluate("sum(" & r.Address(External:=True) & ")") End Function ```
then it does not get recalculated when it has a volatile precedent and you press F9.
So this case gives you the wrong answer, because its not a true False Volatile Dependency: the result really does depend on the argument.

You can bypass this bug by adding anything that references the Value of a cell in the argument:``` Function MySum2(r As Range) As Double If IsEmpty(r) Then Exit Function MySum2 = Evaluate("sum(" & r.Address(External:=True) & ")") End Function ```But just referenceing properties of the range object is not sufficient:``` Function MySum3(r As Range) As Double Dim strAdd As String strAdd = r.Address(External:=True) MySum3 = Evaluate("sum(" & strAdd & ")") End Function ```MySum3 has the same problem.

## False Dependencies and Calculation Sequence

It has been suggested that you can use False Dependencies to control the sequence in which Excel calculates formulas.

This is a dangerous idea because false dependencies on uncalculated cells cannot be recognised by Excel since it does not get a chance to discover that they are uncalculated.

## Conclusion

• Yet more reasons to avoid Volatile Functions!
• Another EVALUATE bug!

Do you have any bad experiences with volatile functions?

This entry was posted in Calculation, UDF, VBA. Bookmark the permalink.

### 4 Responses to Volatile Dependencies, Indirect Dependencies, False Dependencies – When Dependencies Don’t Work the Way You Think They Should

1. Harlan Grove says:

Not so easy to avoid volatile functions in 2 specific cases. Current date and possibly time (TODAY, NOW). Yes, setting a named constant in an Open event could substitute, but it requires macros be enabled (and it fails when files remain open after midnight). Second, 3D referencing in functions not intended to accept them, such as SUMIF[S] etc, in which it’s necessary to use arrays of worksheet names inside INDIRECT calls. Annoying that these functions don’t support 3D references since NPV does, but MSFT has seemed much more interested in new features for the mathematically disinclined since Excel 2K.

• fastexcel says:

It is curious that some functions accept 3D references but most do not. From a UDF perspective (XLL or VBA) I have never worked out how to handle 3D references, but it must be possible because Laurent Longre managed it in MoreFunc THREED …

2. Keith says:

The C SDK let’s you handle 3D references. It is an XLOPER12 of xltype xltypeRef. val.mref.lpmref->count tells you how many XLREF12’s you will see at val.mref.lpmref->reftbl.