# 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.

#### 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

1. Right click in empty area of the Ribbon
2. Click “Customise the Ribbon” to open
3. In Customise the Ribbon, select Main Tabs, then Check the box against Developer and click OK.
4. 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.