Microsoft Office Tutorials and References

In Depth Information

**16.1.8.3 Goto method**

[A1].Value = "Mary"

The first line uses the Range method. The second line will produce an error because Excel

considers A1 a variable rather than a cell reference. The third line uses the Evaluate

method to convert the name of a cell to a Range object. The fourth line is shorthand for

the third line.

A range

Name
can be any range formed by using the range operator (colon), intersect operator

(space), and union operator (comma) with references. The Evaluate method will return

the corresponding Range object. To illustrate, consider the following code:

Evaluate("B2:C4").Select

Evaluate("B2:C4, D5:F6").Select

Evaluate("B2:C4 B1:F2").Select

[B2:C4 B1:F2].Select

The first line selects the range B2:C4. The second line selects the union of the two

rectangular ranges B2:C4 and D5:F6. The third line selects the intersection of the two

rectangular ranges B2:C4 B1:F2. The fourth line is shorthand for the third line.

A Defined Name

Name
can be any defined name. For instance, if we name a range
test
, then the

following code selects that range:

Evaluate("test").Select

(Incidentally, I have had some inconsistent results using the syntax
[test].Select
. It

seems to work some but not all of the time.) We can also use formula names. For instance,

the following code displays the sum of the values in cells B2 through B5:

MsgBox Evaluate("SUM(B2:B5)")

Note that external references (references to other workbooks) can be used as well, as in:

Workbooks("BOOK2.XLS").Sheets("MySheet").Evaluate("A1").Select

As we have seen, using square brackets is equivalent to calling the Evaluate method with a string

argument. Square brackets have the advantage of producing more concise code, but they cannot be

used with string variables. For instance, we can write:

Dim sFormula As String

sFormula = "SUM(B2:B5)"

MsgBox Evaluate(sFormula)

But the code:

MsgBox [sFormula]

will simply display the string SUM(B2:B5), as it would without the square brackets.

16.1.8.3 Goto method