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
or Press*Developer Tab > Visual Basic*`<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 functionand hit enter*=EvaluateString(A1)* - 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

in empty area of the Ribbon*Right click*- Click
to open*“Customise the Ribbon”* - In
, select*Customise the Ribbon*s, then*Main Tab*the box against**Check**and*Developer*.*click OK* - Now you will see
in*Developer Tab**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.

### Related Posts

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.