If an Excel VBA function is not updating automatically, first check Formulas > Calculation Options > Automatic, then press Ctrl+Alt+F9 to force a full recalculation. If the result still does not change, make sure every cell the function reads is passed as an argument; Excel may not know that an indirectly referenced cell is a dependency.
The right fix depends on what you mean by a VBA function. A worksheet custom function, or UDF, is recalculated according to Excel’s calculation rules. A regular macro, by contrast, runs only when something calls it, such as a button, keyboard shortcut, or event procedure. Understanding that distinction helps narrow down the cause without adding unnecessary code.
How Excel decides when to recalculate a VBA function
Excel tracks which cells a formula depends on. For a built-in formula such as SUM, it can usually tell that changing a referenced cell may change the answer. A VBA function used in a worksheet formula can also participate in this dependency system when its inputs are supplied as arguments.
For example, suppose A2 contains 12 and you enter the custom function shown later in B2. The formula in B2 is =DoubleValue(A2), and it returns 24. Because A2 is an argument, Excel can identify it as an input. Changing A2 should mark the formula for recalculation when calculation is automatic.
The situation differs if the VBA code reads a cell behind the scenes, such as using a reference to a worksheet range inside the function, while the worksheet formula itself has no argument for that range. Excel may not register that hidden relationship. The function can then remain unchanged until some other action causes it to run again.
Check Excel’s calculation mode first
In Microsoft 365 for Windows, open the Formulas tab, choose Calculation Options, and select Automatic. In Automatic mode, Excel recalculates dependent formulas when their inputs change. If the setting is Manual, formulas—including formulas that call VBA UDFs—may display old results until you request calculation.
To test the workbook after changing the setting, edit a cell used by the function or press a calculation shortcut:
- F9 recalculates formulas in open workbooks that need calculation.
- Shift+F9 recalculates the active worksheet.
- Ctrl+Alt+F9 forces a full calculation of all formulas in open workbooks.
- Ctrl+Shift+Alt+F9 rebuilds formula dependencies and then performs a full calculation.
On a Mac, use Formulas > Calculate Now or Formulas > Calculate Sheet where available; keyboard shortcuts can differ by keyboard and Excel build. On Excel for the web, VBA code cannot run, so a VBA UDF will not calculate there. Open the workbook in the desktop Excel app to use its VBA function.
Calculation mode is an Excel application setting that can affect more than one open workbook. If a workbook unexpectedly switches to Manual, check the setting again after opening the files you need. Do not assume a single workbook’s visible result proves that every other open workbook is calculating automatically.
Pass every changing input as a function argument
A worksheet UDF is most predictable when its inputs are explicit. If a function uses a rate in C1 and an amount in A2, pass both cells into the function in the worksheet formula and declare matching arguments in VBA. Then Excel has a visible dependency on both inputs.
By contrast, a function that reads C1 internally through a worksheet or range reference may not recalculate when C1 changes, because C1 is absent from the formula’s argument list. The practical repair is usually to redesign the function to accept the changing cell or range as an argument. This is preferable to making every function volatile, especially in a large workbook.
Also check that the formula points to the intended cells and workbook. A function can appear stale when it is actually reading a different sheet, a fixed absolute reference, or a value from another workbook that has not been updated. Inspect the formula bar and, if needed, use Formulas > Evaluate Formula to follow a formula’s calculation steps. That tool can help with the worksheet expression, though it does not step through VBA code.
When Application.Volatile helps—and when it hurts
VBA provides Application.Volatile for a UDF that must be recalculated whenever Excel recalculates. Put it inside the function. This can be useful when the result depends on information Excel cannot see in the function’s arguments, such as a value obtained from an external state that may change without a normal cell dependency.
Volatile does not mean continuously updating. It does not create a timer or make Excel recalculate every second. It means the function is marked for recalculation during calculation cycles, including cycles caused by other workbook changes or by a manual calculation command. If nothing triggers a calculation cycle, volatility alone does not refresh the result.
Use volatility cautiously. If many cells call a volatile UDF, Excel may run those functions repeatedly during recalculation, which can slow a workbook. It can also make results seem unpredictable when the function depends on external state. Prefer explicit arguments whenever possible; reserve volatility for cases where the function genuinely needs to run on each calculation cycle.
Distinguish a worksheet function from a macro or event
A VBA UDF is called from a cell formula and returns a value to that cell. A normal Sub procedure is a macro: it does not automatically run just because a cell used in a calculation changes. If the code is a Sub that writes a result into a cell, you must explicitly run it or arrange for an event procedure to call it.
Events have their own triggers. For instance, a Worksheet_Change event responds to a user or external process changing cell contents; it does not fire merely because a formula result recalculated. A Worksheet_Calculate event is associated with recalculation of that worksheet. Event code belongs in the relevant worksheet’s code module, not in an ordinary standard module. Event procedures can also cause repeated calculations or loops if they change cells that trigger more events, so they need careful design.
Before adding an event macro, ask whether a normal worksheet formula or a UDF with explicit arguments solves the problem. Use an event only when the required action is genuinely event-driven—for example, copying or formatting information after an edit—not simply to force a UDF to refresh. If you use event code, check whether events were disabled by other code and ensure they are turned back on after an error.
Check the function code and workbook setup
Confirm that the function is a Public function in a standard module, that its name matches the worksheet formula, and that the workbook contains the code. In the Visual Basic Editor on Windows, press Alt+F11, then choose Insert > Module to add a standard module. A simple test function looks like this:
Public Function DoubleValue(ByVal inputValue As Double) As Double
' Return twice the supplied cell value.
DoubleValue = inputValue * 2
End Function
With 12 in A2, enter =DoubleValue(A2) in B2; the result is 24. This function has no hidden inputs, so a change to A2 can be tracked as a dependency. Save a workbook that contains VBA as an Excel Macro-Enabled Workbook (.xlsm) or an add-in (.xlam); saving as .xlsx removes VBA code. Save a copy before running a macro, because macro actions cannot be undone with Ctrl+Z.
Also verify that macros are permitted for this trusted file. If Excel blocks the code, the UDF may show an error such as #NAME? rather than returning a current result. Do not enable all macros globally just to solve one workbook problem; follow your organization’s security guidance and enable code only when you trust its source. If the formula itself returns an error, inspect the function’s input types and any error values in its referenced cells.
A practical troubleshooting order
Use this sequence to find the cause efficiently instead of changing several settings at once:
- Confirm that the formula is actually a worksheet formula calling a VBA Function, not a Sub macro that needs to be run.
- Set calculation to Automatic and test by changing an input cell.
- Use F9, then Ctrl+Alt+F9 if needed, to distinguish a calculation issue from a code or dependency issue.
- Check whether every changing cell is included in the UDF’s argument list.
- Confirm macros are allowed, the file is saved in a macro-enabled format, and the function name and module are correct.
- If the function relies on hidden or external state, consider whether volatility is appropriate; do not use it as a substitute for clear inputs.
If a full calculation updates the result but an ordinary edit does not, calculation mode or dependency tracking is the likely area to investigate. If even a full calculation does not help, look for a missing input, disabled or unavailable VBA, a code error, or a formula that calls a different function than expected.
Frequently Asked Questions
Why does my Excel VBA function update only when I press F9?
This often indicates Manual calculation mode or an input Excel cannot see. Set Formulas > Calculation Options to Automatic, then ensure the cells the function reads are passed as arguments. F9 requests calculation; it does not repair a missing dependency by itself.
Does Application.Volatile make a VBA function update instantly?
No. It marks a UDF for recalculation during Excel calculation cycles. It does not run continuously or create a timer, and Excel still needs a calculation cycle to occur.
Why does my UDF not update when another cell changes?
If the function reads that cell indirectly from VBA instead of receiving it as an argument, Excel may not know the dependency. Pass the cell or range as an argument where possible, then recalculate.
Can a VBA function run in Excel for the web?
No. Excel for the web can preserve a workbook containing VBA, but it cannot run VBA code. Open the workbook in desktop Excel to calculate its VBA UDF.
Will Worksheet_Change fix a function that is not recalculating?
Not usually. Worksheet_Change responds to cell-content changes, not formula recalculation. For a worksheet UDF, correct calculation mode and explicit function arguments are generally the appropriate fixes.
Related
- Excel Text to Columns: How to Split Data Into Fields
- Excel Text to Columns: Split Cell Text at Line Breaks
- Excel Text to Columns: Find It and Split Data Correctly
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Sort Column: Code, Options, and Common FixesTo sort a column with VBA in Excel, define the sort key and apply a sort to the…
- Excel VBA Array Allocation: Declare, Size, and ResizeIn Excel VBA, declare a dynamic array with empty parentheses, then allocate it with ReDim; for example, use…
- VBA Outlook Create Contact Group from ExcelYes. In classic Outlook for Windows, an Excel VBA macro can read email addresses from a worksheet, create…
- VBA AutoFilter Off: Remove Arrows or Clear FiltersTo show every row while keeping the filter controls, use VBA’s ShowAllData method when the worksheet is filtered.…
- Read a Text File with Excel VBA: Methods and ExamplesYes. In Excel for Microsoft 365 on Windows, VBA can read a plain-text file with the Open and…
- Excel VBA Date Formatting: Examples and FixesTo format a date in Excel VBA, use Format$ when you need formatted text, or set a cell’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.