The Application has a Volatile method which you can use in your user defined functions (UDF’s) to force the UDF to calculate whenever the sheet calculates. Many users misunderstand this method, so I want to set the record straight on it and tell you when I use it.

The Volatile method, when set to True in a UDF, will cause the UDF to execute when the sheet from which it is called recalculates. Normally, a function only executes when one of it’s arguments is changed. This function

Function AddTwo(Rng As Range) As Double

End Function

will only execute when Rng is changed. By adding

Application.Volatile True

to the function, it will recalculate whenever the sheet recalcs, not just when Rng is changed. Don’t be fooled into thinking that because you add this line that the function will always show the correct results. We saw in the SumReds post that changing the interior color of a cell doesn’t trigger a recalculation. If the sheet doesn’t recalc, it doesn’t matter if you have Application.Volatile or not, the UDF will not execute.

So when do you use Volatile? Almost never. If you want your UDF to always show the correct result, include everything you need as arguments to the function. In AddTwo() above, everything this function needs is in the arguments. If Rng changes, it recalculates. If something other than Rng changes, it doesn’t and shouldn’t.

Using Volatile sparingly will afford you two benefits. Your worksheet will recalculate faster because it’s only recalculating what it needs to. And UDF’s are notoriously slow compared to built-in functions. The other advantage is that your code is more self-documenting. I should be able to tell from the function name and the argument names just what the function does. You wouldn’t know what this function does

Function AddTwoRanges(cell As Range) As Double

End Function

If Range(“A1”) changes, this function doesn’t execute because Range(“A1”) isn’t an argument. You could make it Volatile, but the user still can’t tell what the function does. And there’s the whole calculation time thing. If you really want to add a cell to Range(“A1”), include two Range arguments to the function. You’ll end up with a more flexible function that could be used for other things and users who understand what’s going on.

Certainly there are times when making a function Volatile is necessary. Excel’s NOW() and INDIRECT() functions are volatile, and rightly so. If you think your function needs to be Volatile, make sure you think through your inputs and arguments. Sometimes it’s just necessary, but use it judiciously.

Thanks for the great post suggestion, Vincent.