This Excel tutorial explains the use of Application Volatile Method to recalculate custom Function (User Defined Function).
You may also want to read:
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.
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