VBA Evaluate

What is VBA Evaluate Function in Excel?

You can run Excel formulas or expressions from within your VBA code by using the VBA Evaluate function. This flexible function allows you to work with arrays, calculate values dynamically, and even run Excel functions. VBA Evaluate allows you to work directly with Excel’s calculation engine from your VBA code, providing a variety of opportunities for automation and data manipulation. In the example below, we will understand how to use the VBA Evaluate function to concatenate two strings, “Hello ” and “World!” using the “Concatenate” function.

In this code, firstly, a variable “concatenatedString” is declared to store the result of the concatenation. The VBA Evaluate function executes the “Concatenate” function with the specified strings as arguments and assigns the result to the “concatenatedString” variable. Finally, the concatenated string is shown in a message box.

What is VBA Evaluate Function in Excel?

Excel formulas or expressions can be dynamically calculated within VBA code using the VBA Evaluate function. This makes it possible to automate difficult computations and data processing jobs.
You can use the VBA Evaluate to handle a wide range of Excel formulas, including array formulas, functions, and mathematical expressions.
With the VBA Evaluate, you can efficiently handle array operations within VBA code. This is useful for performing calculations across multiple cells or ranges and processing large datasets effectively.
To enable dynamic evaluation of expressions or formulas stored as strings, use the VBA Evaluate String as Code function to treat the supplied string as executable code within Excel’s calculation engine.

Syntax

The syntax for the VBA Evaluate function is straightforward.

Evaluate (Name)

The name can be a formula or the name of an object that you want to evaluate. This name must not exceed 255 characters.

Note:

The VBA Evaluate method in Microsoft Excel VBA supports various types of names and references, including:

Type of Name	Description
Formulas	Allows execution of Excel formulas as strings.
A1-style references	Single-cell references in A1 notation.
Ranges	Use range, intersect, and union operators (colon, space, and comma) with references.
Defined names	Specify any name within the macro’s language.
External references	Use the “!” operator to refer to cells or names in another workbook.
Chart objects	Access properties and methods of chart objects like Legend, Plot Area, or Series.
Form Control number	Refer to Form Controls on a worksheet using numbers or names. For instance, both “Label 1” and “1” can be used interchangeably to refer to a Label Form Control.

How to Use VBA Evaluate Function?

Here’s how to use the Evaluate function in VBA:

Step 1: Open Excel and press ALT + F11 to open the VBA editor.

Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”

Step 3: In the new module window, you can write your VBA code that includes the VBA Evaluate function.

Step 4: Use the VBA Evaluate function in your VBA code to run Excel expressions or formulas. Give the VBA Evaluate function the desired formula or expression as a string input.

The outcome can be used directly in your code or saved in a variable.

For example:

result = Evaluate(“SUM(A1:A10)”)

Step 5: You can run your VBA code once you have completed the coding.

You can close the VBA editor window and execute the macro directly from the Excel workbook. Otherwise, click the “Run” button in the toolbar of the VBA editor.

Examples

Example #1

In this example, we will learn how to calculate the factorial of a given number dynamically using the VBA Evaluate function by constructing and evaluating the factorial expression and then displaying the result in a message box.

Step 1: In the new module, begin by creating a subroutine named “CalculateFactorial.”

Step 2: Next, declare a variable num as an Integer data type to store the number for which we want to calculate the factorial.

Step 3: Also declare a variable “factorialResult” as a Variant data type to store the result of the factorial calculation.

Step 4: In this step, assign the value 5 to the num variable, representing the number for which we want to calculate the factorial.

Step 5: Next, construct a string “factorialExpression,” representing the factorial expression dynamically using a loop.

This loop iterates from 2 to num and appends each number to the expression with the multiplication operator.

Step 6: Now, we will use the VBA Evaluate function to calculate the result of the factorial expression.

Step 7: Finally, we will use the message box function to display a message box showing the factorial of the specified number.

Step 8: When you run the code, it calculates the factorial of the specified number (5 in this case) and displays the result in a message box.

Here is the full code:

Sub CalculateFactorial()

Dim num As Integer

Dim factorialResult As Variant

num = 5

Dim factorialExpression As String

factorialExpression = “1”

For i = 2 To num

factorialExpression = factorialExpression & ” * ” & i

Next i

factorialResult = Evaluate(factorialExpression)

MsgBox “Factorial of ” & num & ” is: ” & factorialResult

