UDF: Volatile vs Not

[eggyjla]

When creating a UDF, is it preferred to make it volatile or NOT. What are the pros/cons of volatile functions. I have a couple UDFs that each are similar. One updates anytime any of the variables changes and the other requires Ctrl+Alt+F9 or Application.Volatile added in the function in order to update.
The following will update anytime Var1 or Var2 are modifed.

Function Var4(Var1 As Double, Var2 As Double) As Variant
Dim WF As WorksheetFunction
Set WF = WorksheetFunction
Dim Rng2 As Range
Dim d As Double

d = 1

On Error Resume Next
d = WF.VLookup(Var1, Range("Rng2"), 2, 0)

Var4= (Var1* 1.38 / d + Var2) / (Var1* 1.38 / (d * 0.8) + Var2

End Function

In this function, the values are not updated as Var1 or Var2 are modifed. Application.Volatile or Ctrl+Alt+F9 are required to update the value.

Function Var1Var2(Var1 As Double, Optional Var2) As Variant
Dim WF As WorksheetFunction
Set WF = WorksheetFunction
Dim Rng As Range

If IsMissing(Var2) Then Var2= 1

Var2= WF.VLookup(Var1, Range("Rng"), 2)

Var1Var2= Var1 * 1.38 / Var2

End Function

 

[Joe Was]

A Volatile function is re-calculated every time the sheet is re-calculated and is dynamic in responce to changes on the sheet.

A non-Volatile function will only update the value shown in its cell if it is re-Entered!
It is dead to the other changes on that sheet, unless you update that UDF directly or one of its inputs are directly changed, within that UDF's cell!

 

[eggyjla]

What makes a UDF volatile or not? ie what makes Var1Var2 non-volatile? How do you detect or determine if a UDF is or isn't volatile?

 

[Joe Was]

Just under the Function statement and any defines add the line:

Application.Volatile

and your function will be "Volatile!"

 

[eggyjla]

How does one know when to add this line in the UDF? Is there a way to determine if a UDF will provide continuous updates or refresh the value when any of the input variables change thereby not requiring the Application.Volatile command?

 

[Norie]

eggyjla

It's really up to you and depends on the purpose of the UDF, VBA won't be able to determine whether a UDF should be volatile or not.

 

[Joe Was]

A UDF is a Custom Sheet Function and like the built-in worksheet functions your Custom UDF's can be made to function just like the built-in ones. The built-in ones will update their values as things change on your sheet according to how you have that workbook's "Calculation" option set.

To make your custom UDF work this way, the way the Built-in ones work, you add the code line:

Application.Volatile

to your custom UDF. And, as Norie, has pointed out you may be using your custom UDF in ways that should not be Dynamic with all worksheet changes. That is, you may want to control when and how the custom UDF is updated. This is why we have the option of making custom UDF's pasive or active in relation to sheet changes!

 

[tusharm]

A properly written UDF should almost never be volatile. After reading your post I had intended to post a short write up on UDFs (and still will) but in the meantime I found http://www.dailydoseofexcel.com/archives/2004/06/22/volatile-functions/

When creating a UDF, is it preferred to make it volatile or NOT. What are the pros/cons of volatile functions. I have a couple UDFs that each are similar. One updates anytime any of the variables changes and the other requires Ctrl+Alt+F9 or Application.Volatile added in the function in order to update.
The following will update anytime Var1 or Var2 are modifed.

Function Var4(Var1 As Double, Var2 As Double) As Variant
Dim WF As WorksheetFunction
Set WF = WorksheetFunction
Dim Rng2 As Range
Dim d As Double

d = 1

On Error Resume Next
d = WF.VLookup(Var1, Range("Rng2"), 2, 0)

Var4= (Var1* 1.38 / d + Var2) / (Var1* 1.38 / (d * 0.8) + Var2

End Function

In this function, the values are not updated as Var1 or Var2 are modifed. Application.Volatile or Ctrl+Alt+F9 are required to update the value.

Function Var1Var2(Var1 As Double, Optional Var2) As Variant
Dim WF As WorksheetFunction
Set WF = WorksheetFunction
Dim Rng As Range

If IsMissing(Var2) Then Var2= 1

Var2= WF.VLookup(Var1, Range("Rng"), 2)

Var1Var2= Var1 * 1.38 / Var2

End Function