How to Evaluate a Formula Stored as Text in Excel (VBA)

Table of Contents

Quick answer: Excel does not have a regular worksheet function that evaluates arbitrary formula text stored in a cell. In desktop Excel, you can create a small VBA user-defined function using Application.Evaluate, then call it from another cell. This tutorial shows the code, explains its limitations, and covers common errors. Only evaluate trusted formula strings: avoid running expressions supplied by unknown users.

Example: calculate a formula stored as text

Suppose cell A1 contains the text =SUM(1,2,3) (stored as text, not calculated as a worksheet formula). You want B1 to display 6. A VBA function can evaluate the expression and return its result. Use this technique only for formula strings you control; some expressions may fail or behave differently depending on the workbook context.

Create a VBA function to evaluate trusted formula text

A VBA user-defined function is one way to evaluate a trusted Excel formula string. This approach requires a desktop version of Excel with VBA support; it will not run in Excel for the web.

Step by step guide

1. Open the VBA Editor

Go to Developer Tab > Visual Basic or Press Alt + F11 to open the VBA editor. Note: If you don’t see Developer Tab follow these steps.
Insert a new module by right-clicking on any existing module or the workbook name, then selecting Insert > Module.

2. Add the following VB code

Public Function EvaluateFormula(ByVal FormulaText As String) As Variant
    On Error GoTo EvaluationError
    EvaluateFormula = Application.Evaluate(FormulaText)
    Exit Function
EvaluationError:
    EvaluateFormula = CVErr(xlErrValue)
End Function

Code Explanation:

FormulaText As String: The argument receives the text of the expression. When you call =EvaluateFormula(A1), Excel passes the text stored in A1 to the function.
Application.Evaluate: Excel interprets the supplied expression and returns a result. This sample does not use Application.Volatile; Excel normally recalculates the function when its referenced argument changes.
Error handling: If evaluation raises a VBA error, the function returns #VALUE!. Other invalid Excel expressions may also return an Excel error value. Test formulas in a copy of your workbook.

3. Save as a macro-enabled workbook

Save the workbook as Excel Macro-Enabled Workbook (*.xlsm) before closing the VBA editor. A standard .xlsx file does not retain VBA modules. Only enable macros in files you trust; workplace security settings may prevent VBA from running.

4. Enter the text expression and call the function

Let us understand its use with following Example

Set cell A1 to Text format first, then enter =SUM(1,2,3). Alternatively, prefix the entry with an apostrophe to store it as text. If you enter it as an ordinary formula, Excel will calculate it immediately rather than storing the expression.
In cell B1, enter =EvaluateFormula(A1) and press Enter. The function name must match the VBA code above.
For this example, B1 should display 6. The existing screenshot illustrates the idea, but follow the function name EvaluateFormula used in the corrected code above.

Get Developer Tab in Excel

The Developer tab is optional if you open the VBA editor directly. To display it in desktop Excel for Windows, follow these steps:

Right-click an empty area of the Ribbon.
Click “Customise the Ribbon” to open
In Customise the Ribbon, select Main Tabs, then Check the box against Developer and click OK.
The Developer tab should now appear on the Ribbon. This option is not available in Excel for the web.

Important Limitations and Safety Notes

Desktop Excel only: VBA functions do not run in Excel for the web.
Save as .xlsm: A normal .xlsx file cannot retain VBA code.
Trusted input only: Evaluate can interpret Excel expressions, including references and functions. Do not feed it formulas from untrusted users.
Recalculation: Excel may not track dependencies hidden inside a text expression. For example, if A1 contains a formula string referring to C1, changing C1 alone may not trigger the expected recalculation of the UDF.

Troubleshooting

Why do I see #NAME?

Check that the code is in a standard VBA module, the workbook is macro-enabled, macros are permitted by your organization’s security policy, and the worksheet formula uses =EvaluateFormula(A1) rather than a different function name.

Why do I see #VALUE!?

The text may not be a valid Excel expression, or evaluation may have failed. Try a simple expression such as =1+2 first. Check the formula separators and function names used by your Excel installation.

Can I use this in Excel Online?

No. Excel for the web cannot execute VBA macros. If you need a browser-based solution, consider restructuring the workbook to use ordinary formulas or a supported automation approach instead of a VBA user-defined function.

Conclusion

Use this VBA approach only for trusted expressions that cannot be represented with ordinary worksheet formulas. Save as .xlsm, check the expected result using a simple expression, and document any hidden reference or recalculation limitations before sharing the workbook. Excel for the web cannot execute the function.

Worked Examples and Safer Alternatives

For a quick check, store =10*3+2 as text in A1 and enter =EvaluateFormula(A1) in B1. The expected result is 32. If A1 contains =SUM(1,2,3), the expected result is 6. These examples use only literal numbers, so they do not depend on another worksheet cell.

Important: If the text refers to a cell, such as =C1*2, the result depends on how Excel resolves the reference. Application.Evaluate does not automatically make that hidden C1 reference a tracked dependency of the UDF. Changing C1 may leave B1 stale until recalculation. Prefer a normal worksheet formula such as =C1*2 when possible.

When not to use Evaluate

If your input contains only a few supported operations, build the calculation with normal Excel formulas or explicitly parse the allowed values instead of evaluating arbitrary strings. Do not enable macros merely to process downloaded spreadsheets or formula text from untrusted sources. For simple numeric inputs, a direct formula is easier to audit, share, and recalculate.

C P Gupta

C P Gupta is a YouTuber and Blogger. He is expert in Microsoft Word, Excel and PowerPoint. His YouTube channel @pickupbrain is very popular and has crossed 9.9 Million Views.