How to Evaluate String as Code with Excel VBA (2 Examples)

Are you looking for ways to evaluate string as code with Excel VBA? Visual Basic for Applications (VBA) is a versatile programming language widely used for automating tasks, building custom solutions, and enhancing the functionality of Microsoft Office applications. One of its powerful features is the ability to evaluate strings as code, allowing developers to dynamically execute commands and expressions stored as text. In this article, we will delve into the concept of evaluating strings as code in VBA.

Introduction to VBA EVALUATE Function in Excel

• Objective

The EVALUATE function is primarily used to evaluate Excel formulas within the context of a worksheet. Its main objective is to calculate the result of an Excel formula and return that result to your VBA code.

• Syntax

• Argument

• Output

Calculates the result of the provided formula or expression and returns that result as a value of the appropriate data type.

• Version

You will get this function in every recent version of the Excel application.

Excel VBA to Evaluate String as Code: 2 Examples

The concept of “evaluating a string as code” means taking a string that contains code in a particular programming language and dynamically executing that code as if it were part of the program. This concept is more commonly found in languages like JavaScript and some scripting languages.

In the context of VBA, which is more limited in this aspect compared to languages like JavaScript, “evaluating a string as code” is not a built-in feature. Instead, VBA allows you to interact with and manipulate its own code modules, which can give you some level of dynamic code behavior. However, it’s not as straightforward as in other languages with a direct eval() function.

In other words, “evaluate string as code” typically means turning a string into executable instructions in the programming language, and while you can achieve somewhat similar results in VBA through various methods like dynamically adding code to modules and using Application.Evaluate() for formulas, the concept isn’t as directly applicable.

In this section, you will find 2 effective and practical ways to evaluate a string as code in Excel. To do the tasks, we will use the EVALUATE function in VBA, as it generally converts strings into numbers or values. Let’s check them now!

1. Evaluate String as Formula to Find Output of a Single Cell with VBA

Let’s say, we have a dataset of some Products of a store, their Unit Price, and sold Units.

We have to find the total price from that dataset. Here, instead of applying the formula (with a “=” sign), we will just insert the text string of the formula and evaluate the string as code. We evaluate every single cell and find the output from the text string.

⏩ Steps:

• First of all, insert the string without the “=” sign. For example, Monitor has a unit price of 5000 and sold 2 units. If you use a formula, you should enter: “=5000*2” to get the total price. But now, we will just enter “5000*2” as we want to evaluate this string. In this way, fill every cell where you want to find the totals.

• Then, evaluate these strings with the EVALUATE function in VBA. For this, press ALT+F11 to open the Visual Basic Editor window.
• Here, click Insert and select Module to open the Module window. Assign your code to that window. You can use the following.

Code:

Sub TestingEvaluate()
  Dim frng As Range
  Set frng = Range("E5")
  ActiveCell = Evaluate(frng.Value)
End Sub

This code will evaluate a string in cell E5.

• Now, Run that code and you will see that cell containing the string “5000*2” has been converted to 10000. In this way, you can evaluate a string to get the output without applying a formula (i.e. “=” sign).

• Here, one thing is noticeable: the code is applicable just for one cell. If you want to apply the code for multiple cells, then you have to change the cell reference each time and Run the code every single time to run the code.

So, these are the steps you can follow to find the output of a single cell. So easy, isn’t it?

Read More: VBA Evaluate Array Formula

2. Using VBA EVALUATE Function to Format a String

You can also use the EVALUATE function to format a string. Let’s consider the previous dataset.

Let’s say, we want to Bold Row no 8 (i.e. Grand Total ) using the Evaluate function. So, check out the steps for serving that purpose.

⏩ Steps:

• First, open the Module window following the steps stated in Method 1.
• Now, insert the following code in that window.

Code:

Sub TestingEvaluateFormat()
  Dim rfBld As String
  Worksheets("Sheet2").Activate
  rfBld = "C8:E8"
  Application.Evaluate(rfBld).Font.Bold = True
End Sub

Here, we have used C8:E8 as a cell reference as we want to bold the value of these cells.

• After that, Run that code. The code will evaluate that string and bold the referenced strings.

If you are looking for a method to directly execute arbitrary VBA code contained within a string, Excel VBA doesn’t provide a direct “eval” function for this purpose. The techniques I’ve shared provide controlled and structured ways to achieve dynamic behavior in VBA. This approach allows you to dynamically calculate results based on input expressions, effectively evaluating strings as formulas.

Things to Remember

• You have to use individual references while applying Method 1 for evaluating multiple cells.
• Don’t forget to select the cell where you wish to see the evaluation result for Method 1.

📁 Download Practice Workbook

You can download the practice book from the link below.

Conclusion

The example provided doesn’t evaluate arbitrary string as general VBA code, but it evaluates strings as Excel formulas using the built-in Evaluate function. In Excel VBA, the term “evaluate” primarily refers to calculating Excel formulas, not executing arbitrary VBA code. Here, we have tried to show you 2 possible scenarios with VBA to evaluate string as code in Excel. I hope this article has shed some light on your way to this. If you have any feedback regarding this article, please don’t forget to share them in the comment box.

Happy Excelling!