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

more.

Source:
http://www.mrexcel.com/forum/showthread.php?t=240975

USE EVALUATE IN VBA

INSTEAD OF LOOPING THROUGH CELLS

Part

3

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

Next cell

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

C2:C99.”