In this tutorial, I am going to share with you **4** practical examples of how to use the **VBA Evaluate function in Excel**. You can easily apply these examples in any set of data to perform various formula-based calculations. To achieve this task, we will also see some useful features that might come in handy in many other Excel-related tasks.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here.

## Overview of Excel VBA Evaluate Function

**Summary**

The **EVALUATE **function in **Excel** **VBA **allows you to execute a formula stored as a string. This can be useful when you want to dynamically generate a formula based on certain conditions or input values.

**Syntax**

`Evaluate (Name)`

**Arguments**

Argument | Required/Optional | Explanation |
---|---|---|

Name |
Required | This could be a formula name or the name of an item (like a cell address). The Name cannot exceed 255 characters in length. |

**Return Parameter**

The **EVALUATE **function in Excel **VBA **returns a variant data type, which can contain any kind of data (e.g., a number, a string, an array, etc.). The specific data type that the function returns depend on the formula you pass to it as an argument.

**Available In**

The **EVALUATE **function has been available in Excel since at least Excel 2010.

## 4 Practical Examples to Use VBA Evaluate Function in Excel

We have taken a concise initial dataset to explain the steps clearly. The dataset has approximately **7 **rows and **3 **columns. Initially, we are keeping all the cells in **General **format and the monetary values in Accounting format. For all the datasets, we have **3 **unique columns which are **Product**,** Price**, and **Sales (units)**. Although we may vary the number of columns later if that is needed.

### 1. Using Evaluate with SUMIFS Function

We can use the **EVALUATE function** in **Excel VBA** to execute a **SUMIFS **formula stored as a string, which can be useful when you want to dynamically generate the formula based on certain conditions or input values. This can help you save time and simplify your **VBA **code.

**Steps:**

- Next, go to the
**Insert**tab and click on**Module**.

- Now, in the module window, insert the following code:

```
Public Sub SUMIFS()
Dim Sh_result As Double
Sh_result = Evaluate("SUMIFS(D5:D9,C5:C9,"">450"")")
Range("D10").Value = Sh_result
End Sub
```

- After that, navigate to the
**Developer**tab and click on**Macros**.

- Then, select the
**SUMIFS**macro and click on**Run**.

- Finally, this will evaluate the
**SUMIFS**formula and give you the total sales unit values.

### 2. Applying VBA Evaluate with SUMPRODUCT Function in Excel

In this second example, we will look at a simple way to use the **VBA Evaluate **function with **the SUMPRODUCT function **in **Excel**.

**Steps:**

- To begin with, open the
**VBA**module window following the steps of**Example 1**. - Now, insert the following code:

```
Public Sub SUMPRODUCT()
Dim Sh_result As Double
Sh_result = Evaluate("SUMPRODUCT(D5:D9,C5:C9)")
Range("D10").Value = Sh_result
End Sub
```

- Then select the
**SUMPRODUCT**macro and click on**Run**.

- As a result, this will give the total earnings inside cell
**D10**.

### 3. Implementing Evaluate with COUNTIF Function

Let us now see in this third example how we can use the **Evaluate function **in **VBA **along with **the COUNTIF function** in **Excel **to perform simple calculations.

**Steps:**

- Firstly, open the
**VBA**module window following the steps of**example 1**. - Then, type in the following code in the module window:

```
Public Sub COUNTIF()
Dim Sh_result As Double
Sh_result = Evaluate("COUNTIF(C5:C9,"">100"")")
Range("C10").Value = Sh_result
End Sub
```

- After that, choose the
**COUNTIF**macro and press**Run**.

- Consequently, you should get the count of categories that are over
**$100**in sales value.

### 4. Using VBA Evaluate with SUM Function in Excel

Now in this last example, we will show a quick way to use the **VBA Evaluate **method with **the SUM function **in **Excel**.

**Steps:**

- To start with, open the
**VBA**module window following the steps of**example 1**. - Now copy and paste the following code into the module window:

```
Public Sub SUM()
Dim Sh_result As Double
Sh_result = Evaluate("SUM(C5:C9)")
Range("C10").Value = Sh_result
End Sub
```

- After that select the
**SUM**macro from the list and click on**Run**.

- As a result, you should get the total marks value inside cell
**C10**.

## Limitations of Evaluate Function

- Only formulas stored as strings can be executed by the
**EVALUATE**This implies that you are unable to directly provide cell references or ranges. - Formulas that are compatible with Excel can only be used with the
**EVALUATE**It will give you an error if you pass it an invalid formula. - Excel formulas that exceed the capabilities of its calculation engine cannot be used with the
**EVALUATE**For instance, it might run out of memory or surpass Excel’s computation restrictions if you provide it with a formula that uses a very large or complicated array.

## Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to use the **VBA Evaluate function in Excel**. As you can see, there are quite a few ways to apply this function. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more **Excel** techniques, follow our **ExcelDemy** website. If you have any queries, please let me know in the comments.