Databases Reference
In-Depth Information
FIGURE 1.7
The BuildStringRange subroutine uses string-defined ranges.
The result of running the BuildStringRange subroutine can be viewed in Figure 1.7. It is important
to understand the methodology employed within this macro to manipulate the cell range by utilizing a
string variable. Notice that the row and column positions are “built” from the numeric variables
ColPos
and
RowPos
. Further notice that the value of these variables is controlled by decisions rendered in the
ii
looping structure. The range is then built from the numeric variables with the statement
CellRange$ = Chr(64 + ColPos) & Trim(Str(RowPos))
Notice that the column position is built using the
Chr()
function. Looking at Table 1.4,
notice that the decimal equivalent of the character A is the value 65. The statement:
Debug.Print
Chr(65)
will print “A” to the immediate (debug) window. In this example,
the
ColPos
variable increments from 1 to 3. To return the corresponding character represen-
tative of the column to be included in the Range function, the
ColPos
variable is added to 64
in the
Chr()
function. This will result in Chr(65), Chr(66), or Chr(67) being called, which
will return an “A,” “B,” or “C,” respectively.
Also notice the snippet of code
Trim(Str(RowPos)
), which builds the row position of
the
CellRange$
variable. Here the numeric variable
RowPos
is converted to a string by means
of the
Str()
function. Most importantly, note the use of the
Trim()
function on the string
returned by the
Str
() function. When the
Str()
function returns a string form of a number, a
leading space is always present for a positive number. (This space is reserved for a negative sign
(
) should the string returned be from a negative number.) If this leading space is not removed
when the cell range string is being built, the result will be an invalid cell range because an
unintended space will be present in the range.
Since using the A1 form [(column letter)(row number)] of addressing can be the only way of
coding a particular property or method, it is useful to have a subroutine that will take a column
number and return the equivalent alphabetical column reference. The function
ColNoToColLet
accomplishes this task. This function takes into account the fact that column numbers greater than
26 are referenced by adding an incrementing second letter. For example, if 27 is passed to the
function, it will return “AA,” 28 <-> “AB,” 29 <-> “AC,” etc. …
−
Function ColNoToColLet(ByVal ColumnNo As Integer) As String
'This function will convert any column # to the letter Equivalent
If ColumnNo < 27 Then
