Evaluates String as if it Were a Formula in Excel

Microsoft Excel has vast library of built in function, but there are some tasks that it doesn’t handle out of the box. One such task is evaluating a formula stored as a text string within a cell. Though Excel doesn’t provide a direct function for this, you can leverage the power of VBA (Visual Basic for Applications) to create a function that evaluates a string as if it were a formula. In this blog post, we will guide you through creating and using this custom function in Excel.

Problem Statement

For example, you might have a cell containing the text “SUM(1, 2, 3)” or “=SUM(1, 2, 3)” and you want Excel to display the result of this formula as 6 in another cell. This is just an example, instead you can have any other formula as string.

Solution

There is no direct formula that does the above work but with a simple VBA function, you can achieve this functionality very easily.

Step by step guide

1. Open the VBA Editor

Go to Developer Tab > Visual Basic or Press <strong><em>Alt + F11</em></strong> 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

Function EvaluateString(FormulaCell As Range) As Variant
    Application.Volatile
    EvaluateString = Evaluate(FormulaCell.Value)
End Function

Code Explanation:

FormulaCell As Range: This allows the function to take a cell reference as an argument.
Application.Volatile: This ensures that the function recalculates every time a change occurs in the workbook, making it dynamic.
Evaluate(FormulaCell.Value): This evaluates the text string in the referenced cell as a formula and returns the result.

3. Save code and Close VB editor

4. Use above VB function to evaluate String as if it was Excel formula

Let us understand its use with following Example

In cell A1, enter the formula string you want to evaluate, such as "=SUM(1, 2, 3)".
In another cell (e.g., B1), use the custom function =EvaluateString(A1) and hit enter
Cell B1 will now display the result of the formula in A1, which in this case is 6.

Get Developer Tab in Excel

To get developer tab

Right click in 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.
Now you will see Developer Tab in Excel

Conclusion

Microsoft Excel, doesn’t have a direct formula that can evaluate string as if it is formula. However this can be easily achieved using VB function.