Application.Volatile

[LikeButtah]

If I simply remove Application.Volatile from this custom function will it still re-calc after every change ? Or do I have to declare it like Application.Volatile (False) ? Thanks.

Function CalcHours(OffName As String, CalcDate As Date, PayType As String)
    Application.Volatile


' Takes OfficerName(Last,First), DayWorked, Tier


    Dim hours As Double
    Dim Result As Variant
    Dim LName As String
    Dim rng As Variant
    Dim EndDate As String
    Dim CurDay As String
    Dim Tour As Integer
    Dim ResultCol As Integer
    Dim PostType As String
    Dim post As Variant

 

[WarPigl3t]

You have to declare it. Let me explain. Say you have your custom function in cell A1, and you do not have Application.Volatile written. If someone makes a change to any cell except A1, the function will not update. So if someone makes a change to B1, the function will not update. The function will only update if you select A1 and put the curser inside of the formula and hit enter as if it was the first time you entered the function. Application.Volatile will update the function after any change in the sheet.

 

[RickXL]

Sorry but I think WarPigl3t is not quite right.

Here is what Microsoft says: https://msdn.microsoft.com/en-us/library/office/ff195441.aspx

If I had this data:

Excel 2013
	A	B	C	D
1	10	3	3	5.333333
Sheet1

Cell Formulas
Range	Formula
D1	=mm(A1,B1,C1)

with this User-Defined Function:

Function MM(v1 As Variant, v2 As Variant, v3 As Variant) As Variant
    MM = (v1 + v2 + v3) / 3
    MsgBox MM
End Function

Then every time I change A1, B1 or C1 I will see the MsgBox because the Function has been called but if I change any other cell then the Function will not be called and the MsgBox will not appear.

Now, if I changed the code to:

Function MM(v1 As Variant, v2 As Variant, v3 As Variant) As Variant
        Application.Volatile
        MM = (v1 + v2 + v3) / 3
    MsgBox MM
End Function

Then whichever cell on the worksheet was changed the Function would run even though it is not directly affecting the data in the Function.

So I suspect that you will be able to just delete that line.

Volatile Functions are deprecated because they can gobble up processing power in a large spreadsheet.

Some functions need to be volatile. For instance, the NOW() function returns the time. It updates itself every time the worksheet re-calculates but most functions do not need to be volatile.

Here is some more on the subject:
http://www.decisionmodels.com/calcsecretsi.htm
http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/


Regards,

 

[mikerickson]

If I simply remove Application.Volatile from this custom function will it still re-calc after every change ? Or do I have to declare it like Application.Volatile (False) ? Thanks.

What happened when you tried it?

 

[LikeButtah]

No appreciable difference in both calc time and system resources that I see (I just removed the Application.Volatile). But I was curious to have those more experienced than me let me know if I needed to declare it with a False statement or could just remove and still be able to calc correctly which from what I can see it does.