The Evaluate method can be used to calculate Excel worksheet formulas and generate references to Range objects. The normal syntax for the Evaluate method is as follows:


You can also use a shortcut format where you omit the quotes and place square brackets around the expression, as follows:


Expression can be any valid worksheet calculation, with or without the equal sign on the left, or it can be a reference to a range of cells. The worksheet calculations can include worksheet functions that are not made available to VBA through the WorksheetFunction object, or they can be worksheet array formulas. You will find more information about the WorksheetFunction object later in this chapter.

For instance, the ISBLANK function, which you can use in your worksheet formulas, is not available to VBA through the WorksheetFunction object, because the VBA equivalent function IsEmpty provides the same functionality. All the same, you can use ISBLANK if you need to. The following two examples are equivalent and return True if A1 is empty or False if A1 is not empty:

MsgBox Evaluate("=ISBLANK(A1)") MsgBox [ISBLANK(Al)]

The advantage of the first technique is that you can generate the string value using code, which makes it very flexible. The second technique is shorter, but you can only change the expression by editing your code. The following procedure displays a True or False value to indicate whether or not the active cell is empty, and illustrates the flexibility of the first technique:

Sub IsActiveCellEmpty()

Dim sFunctionName As String, sCellReference As String sFunctionName = "ISBLANK" sCellReference = ActiveCell.Address

MsgBox Evaluate(sFunctionName & "(" & sCellReference & ")") End Sub

Note that you cannot evaluate an expression containing variables using the second technique.

The following two lines of code show you two ways you can use Evaluate to generate a reference to a Range object, and assign a value to that object:

Evaluate("A1").Value =


[A1].Value = 10

The first expression is unwieldy and is rarely used, but the second is a convenient way to refer to a Range object, although it is not very flexible. You can further shorten the expressions by omitting the Value property, because this is the default property of the Range object:

More interesting uses of Evaluate include returning the contents of a workbook's Names collection and efficiently generating arrays of values. The following code creates a hidden name to store a password. Hidden names cannot be seen in the Insert O Name O Define dialog box, so they are a convenient way to store information in a workbook without cluttering the user interface:

Names.Add Name:="PassWord", RefersTo:="Bazonkas", Visible:=False

You can then use the hidden data in expressions like the following:

sUserInput = InputBox("Enter Password") If sUserInput = [Password] Then

The use of names for storing data is discussed in more detail in Chapter 5.

The Evaluate method can also be used with arrays. The following expression generates a Variant array with two dimensions, 100 rows and one column, containing the values from 101 to 200. This process is carried out more efficiently than using a For...Next loop:

Similarly, the following code assigns the values 101 to 200 to the range B1:B100, and again does it more efficiently than a For...Next loop:

0 0

Post a comment