Use Evaluate In Vba Instead Of Looping Through Cells

May 28, 2021 - by Bill Jelen

Challenge: You need to change all the cells in a range based on a calculation. You are planning on looping through all the cells with this code:

Solution: The Evaluate function can perform this function faster than a loop. Replace the above code with this single line of VBA:

I used the Timer code to compare the two methods. The loop method required 8.3 seconds for 100,000 cells. The evaluate method ran in 0.09 seconds—a 99% improvement in processing time!

Breaking It Down: You might thinking that this is a cool function that could be used to quickly transform any range of data. Unfortunately, most of Excel’s functions will fail when used inside Evaluate. For example:

will fill the range with the lowercase version of just cell C2. The general rule is that if the Excel function does not normally accept an array, the Evaluate function will not return an array.

However, PGC01 at the MrExcel message board wrote an excellent tutorial, demonstrating how to coax Evaluate to work on a range by introducing an extra dummy range outside the function. PGC01 would use the following expression to solve the above problem:

In this case, ROW(2:99) returns the numbers from 2 to 99. When a logical test returns any numeric value other than 0, the result will be considered TRUE. Thus, the text inside the function is saying, “Here are 98 vertical true values. For each one, calculate the lowercase version of the corresponding cell from C2:C99.”

Additional Details: You can also use Evaluate to change a horizontal vector:

Using Evaluate on a rectangular range is a bit trickier. You need to introduce both a vertical array such as ROW(1:10) and a horizontal array such as either COLUMN(A:J) or TRANSPOSE(ROW(1:10)). The following code uses two IF functions, the first of which introduces a vertical array and the second of which introduces a horizontal array:

You can generalize this code to work on any range. The following code performs the UPPER function on all cells in the selection:

While the examples here deal with changing the case of text using UPPER, LOWER, and PROPER, you can use them to perform calculations with most of Excel’s functions.

Gotcha: Although this method improves the speed of your code substantially, it also makes your code far more difficult for someone else to understand.

Summary: You can use Evaluate to perform simple transformations on vector ranges.

Source: Change Positive Values To Negative on the MrExcel Message Board

Title Photo: Daniel Monteiro on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.