Refresh Excel VBA Function Results

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

To switch to Automatic   Application Calculation   xlCalculationAutomatic       To switch to Manual   Application Calculation   xlCalculationManual

[excel] Refresh Excel VBA Function Results

Examples related to excel

Examples related to vba

Examples related to user-defined-functions