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

Are you looking for ways to evaluate strings as code with Excel VBA? Don’t worry! You have landed in the right place. In order to evaluate a string as code in Excel, the VBA EVALUATE function is available. In this article, you will learn about the VBA EVALUATE function. You will also see some practical ways to evaluate a string as code in Excel.

📁 Download Practice Workbook

You can download the practice book from the link below.

Introduction to VBA EVALUATE Function in Excel

• Objective

The EVALUATE function is used in VBA for converting a name to an object or just a value.

• Syntax

• Argument

• Output

Converts a name to an object or a value.

• Version

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

2 Effective Ways to Evaluate String as Code with VBA in Excel

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. I will demonstrate them one by one here. Let’s check them now!

1. Evaluate String as Code 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?

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.

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.

Conclusion

In this article, I have tried to show you 2 possible methods with VBA to evaluate a string as code in Excel. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website ExcelDemy. We are always responsive to your queries.

Happy Excelling!