Update Excel VBA Function Results

Question

Does anyone know how I can get a user-defined function to re-evaluate myself (based on the modified data in a spreadsheet)? I tried F9 and Shift + F9 , but they do not work. The only thing that works is editing the cell by calling the function, and then pressing Enter. Any ideas? I seem to remember that I can do this ...

Answer 1

You should use Application.Volatile at the top of your function:

 Function doubleMe(d) Application.Volatile doubleMe = d * 2 End Function 

Then it will be reevaluated whenever the workbook changes (if your calculation is set to automatic).

Answer 2

Additional Information on F9 Shortcuts for Computing in Excel

• F9 Recalculation of all sheets in all open books
• Shift + F9 Recalculation of the active worksheet
• Ctrl + Alt + F9 Recalculation of all sheets in all open books (Full recount)
• Shift + Ctrl + Alt + F9 Rebuilds the dependency tree and performs a full recount

Answer 3

Well, I found it myself. You can use Ctrl + Alt + F9 to accomplish this.

Answer 4

If you include ALL references to spreadsheet data in the list of UDF parameters, Excel will recalculate your function every time you change the reference data:

 Public Function doubleMe(d As Variant) doubleMe = d * 2 End Function 

You can also use Application.Volatile , but this has the disadvantage that your UDF is always recounted - even if it is not needed because the reference data has not changed.

 Public Function doubleMe() Application.Volatile doubleMe = Worksheets("Fred").Range("A1") * 2 End Function 

Answer 5

To switch to automatic:

 Application.Calculation = xlCalculationAutomatic 

To switch to manual mode:

 Application.Calculation = xlCalculationManual 

Answer 6

This updates 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 

Answer 7

Using Excel 2010, I ran into the same issues as Brian. When I tried all the solutions offered here, there was no improvement, the user-defined function was not recalculated, despite the existence of Application.Volatile, the use of recalculation key combinations, etc. However, I realized that my definition of the function is to blame for the fact that all the cell references in it are not qualified enough to work correctly when the sheet containing this function was not an active sheet, for example, changing cells (a, b) to Application.Caller .Worksheet.Cells (a, b) fixed the problem! I believe this is a solution because when the sheet containing my function is not the active cells of the sheet (a, b), it is interpreted as referring to any sheet that was active at that time.

Answer 8

Application.Volatile does not work to recalculate the formula with my own function inside. I use the following function: Application.CalculateFull

Answer 9

 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