Refresh Excel VBA Function Results
ExcelVbaUser Defined-FunctionsExcel Problem Overview
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.
Excel Solutions
Solution 1 - Excel
You should use Application.Volatile
in the top of your function:
Function doubleMe(d)
Application.Volatile
doubleMe = d * 2
End Function
It will then reevaluate whenever the workbook changes (if your calculation is set to automatic).
Solution 2 - Excel
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
Solution 3 - Excel
Okay, found this one myself. You can use Ctrl+Alt+F9 to accomplish this.
Solution 4 - Excel
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
Solution 5 - Excel
To switch to Automatic:
Application.Calculation = xlCalculationAutomatic
To switch to Manual:
Application.Calculation = xlCalculationManual
Solution 6 - Excel
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
Solution 7 - Excel
The Application.Volatile
doesn't work for recalculating a formula with my own function inside. I use the following function:
Application.CalculateFull
Solution 8 - Excel
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
Solution 9 - Excel
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