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 & "),
_
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.