UDF's don't auto-calculate

[Jon von der Heyden]

Hi,

I regularly use a few UDFS obtained through this board and Ozgrid. Some of these (maybe all, not sure though), don't automatically calculate with the rest of the formula in the worksheet. If any of their dependents change I have to edit the cell that the UDF is in and exit again before it recalculates. Does anybody know why this is the case and how I can get these to automatically calculate?

Cheers,
Jon

 

[Andrew Poulsom]

How do you define dependants? Are they passed as arguments to the UDF?

 

[parry]

It depends upon a few factors and without you posting the function in question its impossible to say.

Some actions do not cause a recalculation. For example, if you had a UDF that looked at the colour of cells, and you amended the colour of these referenced cells then the formula would not recalulate. Only editing the cell with the function or the inclusion of an Application.Volatile statement (which recalcs when other functions cause a recalc) would cause a recalc. The Today() function is an example of a volatile function.

 

[Jon von der Heyden]

Andrew / Parry - Thanks for the responses...

In particular here is one of the UDF's (indeed it does read off color). How do I include an application.volatile statement into this?

Thanks,
Jon

Function CellColor(rCell As Range, Optional ColorName As Boolean)
Dim strColor As String, iIndexNum As Integer

'Written by Dave Hawley of OzGrid.com
Select Case rCell.Interior.ColorIndex
   Case 1
    strColor = "Black"
    iIndexNum = 1
   Case 53
    strColor = "Brown"
    iIndexNum = 53
   Case 52
    strColor = "Olive Green"
    iIndexNum = 52
   Case 51
    strColor = "Dark Green"
    iIndexNum = 51
   Case 49
    strColor = "Dark Teal"
    iIndexNum = 49
   Case 11
    strColor = "Dark Blue"
    iIndexNum = 11
   Case 55
    strColor = "Indigo"
    iIndexNum = 55
   Case 56
    strColor = "Gray-80%"
    iIndexNum = 56
   Case 9
    strColor = "Dark Red"
    iIndexNum = 9
   Case 46
    strColor = "Orange"
    iIndexNum = 46
   Case 12
    strColor = "Dark Yellow"
    iIndexNum = 12
   Case 10
    strColor = "Green"
    iIndexNum = 10
   Case 14
    strColor = "Teal"
    iIndexNum = 14
   Case 5
    strColor = "Blue"
    iIndexNum = 5
   Case 47
    strColor = "Blue-Gray"
    iIndexNum = 47
   Case 16
    strColor = "Gray-50%"
    iIndexNum = 16
   Case 3
    strColor = "Red"
    iIndexNum = 3
   Case 45
    strColor = "Light Orange"
    iIndexNum = 45
   Case 43
    strColor = "Lime"
    iIndexNum = 43
   Case 50
    strColor = "Sea Green"
    iIndexNum = 50
   Case 42
    strColor = "Aqua"
    iIndexNum = 42
   Case 41
    strColor = "Light Blue"
    iIndexNum = 41
   Case 13
    strColor = "Violet"
    iIndexNum = 13
   Case 48
    strColor = "Gray-40%"
    iIndexNum = 48
   Case 7
    strColor = "Pink"
    iIndexNum = 7
   Case 44
    strColor = "Gold"
    iIndexNum = 44
   Case 6
    strColor = "Yellow"
    iIndexNum = 6
   Case 4
    strColor = "Bright Green"
    iIndexNum = 4
   Case 8
    strColor = "Turqoise"
    iIndexNum = 8
   Case 33
    strColor = "Sky Blue"
    iIndexNum = 33
   Case 54
    strColor = "Plum"
    iIndexNum = 54
   Case 15
    strColor = "Gray-25%"
    iIndexNum = 15
   Case 38
    strColor = "Rose"
    iIndexNum = 38
   Case 40
    strColor = "Tan"
    iIndexNum = 40
   Case 36
    strColor = "Light Yellow"
    iIndexNum = 36
   Case 35
    strColor = "Light Green"
    iIndexNum = 35
   Case 34
    strColor = "Light Turqoise"
    iIndexNum = 34
   Case 37
    strColor = "Pale Blue"
    iIndexNum = 37
   Case 39
    strColor = "Lavendar"
    iIndexNum = 39
   Case 2
    strColor = "White"
    iIndexNum = 2
  Case Else
    strColor = "Custom color or no fill"
End Select

    If ColorName = True Or _
        strColor = "Custom color or no fill" Then
        CellColor = strColor
    Else
        CellColor = iIndexNum
    End If

End Function

 

[parry]

Add the following line directly after the Dim line...

Application.Volatile

Note F9 will force a recalulation. See the help topic "Change when and how formulas are calculated"