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.

score 136 · Accepted Answer · edited Aug 28 '15 at 16:33

136

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

edited Aug 28 '15 at 16:33

Bond

16,071
6
30
53

answered Aug 15 '08 at 06:18

vzczc

9,270
5
52
61

20

Thanks, just been bashing my head against a desk over this. Should point out though that in Excel 2010, you need to pass True to Application.Volatile, i.e. `Application.Volatile True`. – mdm Mar 31 '10 at 13:00
@mdm Thanks for the info on the True!! Needed this also for a custom worksheet function in Excel 2013!! – timbram Dec 11 '15 at 18:28
2

this works when I change the value of a cell, but I have a function that sums cells based on fill color and this doesn't update the function when I change the cell's fill color, I have to retype the value. can I have the worksheet live update as I change Fill Color? – MilkyTech Jun 28 '16 at 00:25
@ChrisL Did you find a solution for your use case with the calculation based on fill color? Appreciate your solution. – smartini May 30 '20 at 16:02
@smartini I never continued looking for a solution. I wasn't using my fill color function often enough for it to matter. I was hoping for a quick answer to my comment 4 years ago but it never came. Maybe best to start a new question. If you do, hit me up with a comment here so I can follow it. – MilkyTech May 30 '20 at 16:24

score 44 · Answer 2 · edited Jul 21 '13 at 17:58

44

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

edited Jul 21 '13 at 17:58

Hulk1991

3,079
13
31
46

answered Sep 07 '08 at 16:11

Robert Mearns

11,796
3
38
42

score 18 · Answer 3 · edited Apr 21 '15 at 15:33

18

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

edited Apr 21 '15 at 15:33

Abhishek

6,912
14
59
85

answered Aug 14 '08 at 13:59

Brian Sullivan

27,513
23
77
91

This is your only option if you have Excel 2007 seeing as the `Application.Volatile` method was added in Office 2010. – I say Reinstate Monica Mar 12 '15 at 19:31

score 12 · Answer 4 · edited May 30 '19 at 00:45

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

score 1 · Answer 5 · answered Dec 03 '14 at 19:22

1

To switch to Automatic:

Application.Calculation = xlCalculationAutomatic

To switch to Manual:

Application.Calculation = xlCalculationManual

answered Dec 03 '14 at 19:22

ayman

29
4

score 1 · Answer 6 · edited May 30 '19 at 00:47

1

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

edited May 30 '19 at 00:47

MarredCheese

17,541
8
92
91

answered Jun 10 '15 at 00:29

MfJ

11
1

score 1 · Answer 7 · edited Nov 21 '17 at 09:12

1

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

edited Nov 21 '17 at 09:12

aniski

1,263
1
16
31

answered Nov 21 '17 at 08:20

alex303411

21
1

score 1 · Answer 8 · edited Sep 13 '19 at 20:33

1

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

edited Sep 13 '19 at 20:33

Robert

7,394
40
45
64

answered Sep 13 '19 at 20:17

Gene Sorg

11
1

score 0 · Answer 9 · edited Oct 22 '14 at 12:34

0

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

edited Oct 22 '14 at 12:34

Sebastian Lange

3,879
1
19
38

answered Oct 22 '14 at 12:01

Prashanth

21

Please explain the code a bit. It would increase the usefullness quite a lot. – NoDataDumpNoContribution Oct 22 '14 at 13:44
rng.Formula = rng.Formula is the same as entering a cell and pressing enter again. It forces Excel to calculate the cell again - including any custom functions. – SSlinky Apr 06 '16 at 01:35

Refresh Excel VBA Function Results

9 Answers9

Linked

Related