Fix Intermittent #VALUE! Errors in Excel VBA UDF Formulas
Question details
Identify why Excel VBA user-defined functions intermittently return #VALUE! after workbook changes.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook uses VBA functions that accept range arguments and call other functions. The user switches workbooks or opens an attached file.
- Observed behavior
- Formulas initially return correct results but occasionally show #VALUE!. Calculate Now or editing an input cell restores the results.
Open the affected workbook in a version of Excel that supports VBA and ensure its macros are enabled if you trust the file. Locate a formula that fails and the VBA code for the UDF and its helper functions.
Trace the UDF and its helper functions
Use this approach to find the code, reference, or data condition that produces the intermittent error.
A successful recalculation does not identify the cause. Review the complete UDF, including GetExcelConstantValue and RP_Get_RoleRatio, because an error in a called function can surface as #VALUE! in the formula.
Select an affected formula cell and review its arguments in the formula bar. Check the referenced ranges for error values and confirm that each range points to the intended workbook and sheet.
Open Developer > Visual Basic, locate the UDF and its helper functions, and set breakpoints on the relevant lines. Reproduce the workbook switch or file-opening sequence, then use the editor to inspect argument values and identify where execution fails.
In the VBA editor, inspect references such as ActiveWorkbook, ActiveSheet, and unqualified Range or Cells calls. Qualify them with the intended workbook and worksheet, or use the workbook associated with a passed range where appropriate.
In the UDF code, verify that changing inputs are passed as arguments where practical and that helper functions handle unavailable data and unexpected values. Add targeted error handling that preserves useful diagnostic information instead of silently masking the cause.
Return to Excel, switch workbooks or open the attached file as before, and check the affected formula cells. If the error recurs, use the breakpoint and inspected values to narrow down the failing call.
Check calculation settings and compare a full calculation
Use these checks to distinguish calculation-state symptoms from an error in the UDF or its data.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Office formats, making it easy to move everyday spreadsheet work. It does not diagnose or fix an Excel VBA UDF error; check macro compatibility before migrating VBA-dependent workbooks.

Frequently Asked Questions
Why does Calculate Now restore my UDF result?
Formulas > Calculate Now reevaluates formulas, so the UDF may succeed when it runs again. That does not establish the cause; inspect the UDF, its helper functions, and the data and workbook references available when the error occurs.
Can switching workbooks affect VBA formulas?
It can if the UDF or a helper function relies on ActiveWorkbook, ActiveSheet, or unqualified Range or Cells references. In Developer > Visual Basic, check those references and qualify them with the intended workbook and sheet.
Should every dependent range be passed to the UDF?
Pass changing inputs as arguments where practical so Excel can track them as formula dependencies. If they are already arguments, inspect their values and the helper functions rather than assuming a missing dependency is the cause.
How can I test a full calculation in Excel?
Open Developer > Visual Basic, open the Immediate window, and run Application.CalculateFull. Compare the outcome with Formulas > Calculate Now and with recalculation after editing an input cell.
Does WPS Office fix an Excel VBA UDF error?
No. The error must be investigated in Excel and its VBA code. WPS Office can be an alternative for everyday spreadsheet work, but check macro compatibility before moving a workbook that depends on VBA.




