Nazmul Hossain Shovon
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.
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.
