Cells using UDF in Excel do not re-calculate

Question

I have written this simple UDF to make a computation in Excel spreadsheets. The code seems to work fine, but only once. However, if I change the values in the table (SharePriceGrowthTable), the results in the cells are not updated. Even when I hit F9 to re-calculate the sheet, the values in the cells where the function is used are not updated. I have verified that the Calculation option is set to Automatic.

Any ideas about what is going on or what I need to do different?

'Compute Stock Growth Score
Function StockGrowthScore(GrowthPercent As Double) As Double

 Dim ScoreTable As Range

 Set ScoreTable = Range("SharePriceGrowthTable")

 If GrowthPercent >= 0 Then
   StockGrowthScore = WorksheetFunction.VLookup(GrowthPercent, ScoreTable, 2)
 Else
   StockGrowthScore = -WorksheetFunction.VLookup(-GrowthPercent, ScoreTable, 2)
 End If

 StockGrowthScore = Application.Round(StockGrowthScore, 3)

End Function

Is the function being called from inside the "SharePriceGrowthTable" Range? — Comintern, Jan 01 '16 at 21:35
No, the Range is in the sheet named "Tools" and the function is called from a number of other sheets in the same workbook. — Jorge O-L, Jan 02 '16 at 00:58

score 0 · Answer 1 · answered Jan 01 '16 at 21:46

In automatic calculation mode, a UDF formula is recalculated when a change occurs in a range that it references explicitly in the formula. In your case, Range("SharePriceGrowthTable") is referenced in the code, but not in the formula.

A possible workaround is to make the range itself a parameter to the UDF:

Function StockGrowthScore(GrowthPercent As Double, tbl as Range) As Double

and now pass the range SharePriceGrowthTable explicitly a second parameter in the formulas.

Comintern · Answer 2 · 2016-01-01T22:00:36.180

0

The issue is with setting the range inside the function. One solution is to pass the Range as a parameter:

Function StockGrowthScore(GrowthPercent As Double, ScoreTable As Range) As Double
    If GrowthPercent >= 0 Then
        StockGrowthScore = WorksheetFunction.VLookup(GrowthPercent, ScoreTable, 2)
    Else
        StockGrowthScore = -WorksheetFunction.VLookup(-GrowthPercent, ScoreTable, 2)
    End If

    StockGrowthScore = Application.Round(StockGrowthScore, 3)
End Function

(Called like =StockGrowthScore(1, SharePriceGrowthTable))

If everything is in the same worksheet, another solution is to make the function Volatile, although the first option is probably better due to the overhead of using Application.Volatile:

Function StockGrowthScore(GrowthPercent As Double) As Double
    Application.Volatile
    Dim ScoreTable As Range
    Set ScoreTable = Range("SharePriceGrowthTable")

    If GrowthPercent >= 0 Then
        StockGrowthScore = WorksheetFunction.VLookup(GrowthPercent, ScoreTable, 2)
    Else
        StockGrowthScore = -WorksheetFunction.VLookup(-GrowthPercent, ScoreTable, 2)
    End If

    StockGrowthScore = Application.Round(StockGrowthScore, 3)
End Function

edited Jan 01 '16 at 22:00

answered Jan 01 '16 at 21:48

Comintern

21,855
5
33
80

1

fwiw - The [Application.CutCopyMode property](https://msdn.microsoft.com/en-us/library/office/ff839532.aspx) [Application.Volatile](https://msdn.microsoft.com/en-us/library/office/ff195441.aspx) method is only appilable to single worksheet calculation; not workbook wide calculation handling without larger evenet handling code. – Jan 01 '16 at 21:55
@Jeeped - Good point (I rarely work across multiple worksheets). Edited. – Comintern Jan 01 '16 at 22:01
Huh? What does Application.Volatile have to do with multiple worksheets? Works fine with references to different worksheets for me. – Charles Williams Jan 01 '16 at 22:59
@CharlesWilliams - Interesting. I was trusting the linked MSDN documentation that says "The function will be recalculated whenever calculation occurs in *any cells on the worksheet on which this function appears*." – Comintern Jan 01 '16 at 23:03
MSDN sometimes says worksheet when it means workbook(s). Probably a hangover from Excel version 3 or whenever there only was one worksheet. – Charles Williams Jan 02 '16 at 11:20

Cells using UDF in Excel do not re-calculate

2 Answers2