Microsoft Office Tutorials and References
In Depth Information
USE EVALUATE IN VBA INSTEAD OF LOOPING THROUGH CELLS
You can then use =myenviron("COMPUTERNAME") in a cell in Excel.
Summary: On a Windows PC, the VBA Environ function provides information
about the path to the temp folder, the path to the application data folder, and
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:
For Each cell In Selection
cell.Value = -1 * cell.Value
Solution: The Evaluate function can perform this function faster than a loop.
Replace the above code with this single line of VBA:
Selection.Value = Evaluate(Selection.Address & "*-1")
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:
Range("C2:C99").Value = Evaluate("lower(C2:C99)")
will ﬁ ll 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:
Range("C2:C99").Value = Evaluate("if(ROW(2:99),LOWER(C2:C99))")
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