In the article, titled VBA Evaluate Array Formula, we’ll look at three effective ways to use VBA’s Evaluate function to efficiently manipulate data in Excel.
The first technique enables quick and dynamic calculations by directly assigning the Evaluate function’s output to cells. The second approach emphasizes assigning Evaluate to a variable, giving users more freedom in how they store and use the calculated results. Finally, we’ll show you how to build a custom function that treats VBA Evaluate like an array and provides a reusable solution for tricky data operations.
This methods will help you to efficiently perform complex calculations involving arrays of data within Excel.
How to Use VBA Evaluate as an Array Formula in Excel
With the help of the VBA Evaluate as an Array function, formulas can be executed as arrays in VBA code, processing data quickly and carrying out complex calculations without loops. You can use the Evaluate function in VBA as follows:
- Application.Evaluate
- Worksheets(“Sheet1”).Evaluate
- .Parent.Evaluate
While using Evaluate as an Array function in the above different ways, there might be minor differences in execution time due to subtle context changes. The overall impact on performance is minimal. The execution time primarily depends on the complexity of the evaluated expression rather than the specific method used for evaluation.
1. Assigning VBA to Evaluate as an Array Formula Directly to Cells
- First of all, press Alt + F11 to open the VBA Macro.
- Click on the Insert.
- Select the Module. Moreover, you can click here to learn about launching the VBA. window.
- Copy the following code and paste it into Module 1.
Sub Evaluate()
Dim dataRange As Range
' Set the data range to exclude the header row (B5:E14 instead of B4:E14)
Set dataRange = Sheet1.Range("B5:E14")
With dataRange.Columns
.Item(4).Value2 = .Parent.Evaluate("=(" & .Item(2).Address & "+" & .Item(3).Address & ")") End With
End Sub
VBA Explanation
Sub Evaluate()
The code defines a Subprocedure named “Evaluate”.
Dim dataRange As Range
It declares a variable “dataRange” of type Range to hold a reference to a specific range of cells in “Sheet1”.
Set dataRange = Sheet1.Range(“B5:E14”)
The “Set” keyword is used to assign the “dataRange” variable to the range “B5:E14” in “Sheet1”. The header row is excluded from the range.
With dataRange.Columns
.Item(4).Value2 = .Parent.Evaluate(“=(” & .Item(2).Address & “+” & .Item(3).Address & “)”) End With
The code then enters a “With” block, referring to the columns of the “dataRange” variable. Inside the “With” block, it sets the value of the cell in the 4th column (D column) of the “dataRange” to the result of an evaluation performed by the “.Parent.Evaluate” method.
The evaluation is based on a formula constructed using the cell addresses of the 2nd column (B column) and 3rd column (C column) of the “dataRange”.
End Sub
Ending of the Subprocedure
- Save your module and run the code (press F5).
- Therefore, you will see that Total Sales ($) in range E5:E14 is calculated in ‘Sheet1’.
- Moreover, you can get a similar result by using the following VBA code. The third brace in the formula “[INDEX(C5:C14+D5:D14,)]” indicates an array formula.
Sub INDEX()
Sheet4.Range("E5:E14") = [INDEX(C5:C14+D5:D14,)]
End Sub
2. Assigning VBA Evaluate as an Array to a Variable
- Again, select a Module.
- Copy the following code and paste it into Module 2.
Sub Total_Price()
Dim Col1 As String, Col2 As String, Col3 As String
With Sheet2.Range("B5:E14").Columns
Col1 = .Item(1).Address
Col2 = .Item(2).Address
Col3 = .Item(3).Address
' Use the Evaluate function to calculate the result of the formula
Dim result As Variant
result = .Parent.Evaluate("=(" & Col2 & "+" & Col3 & ")")
' Set the calculated result in the 4th column (D column) of the range
.Parent.Range(.Item(4).Address).Value = result
End With
End Sub
VBA Explanation
The “Total Price” Sub procedure operates on a range of cells in “Sheet2” (B5:E14) and extracts the addresses of its 1st, 2nd, and 3rd columns into variables Col1, Col2, and Col3, respectively. It then uses the “Evaluate” function to calculate the addition of values from the 2nd and 3rd columns. The result is stored in the “result” variable. Finally, the calculated result is written to the 4th column (D column) of the range in “Sheet2,” updating the values accordingly.
- To run the code, save your module and press F5 or the run button.
- As a result, Total Sales ($) in the range E5:E14 is calculated in ‘Sheet2‘, as you can see.
Read More: How to Evaluate String as Code with Excel VBA
3. Using a VBA Custom Function to Evaluate Array as Formula
- Select a Module once more.
- You need to copy and paste the following code into Module 3.
Function MyEvaluate(rng1 As Range, rng2 As Range) As Variant
Dim result() As Variant
Dim i As Long
ReDim result(1 To rng1.Rows.Count, 1 To 1)
For i = 1 To rng1.Rows.Count
result(i, 1) = rng1.Cells(i).Value + rng2.Cells(i).Value
Next i
MyEvaluate = result
End Function
Sub EvaluateAndAssign()
Dim dataRange As Range
Set dataRange = Sheet1.Range("C5:D14")
Dim result As Variant
result = MyEvaluate(dataRange.Columns(1), dataRange.Columns(2))
dataRange.Offset(0, 2).Value = result = my
End Sub
VBA Explanation
Function MyEvaluate(rng1 As Range, rng2 As Range) As Variant
This is a custom VBA function named MyEvaluate that takes two Range objects as its arguments: rng1 and rng2. It returns a Variant data type (which allows for different types of data).
Dim result() As Variant
This line declares a dynamic array variable result to store the calculated values.
Dim i As Long
This line declares a variable i of data type Long to be used as a loop counter.
ReDim result(1 To rng1.Rows.Count, 1 To 1)
This line resizes the result array to match the number of rows in the input ranges rng1 and rng2. It creates a 2-dimensional array with one column and the same number of rows as the input ranges.
For i = 1 To rng1.Rows.Count
This line starts a loop that iterates from 1 to the number of rows in rng1.
result(i, 1) = rng1.Cells(i).Value + rng2.Cells(i).Value
Inside the loop, the code adds the values of cells in the corresponding rows of rng1 and rng2 and stores the result in the result array.
Next i
This line marks the end of the loop.
MyEvaluate = result
After the loop, the function returns the result array containing the calculated values.
Sub EvaluateAndAssign()
This is another VBA subroutine named EvaluateAndAssign.
Dim dataRange As Range
This line declares a Range variable dataRange.
Set dataRange = Sheet1.Range(“C5:D14”)
This line sets the dataRange to reference the range “C5:D14” on “Sheet1”.
Dim result As Variant
This line declares a variable result of data type Variant.
result = MyEvaluate(dataRange.Columns(1), dataRange.Columns(2))
This line calls the MyEvaluate function with the first and second columns of dataRange as arguments and assigns the result to the result variable.
dataRange.Offset(0, 2).Value = result = my
This line assigns the calculated values in the result to the third column (two columns to the right) of dataRange.
The purpose of this code is to create a custom function MyEvaluate that can add two ranges element-wise and then use it in the EvaluateAndAssign subroutine to calculate the sum of the corresponding elements of columns C and D in the range “C5:D14” on “Sheet1” and store the result in the third column (two columns to the right) of the same range.
- Now you need to save the code and close the module.
- As a result, to find the Total Sales in the range E5:E14 you need to enter the following formula.
=MyEvaluate(C5:C14,D5:D14)
Things to Remember
Array Formula Syntax: Make sure the formula syntax is appropriate for VBA’s “Evaluate” method before using array formulas. However, comparing the formulas to the default Excel array formulas may require some adjustments.
Data Range Selection: When using array formulas, be careful to choose the proper data range. Because of using the wrong range, it may produce unexpected outcomes or errors.
Error Handling: To handle scenarios where the array formula might not yield the desired results, you need to implement proper error handling in your VBA code.
Frequently Asked Questions (FAQs)
Q1: Can I perform sophisticated mathematical operations using VBA evaluate array formulas?
Answer: Yes, VBA evaluating array formulas can handle complex mathematical operations such as statistical calculations, matrices, and more. Moreover, they are extremely useful for complex data analysis tasks.
Q2: Can I use VBA to evaluate array formulas with non-continuous ranges?
Answer: Yes, VBA evaluates array formulas that can work with both continuous and non-continuous ranges. Also, ensure that you properly define the ranges in the array formula to include all the necessary data elements.
Download Practice Workbook
You can download the practice workbook from the following download button.
Conclusion
In a nutshell, VBA Evaluate Array Formula offers a strong and effective way to handle modifying data and complex calculations. We looked at three different approaches to using VBA Evaluate as an array function. Each approach has its benefits and can be used depending on what is going on. The goal of this blog post was to give readers the knowledge and resources they need to fully utilize VBA Evaluate for array formulas, enabling them to manage large data sets and difficult calculations with ease. As you gain skills with VBA Evaluate, you’ll find new opportunities to simplify your Excel workflows and unleash the full capabilities of this powerful tool.
