Excel Evaluate String as Formula (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will explore the idea to evaluate a string as a formula in Microsoft Excel. The necessity of this feature is discussed, especially in situations when dynamic computations and automation are necessary. This can be done in a number of ways, including by utilizing VBA custom functions and the Excel Name Manager feature, which is described in detail throughout the article. It shows how to evaluate formula strings in Excel with step-by-step demonstrations and code excerpts. It also covers typical problems, solutions, and restrictions related to evaluating strings as formulas in Excel. The article’s overall goal is to offer users who want to evaluate formulas in Excel utilizing string inputs some useful practical advice.

Overview to Evaluate String as Formula with Excel VBA


Download Practice Workbook

You can download the practice workbook here.


What Does String as a Formula Mean?

In Excel, “string as formula” refers to treating a string of characters as a formula expression. Excel typically interprets a cell’s contents as either a value or a text string. Excel can, however, assess and determine the outcome of a legitimate formula when a string that represents it is entered in a certain format.

Consider the string “=SUM(A1:A5)” as an example. When you type this string into a cell, Excel will recognize it as a formula expression and add the values in cells A1 through A5 for you.

In situations when you wish to construct or modify formulas on the fly based on particular criteria or inputs, strings can be useful as formulas. It enables you to develop and assess things programmatically.


Excel Evaluate String as Formula: 2 Easy Ways

In this segment, we will briefly discuss the stepwise and handy ways to evaluate string as formula in Excel. In both methods, we will use VBA to do the tricks. Without further delay, let’s hover over the methods. We used a dataset numeric value as Text Format in Text1 and Text2 columns and made a Formula Text column with them. Open VBA window & Insert New Module first. Follow the link if you don’t know the way already.

Dataset


1. Creating User Defined Function to Evaluate String as Formula in Excel

In the first method, we will simply use the VBA Evalute function to do the task. We will create a user-defined function to combine with the VBA Evaluate function to do the task. Let’s follow the simple steps.

  • Firstly, prepare a dataset. you can follow our one.
  • Then, open the VBA window and insert a new module and insert the following code in the module.
  • No need to run the code but you can reset it.

Code:

Function EvaluateFormula(formula As String) As Variant
    On Error Resume Next
    EvaluateFormula = Evaluate(formula)
    If Err.Number <> 0 Then
        EvaluateFormula = "Error: " & Err.Description
    End If
    On Error GoTo 0
End Function

Code for Using VBA Evaluate Function in User Defined Function of Excel VBA

Code Breakdown:

  • The function EvaluateFormula takes a formula parameter, which is the formula string to be evaluated.
  • On Error Resume Next is used to bypass any runtime errors that may occur during formula evaluation.
  • EvaluateFormula = Evaluate(formula) evaluates the formula string using the Evaluate function in Excel. It assigns the evaluated result to the EvaluateFormula
  • If Err.Number <> 0 Then check if any error occurred during formula evaluation.
  • EvaluateFormula = “Error: ” & Err.Description assigns an error message to the EvaluateFormula variable if an error occurred. The error description is retrieved using Description.
  • On Error GoTo 0 resets the error handling to its default behavior.

  • Finally, use the following formula to have the formula result from the formula in text format. We used the VBA custom function.
=EvaluateFormula(D5)
  • Of course, you can use the Fill Handle to copy the formula in the following cells.

Using Custom Function to Evaluate String as Formula in Excel


2. Creating User Defined Function with VBA Mid Function to Evaluate String as Formula in Excel

This time, the code is similar except that we used the VBA Mid function in the code. Let’s see the simplest procedures.

  • Firstly, prepare a dataset. you can follow our one.
  • Then, open the VBA window and insert a new module and insert the following code in the module.
  • No need to run the code but you can reset it.

Code:

Function EVALUATE_FORMULA(formulaString As String) As Variant
    Dim formula As String
    formula = Mid(formulaString, 2, Len(formulaString) - 2) _
    ' Remove the leading and trailing "="
    On Error Resume Next
    EVALUATE_FORMULA = Evaluate(formula)
    If Err.Number <> 0 Then
        EVALUATE_FORMULA = "Error: " & Err.Description
    End If
    On Error GoTo 0
End Function

Code for Utilizing VBA Mid Function in User Defined Function to Evaluate String as Formula in Excel

Code Breakdown:

  • The function EVALUATE_FORMULA takes a formulaString parameter, which is the formula string to be evaluated.
  • Dim formula As String declares a variable named formula to store the modified formula string.
  • formula = Mid(formulaString, 2, Len(formulaString) – 2) extracts the formula string without the leading and trailing “=” characters using the Mid
  • On Error Resume Next is used to bypass any runtime errors that may occur during formula evaluation.
  • EVALUATE_FORMULA = Evaluate(formula) evaluates the modified formula string using the Evaluate function in Excel. It assigns the evaluated result to the EVALUATE_FORMULA
  • If Err.Number <> 0 Then check if any error occurred during formula evaluation.
  • EVALUATE_FORMULA = “Error: ” & Err.Description assigns an error message to the EVALUATE_FORMULA variable if an error occurred. The error description is retrieved using Description.
  • On Error GoTo 0 resets the error handling to its default behavior.

  • Finally, use the custom function. Insert the following formula in a cell.
  • Also, hit Enter.
=EVALUATE_FORMULA(D5)
  • As a result, the formula result value from the string formatted formula is displayed in the cell.
  • Of course, you can use the Fill Handle to copy.

Use Custom Function


Frequently Asked Questions

  • Do I need to use VBA or custom functions to evaluate a text as a formula in Excel?

Ans: Yes, using VBA or Custom Function is often necessary to evaluate a string as a formula in Excel. There isn’t a built-in function in Excel that can evaluate a formula string directly.

  • What does it mean to evaluate a string in an Excel formula?

Ans: String evaluation as a formula enables automated and dynamic calculations. It is especially helpful when you want to create or alter formulas based on specific requirements or inputs.


Things to Remember

  • Don’t forget to save the workbook as a .xlsm file after inserting any VBA
  • Be careful about the absolute and normal cell references.

Conclusion

In conclusion, VBA or custom functions can be used to evaluate a string as a formula in Excel. Although there isn’t a built-in function in Excel, the Name Manage feature can be used to evaluate strings as formulas. Overall, the article offers helpful tips and advice to users who want to evaluate formulas in Excel using string inputs. Feel free to comment if you have any queries or suggestions.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo