Use Evaluate In Vba Instead Of Looping Through Cells


May 28, 2021 - by Bill Jelen

Use Evaluate In Vba Instead Of Looping Through Cells

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:

e9781615474011_i0246.jpg

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

e9781615474011_i0247.jpg

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:

e9781615474011_i0248.jpg

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:

e9781615474011_i0249.jpg

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:

e9781615474011_i0250.jpg

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:

e9781615474011_i0251.jpg

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

e9781615474011_i0252.jpg

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.



Bill Jelen is the author / co-author of:
MrExcel 2021 – Unmasking Excel

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.