Table of Contents

Volatile Functions

By default UDF’s (User Defined Functions) in Excel VBA are not volatile. They are only recalculated when any of the function’s arguments change. A volatile function will be recalculated whenever calculation occurs in any cells on the worksheet. Let’s take a look at an easy example to explain this a bit more.

1. Open the Visual Basic Editor and click Insert Module.

Create a function called MYFUNCTION which returns the sum of the selected cell and the cell below this cell.

2. Add the following code lines:

Function MYFUNCTION(cell As Range)

MYFUNCTION = cell.Value + cell.Offset(1 0).Value

End Function

3. Now you can use this function just like any other Excel function.

Non-volatile Function in Excel VBA

4. This is a non-volatile function. Non-volatile functions are only recalculated when any of the function’s arguments change. Change the value of cell B2 to 8.

Function's Argument Changes

5. Now change the value of cell B3 to 11.

Any Other Cell Changes

Explanation: the non-volatile function is not recalculated when any other cell on the sheet changes.

6. Update the function as follows to make the function volatile:

Function MYFUNCTION(cell As Range)

Application.Volatile
MYFUNCTION = cell.Value + cell.Offset(1 0).Value

End Function

7. Change the value of cell B3 to 12.

Result:

Volatile Function in Excel VBA

Note: you need to enter the function again to make it volatile (or refresh it by placing your cursor in the formula bar and pressing enter).

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog