UDF for Evaluate a Formula in Excel: Turn Text into Results

A UDF for evaluate a formula in Excel solves a common annoyance: a formula stored as text, such as 5+6+7 or VLOOKUP(10005,Sheet2!A:B,2,0), just sits in the cell and never calculates. With a three-line VBA user defined function you can point at that text and get the real result in the next column. In this tutorial you will build PK’s Evaluate_Formula function, plus a second function, Text_Of_Formula, that does the opposite and shows the formula behind any cell, just like FORMULATEXT does in Excel 2013 and later.

⬇️  Download the Practice Workbook
.zip containing Evaluate_Formula.xlsm (macro-enabled) · enable macros to use the two functions

Watch the Video Tutorial

PK shows the text formulas that refuse to calculate, uses Evaluate_Formula to get their results, compares Text_Of_Formula with FORMULATEXT, and then walks through the VBA code of both functions.

What You Will Build

• Evaluate_Formula, a worksheet function that calculates a formula written as text, with or without the equals sign.
• Leading-space handling, so text such as a formula typed after several spaces still works.
• Text_Of_Formula, a function that returns the formula of any cell as text, for Excel 2010 and earlier.
• The FORMULATEXT alternative for Excel 2013 and later.
• Automatic recalculation through Application.Volatile.

Why Text Formulas Do Not Calculate

Excel only calculates a cell when its content starts with an equals sign and nothing comes before it. In the practice file, each entry in column A is plain text for one of these reasons:

• No equals sign: 5+6+7, VLOOKUP(10005,Sheet2!A:B,2,0), Counta(Sheet2!A:A) and 30+5.
• Spaces before the equals sign: =RANDBETWEEN(50,70) and =Today() are typed after a few spaces, so Excel treats them as text.

This happens a lot with formulas pasted from emails, exported from other systems or built by joining text. Retyping each one is slow. A UDF can calculate them for you.

Part 1 – Create the Evaluate_Formula UDF

Step 1: Open the VBA editor and add a module

Press Alt+F11 (or Developer › Visual Basic), then choose Insert › Module. A user defined function must live in a standard module, not in a sheet’s code window, to be usable in cells.

Step 2: Paste the function

This is the code from PK’s practice workbook (Module1):

Option Explicit

Function Evaluate_Formula(formula_text As String) As Variant

    Application.Volatile
    Evaluate_Formula = Application.Evaluate(VBA.Trim(formula_text))

End Function

How the code works

• Function Evaluate_Formula(formula_text As String) – the function takes one input: the formula as text. In a cell you pass a reference such as A2.
• As Variant – the result can be a number, a date or text (VLOOKUP returns Agent-5), so PK uses Variant, which can hold any type.
• Application.Volatile – marks the function as volatile, so it recalculates whenever anything on the sheet changes. That keeps results such as RANDBETWEEN and TODAY current.
• VBA.Trim(formula_text) – removes leading and trailing spaces, which is why the formulas typed after several spaces still work.
• Application.Evaluate(...) – asks Excel to calculate the text as if it were a formula and returns the result. As PK points out, it works whether or not the text starts with an equals sign.

Step 3: Use it in the sheet

In B2 enter =Evaluate_Formula(A2) and fill it down to B8. The practice file returns these results:

Sheet2 holds the lookup data: Emp_Id 10001 to 10020 in column A with Emp_Name Agent-1 to Agent-20 in column B, so the VLOOKUP finds employee 10005 and returns Agent-5, and COUNTA counts 20 IDs plus the header, giving 21.

When the result is a date, such as the TODAY text, Excel shows a serial number until you apply a date format to the cell.

Part 2 – Get the Text of a Formula

The second half of the video goes the other way: you have a calculated cell, for example B2 showing 18, and you want to display the formula that produced it.

Step 4: Use FORMULATEXT in Excel 2013 and later

Excel 2013 and newer versions have a built-in function for this. In C2 enter:

=FORMULATEXT(B2)

It returns =Evaluate_Formula(A2), the formula in B2, as text.

Step 5: Create Text_Of_Formula for Excel 2010 and earlier

Older versions do not have FORMULATEXT, so PK wrote a small UDF. Add it to the same module:

Function Text_Of_Formula(rng As Range) As String

    Application.Volatile
    Text_Of_Formula = rng.Formula

End Function

• rng As Range – the input is a cell reference, not text.
• As String – the output is always text, because it is the formula written out.
• rng.Formula – the Formula property of a range returns the formula exactly as it is written in the cell.
• Application.Volatile – keeps the result up to date if you edit the formula in the referenced cell.

