Excel VBA Application Volatile Method to force recalculate

This Excel tutorial explains the use of Application Volatile Method to recalculate custom Function (User Defined Function).

You may also want to read:

Excel Workbook Calculation Automatic and Manual

Excel VBA Application Volatile Method

In Excel VBA, you can create your own Functions, we call it User Defined Function (UDF), usually I simply call it custom Function. Function is a Procedure that can take argument and return a value.

There are two kinds of custom Functions.

The first kind does not need to reference to other Cells, the Function can have an argument with constant value or without an argument. For example, you can use Rnd Function inside a custom Function to generate a number, therefore you don’t need any reference.

The second kind needs to reference to other Cells and return a value. For example, you need to reference to a Range when you use SUM Function.

Excel uses a special mechanism to minimize the number of calculation to speed up the process.  Excel builds “Dependency Trees”, which keeps track on the following changes

• Formulae/Names that have changed.


• Formulae containing Volatile Functions


• Formulae dependent on changed or volatile formulae or cells or names

Navigate to Excel Options to turn Workbook Calculation to Automatic,  then your formula Cell will recalculate automatically if the relationship is in the Dependency Trees.

According to the above rules, Functions that do not depend on other changed cells would not recalculate, unless Application Volatile Method is used.

To illustrate the above concepts, I refer to the custom Functions I previously wrote.

Custom Function that do no require Application Volatile Method

In the previous post, I wrote a custom Function to count substring in a string, for example, it can count how many “p” appears in “apple”.

Public Function wCountSubStr(str As String, substr As String) As Integer
    lenstr = Len(str)
    lensubstr = Len(substr)
    
    For i = 1 To lenstr
        tempString = Mid(str, i, lensubstr)
        If tempString = substr Then
            count = count + 1
        End If
    Next i
    wCountSubStr = count
End Function

This Function requires two parameters, if you refer the argument to a Cell, the Function would recalculate automatically when the Cell changes.

For example,  the formula recalculates every time A1 value is changed.

=wCountSubStr(A1,”p”)

Custom Function that requires Application Volatile Method

In the below example, the Function does not have any argument and the result depends on A1.

Function test()
    test = Range("A1").Value * 2
End Function

Excel cannot recognize you are referencing A1 value because Excel builds the Dependency Tree using reference in arguments but not VBA. So even if you change A1, the formula would not recalculate automatically.

If you insert Application Volatile Method in this Function, this Function will recalculate whenever a Cell is edited, regardless of whether it is Cell A1 or not, therefore Application Volatile should be avoided if possible as it slows down Excel performance.

Function test()
    Application.Volatile
    test = Range("A1").Value * 2
End Function

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff195441.aspx

Excel VBA Application Volatile Method to force recalculate