CODEWITHSUNDEEP
excelvbauser-defined-functions
Brian Sullivan
Brian Sullivan

Reputation: 28563

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.

Upvotes: 82

Views: 170413

Answers (9)

Gene Sorg
Gene Sorg

Reputation: 21

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

Upvotes: 2

MfJ
MfJ

Reputation: 11

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

Upvotes: 1

Charles Williams
Charles Williams

Reputation:

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

Upvotes: 12

alex303411
alex303411

Reputation: 21

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

Upvotes: 1

vzczc
vzczc

Reputation: 9380

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).

Upvotes: 139

Brian Sullivan
Brian Sullivan

Reputation: 28563

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

Upvotes: 20

ayman
ayman

Reputation: 29

To switch to Automatic:

Application.Calculation = xlCalculationAutomatic

To switch to Manual:

Application.Calculation = xlCalculationManual

Upvotes: 1

Prashanth
Prashanth

Reputation: 21

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

Upvotes: 0

Robert Mearns
Robert Mearns

Reputation: 11986

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

Upvotes: 47

Related Questions