Databases Reference
In-Depth Information
Case 3
StrRange$ = "B8:D10, F8:H10"
End Select
Range(StrRange$).Select
End Sub
subroutine, an integer value is passed to the subroutine when
it is called. The Range to be selected will vary depending upon the value of the integer passed.
However, this could be streamlined even further by simply passing a string variable to the subroutine
that contains a valid range. The next subroutine demonstrates this:
In the
ReplaceStrInRange
Sub SelectPassedRange(ByVal CellRange$)
Range(CellRange$).Select
End Sub
which brings the discussion to the true point of focus, which is the flexibility of utilizing a
string variable to represent a range. The point here is that the programmer can “build” the
string variable to represent any Range that is desired. The following is an example to illustrate
the point.
Suppose it was desired to print out the letters of the alphabet to a Worksheet in a specific manner.
What if the specification called for a letter to be added to each cell columnwise, and after the third
column (C) was filled, the letters should be added restarting at the first column but one row down
(so as not to overwrite the previous additions)? So, row 1 would have A,B,C, and row 2 would have
D,E,F, until row 9 col B is reached whereby Z is written into this cell and the macro terminates.
Obviously, to accomplish this task, the range to which the Cells are written is changing for each
subsequent letter. Although this could be done utilizing the Cells form of the Range method, it is done
in the following subroutine by indirectly defining the limits of the range by using a string variable.
This importance of knowing how to do this is that not every property and method in Excel VBA is
capable of recognizing a cell address based on a variable parameter. Some only recognize the A1 style
[(column letter)(row number)] form of addressing. In many instances, building a string to represent
the address to be acted upon is the only way of coding a particular property of method. The Build-
StringRange subroutine shows how to build a string range to satisfy the previously defined specification.
Sub BuildStringRange()
Dim ii As Integer, CellRange$, ColPos As Integer
Dim RowPos As Integer
RowPos = 1
For ii = 0 To 25
If ii Mod 3 = 0 And ii <> 0 Then
ColPos = 0
RowPos = RowPos + 1
End If
ColPos = ColPos + 1
CellRange$ = Chr(64 + ColPos) & Trim(Str(RowPos))
'Debug.Print CellRange$
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSheet.Name).Range
(CellRange$).Value = Chr(65 + ii)
Next ii
End Sub