In the practice file, C2 contains =Text_Of_Formula(B2) and returns =Evaluate_Formula(A2), matching FORMULATEXT. In the video PK also tests it on a cell with =NOW() and on =VLOOKUP(10001,Sheet2!A:B,2,0), which returns Agent-1. Text_Of_Formula shows the formula text of each, and passing that text back into Evaluate_Formula gives Agent-1 again.

Part 3 – Make the Functions Available Everywhere

A UDF in a normal workbook only works in that workbook, which must be saved as .xlsm. To use the functions in every file, PK suggests copying the code into your Personal Macro Workbook (PERSONAL.XLSB); see how to set up a personal macro. When a function lives in PERSONAL.XLSB, call it with the workbook name in front, for example =PERSONAL.XLSB!Evaluate_Formula(A2).

No-Module Alternative: the EVALUATE Name

This section is an addition to PK’s method. Excel still supports the old Excel 4 macro function EVALUATE inside a defined name. Select B2, open Formulas › Name Manager › New, name it EvalText and set Refers to to =EVALUATE(Sheet1!A2) with a relative reference (no $ signs). Then type =EvalText in B2 and fill down. The workbook still has to be saved as .xlsm, and this version does not trim spaces, so the UDF is the more flexible choice.

Common Errors and Fixes

#NAME? instead of a result

Excel cannot find the function. Check that the code is in a standard module (Insert › Module), that macros are enabled, and that the file is saved as .xlsm. If the code is in PERSONAL.XLSB, add the PERSONAL.XLSB! prefix.

#VALUE! from Evaluate_Formula

The text is not a valid formula, for example a missing bracket or a misspelt function name. Test it by typing an equals sign and the same text in an empty cell. Microsoft also notes that the string passed to Application.Evaluate must be 255 characters or fewer, so longer formula text fails (an added note, not from the video).

Wrong result for a reference without a sheet name

Application.Evaluate resolves references such as A:B against the sheet that is active when Excel recalculates. Include the sheet name, as the practice file does with Sheet2!A:B, so the result never depends on which sheet is open (added tip).

Date shows as a number

The function returned a date serial number. Apply a date format to the result cell.

Tips

These tips are general Excel advice added to PK’s method:

• Volatile functions recalculate on every change. On a few hundred cells that is fine; on tens of thousands it can slow the workbook, so remove Application.Volatile if your text formulas only use fixed values.
• Use Evaluate_Formula on formulas you trust. It calculates whatever text it is given.
• FORMULATEXT returns #N/A for a cell without a formula, while Text_Of_Formula returns the cell’s constant value instead.
• Microsoft documents Application.Evaluate and the FORMULATEXT function in detail.

Want It Ready-Made?

If you like small VBA helpers that save hours, the Excel VBA Tools & Utilities Pack on NextGenTemplates.com bundles 8 premium, ready-to-use Excel VBA templates, or you can browse all Excel VBA tools.

Related Tutorials

• VBA User Defined Functions: Create Custom Excel Functions (UDF)
• Count Sub-strings in an Excel Cell with a User Defined Function
• VBA Worksheet Functions: Formula Property and WorksheetFunction
• Excel INDIRECT Function: Syntax, Examples and Errors
• Personal Macro in Excel: Quick Data Formatting with Ctrl+M

Frequently Asked Questions

How do I evaluate a formula stored as text in Excel?

Add the Evaluate_Formula user defined function to a VBA module and enter it in a cell with a reference to the text, as shown in Step 3. It passes the text to Application.Evaluate and returns the calculated result.

Does the text need an equals sign?

No. Application.Evaluate calculates the text with or without a leading equals sign, and VBA.Trim removes any spaces before it.

What is the difference between Evaluate_Formula and Text_Of_Formula?

Evaluate_Formula turns formula text into a result. Text_Of_Formula does the reverse: it reads a cell and returns its formula as text, like the built-in FORMULATEXT function.

Should I use FORMULATEXT or Text_Of_Formula?

Use FORMULATEXT in Excel 2013 and later because it needs no macros. Text_Of_Formula is for Excel 2010 and earlier, where FORMULATEXT does not exist.

Why does the function use Application.Volatile?

It makes the function recalculate whenever the sheet changes, so results such as RANDBETWEEN, TODAY and lookups stay current.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

A UDF for evaluate a formula in Excel takes only three lines: trim the text, pass it to Application.Evaluate and return the result as a Variant. Add Text_Of_Formula, or use FORMULATEXT in newer Excel, and you can move in both directions between a formula and its text. Download the practice workbook, enable macros and try your own text formulas in column A.