End Sub

Example #2

In this example, we will use the VBA Evaluate function to retrieve the value stored in cell A1 of Sheet1 and then display the value in a message box alongside the label “Value in A1:”.

Step 1: Firstly, we start by defining a subroutine named CellReferences.

Step 2: In this step, a variable named “cellValue” of data type Variant is declared to store the value of the cell.

Step 3: Here, we will use the VBA Evaluate function to retrieve the value of cell A1 from Sheet1 and store it in the cellValue variable.

Step 4: Now, we use the message box function to display the value of cell A1.

Step 5: Finally, when you run the code, a message box in VBA will appear displaying the value of cell A1.

In this case, since the word “Hello World!” is mentioned in cell A1, the message box will display the same.

Here is the full code:

Sub CellReferences()

Dim cellValue As Variant

cellValue = Evaluate(“Sheet1!A1”) ‘ Evaluates the value in cell A1 of Sheet1

MsgBox “Value in A1: ” & cellValue

End Sub

Example #3

In this example, we will see the usage of the VBA Evaluate function to dynamically calculate the sum of a specified range of cells, i.e., cell A1 to cell A5 by constructing a SUM formula string, evaluating it, and then displaying the result in a message box.

Step 1: First, we start by creating a subroutine named “CalculateRangeSum.”

Step 2: Next, we declare a variable “sumResult” as a Variant data type to store the result of the sum calculation.

Step 3: Define another variable as “rangeAddress” to store the address of the range for which we want to calculate the sum (A1:A5).

Step 4: Next, write a string “formulaString” representing the SUM formula dynamically using the range address.

Step 5: In this step, we will use the VBA Evaluate function to calculate the sum of the specified range.

Step 6: We will use the message box feature to display a message box showing the sum of the specified range.

Step 7: Now, save the macro and click on run.

When you run the code, it calculates the sum of the specified range (A1:A5) dynamically and displays the result in a message box.

Here is the full code:

Sub CalculateRangeSum()

Dim sumResult As Variant

Dim rangeAddress As String

rangeAddress = “A1:A5”

Dim formulaString As String

formulaString = “=SUM(” & rangeAddress & “)”

sumResult = Evaluate(formulaString)

MsgBox “Sum of range ” & rangeAddress & ” is: ” & sumResult

End Sub

Important Things To Note

Be cautious when creating these strings dynamically, especially when they contain user input or external data sources, because the VBA Evaluate takes formulas or expressions as strings.
The VBA Evaluate has limitations even if it is flexible in managing Excel formulas and expressions. It can’t be used to run non-formulaic expressions, directly work with Excel objects, or control flow or conditional logic in VBA code.
You can evaluate Excel formulas built as strings within your VBA code by using the VBA Evaluate Formula String, which allows you to handle formula strings dynamically.

Frequently Asked Questions (FAQs)

Q1. Is there any difference between using Evaluate and directly referencing cell values in VBA?

Yes, there is a difference. In VBA, directly referring to cell values entails using attributes like “Range.Value” to directly access cell values. For straightforward operations, this approach is more effective because it avoids using the VBA Evaluate function’s overhead.

However, the VBA Evaluate is made especially to deal with intricate Excel formulas and expressions. It enables managing arrays, dynamic value calculation, and Excel function execution within VBA code.

Q2. Can I use Evaluate to execute non-formulaic expressions or statements?

No, the main purpose of the VBA Evaluate function is to assess Excel formulas and expressions. Non-formulaic expressions or statements cannot be directly executed using it.

VBA Evaluate can only handle Excel-specific syntax and functions and operates inside the framework of Excel’s calculation engine.

Q3. Can I use Evaluate to manipulate Excel objects like ranges or worksheets?

No, directly altering Excel objects like ranges or worksheets is not intended for the VBA Evaluate function. Its objective is to assess expressions or formulas used in Excel’s computation engine.

You should use the relevant VBA methods and properties offered for interfacing with Excel’s object model to manipulate Excel objects programmatically.

Q4. Can I use Evaluate in VBA to perform conditional logic or control flow operations?

No, conditional logic and control flow operations in VBA are not appropriate for the VBA Evaluate function.

Its purpose is to evaluate formulas or expressions only. Use VBA’s built-in conditional statements, such as “If, Then, Else, Select Case, or loop structures, such as For and While loops,” for conditional logic or control flow activities.

VBA Evaluate