Databases Reference
In-Depth Information
TABLE 1.3
Column Letter and Number Reference Equivalents
ABC D E F GH I J K L M NO P QR S TUVWXYZ
123456789 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6
This short macro brings several techniques to the forefront that will be utilized throughout this
text. First, notice that the For loop is utilized to transition from column to column in this example.
Also notice how the Cells method references individual Cells. The Cells method is followed by
two numbers separated by commas in parentheses. The first number is the row (which, in this
example, is fixed at one or two), and the second number is the column. When taking a linear algebra
class, a professor said it was good to memorize the following statement to remember how to
multiply a matrix: “Rows by Columns, write in Rows.” If this statement is committed to memory,
the first statement is how the Cells form of the Range method references a cell: Cells(row, col).
Notice also that there are two ways of utilizing the Cells method to point to a single cell in a
Worksheet. The first utilizes just the Cells method, and the second utilizes the Cells method as part
of a range.
Using the Cells method is the simplest and easiest way to reference an individual cell. Here
the Cells method directly follows the Worksheet reference, and only the coordinates for a single
row and column must follow it as in this statement:
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSheet.Name).Cells
(1, ii).Value = Chr(64 + ii)
In this example, it is worth pointing out that both the column and the row references could be
replaced by variables that could be altered by means of a loop or another method. Here the row is
fixed (at 1), and the column varies as the loop
increments.
The Cells method can also be incorporated as part of the Range method as shown in this
statement (which was commented out in the CreateColEqTable subroutine):
ii
'Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSheet.Name).
Range(Cells(2, ii), Cells(2, ii)).Value = ii
Notice here that the Cells method is nested inside the Range method. Also notice that the Cells
method occurs twice inside the Range method. This must always be the case. The first Cells
statement defines the upper left corner of a rectangular Range. The second Cells statement defines
the lower right-hand corner of a rectangular Range. To reference a single cell, both Cells
statements must point to the same cell, resulting in a single-cell selection bounded by itself.
Many VBA programmers run into confusion (and rightly so) when they try to implement a
statement such as this:
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSheet.Name).
Range(Cells(2,2)).Value = 100
On its face this statement seems completely reasonable. Clearly, here the programmer wishes to assign
the value 100 to the cell in the second row of column B. Trying to execute this statement will result
in an error every time. For whatever reason, Excel was designed so that when the Cells form of the
Range method is utilized, two instances of the Cells method must appear within the Range method
even if the Range is to reference a single cell. The only valid way to write such a statement would be
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSheet.Name).
Range(Cells(2,2), Cells(2,2)).Value = 100
