Microsoft Office Tutorials and References

In Depth Information

**USE EVALUATE IN VBA INSTEAD OF LOOPING THROUGH CELLS**

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

Range("A1:J1") = Evaluate("if(row(1:10),upper(A1:J1))")

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 ﬁ rst of which introduces a vertical array and the second of which

introduces a horizontal array:

Range("A1:L23") = Evaluate("IF(ROW(1:23),IF(TRANSPOSE(ROW(1:

12)),LOWER(A1:L23)))")

You can generalize this code to work on any range. The following code performs

the
UPPER
function on all cells in the selection:

Sub RectangularProper()

‘ Convert all cells in the selection to proper case

Dim rngRectangle As Range, rngRows As Range, rngColumns

As Range

Set rngRectangle = Selection

‘ Deﬁ ne a vertical vector array

Set rngRows = rngRectangle.Resize(, 1)

‘ deﬁ ne a horizontal vector array

Set rngColumns = rngRectangle.Resize(1)

rngRectangle = Evaluate("IF(ROW(" & rngRows.Address & "),

_

IF(COLUMN(" & rngColumns.Address & _

"),PROPER(" & rngRectangle.Address & ")))")

End Sub

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 difﬁ cult for someone else to understand.

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

ranges.

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