Page 58 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 2 FUNDAMENTALS OF PROGRAMMING WITH VBA 35

Range("D1: D20").Copy (Sheets("Sheet1 5").Range("Al")) I
Figure 2-13. A more efficient way to accomplish the same thing, without selecting cells.

References Using the Union or Intersect Method
VBA can create references by using methods that are the equivalents of the
union operator (the comma) or the intersection operator (the space character)
that can be used in worksheet formulas. The worksheet union operator creates a
reference that includes multiple selections, for example, SUM(A1 ,B2,C3,D4,E5).
The syntax of the corresponding VBA Union method is Union(range1,
range2,. . .). The worksheet intersection operator creates a reference that is
common to two references (e.g., the expression F4:F6 E5:E returns the reference
F5). The syntax of the corresponding VBA Intersect method is
Intersect(range7, range2). Both range1 and range2 must be range objects.
Examples of Expressions to Refer to a Cell or Range

1. Using the Range keyword with an address
Range("B1:DlO")

2. Using the Cells keyword with row and column numbers
Cells( 15, 5)
This expression refers to cell El 5.

3. Using the Range keyword with a range name
Range("addr1")
The range name addrl was assigned previously using Insert+Name-+
Define. This method is useful if the user can possibly modify the spreadsheet so
that the addresses of cells needed by the procedure are changed.

4. Using the Cells keyword with variables
Cells( RowN urn, COIN urn)

5. Using the Range keyword with a variable
Range(addr2)

The variable addr2 was previously defined by means of a statement such as
addr2 = Selection.Address