Refresh Excel VBA Function Results

How can I get a user-defined function to re-evaluate itself based on changed data in the spreadsheet?

I tried F9 and Shift+F9.

The only thing that seems to work is editing the cell with the function call and then pressing Enter.

Some more information on the F9 keyboard shortcuts for calculation in Excel

• F9 Recalculates all worksheets in all open workbooks
• Shift+ F9 Recalculates the active worksheet
• Ctrl+Alt+ F9 Recalculates all worksheets in all open workbooks (Full recalculation)
• Shift + Ctrl+Alt+ F9 Rebuilds the dependency tree and does a full recalculation

Okay, found this one myself. You can use Ctrl+Alt+F9 to accomplish this.

If you include ALL references to the spreadsheet data in the UDF parameter list, Excel will recalculate your function whenever the referenced data changes:

Public Function doubleMe(d As Variant)
    doubleMe = d * 2
End Function

You can also use Application.Volatile, but this has the disadvantage of making your UDF always recalculate - even when it does not need to because the referenced data has not changed.

Public Function doubleMe()
    Application.Volatile
    doubleMe = Worksheets("Fred").Range("A1") * 2
End Function

This refreshes the calculation better than Range(A:B).Calculate:

Public Sub UpdateMyFunctions()
    Dim myRange As Range
    Dim rng As Range

    ' Assume the functions are in this range A1:B10.
    Set myRange = ActiveSheet.Range("A1:B10")

    For Each rng In myRange
        rng.Formula = rng.Formula
    Next
End Sub

The Application.Volatile doesn't work for recalculating a formula with my own function inside. I use the following function: Application.CalculateFull

To switch to Automatic:

Application.Calculation = xlCalculationAutomatic    

To switch to Manual:

Application.Calculation = xlCalculationManual    

I found it best to only update the calculation when a specific cell is changed. Here is an example VBA code to place in the "Worksheet" "Change" event:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("F3")) Is Nothing Then
    Application.CalculateFull
  End If
End Sub

Public Sub UpdateMyFunctions()
    Dim myRange As Range
    Dim rng As Range

    'Considering The Functions are in Range A1:B10
    Set myRange = ActiveSheet.Range("A1:B10")

    For Each rng In myRange
        rng.Formula = rng.Formula
    Next
End Sub