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.
This question is tagged with
~ Asked on 2008-08-14 13:54:43
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).
~ Answered on 2008-08-15 06:18:51
Some more information on the F9 keyboard shortcuts for calculation in Excel
~ Answered on 2008-09-07 16:11:58
Okay, found this one myself. You can use Ctrl+Alt+F9 to accomplish this.
~ Answered on 2008-08-14 13:59:21
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
~ Answered on 2008-09-23 11:12:34
This refreshes the calculation better than
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
~ Answered on 2015-06-10 00:29:57
Application.Volatile doesn't work for recalculating a formula with my own function inside. I use the following function:
~ Answered on 2017-11-21 08:20:20
To switch to Automatic:
Application.Calculation = xlCalculationAutomatic
To switch to Manual:
Application.Calculation = xlCalculationManual
~ Answered on 2014-12-03 19:22:59
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
~ Answered on 2019-09-13 20:17:47
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
~ Answered on 2014-10-22 12:01:38