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
Search JabSto ::




Custom Search