Databases Reference
In-Depth Information
'Set CtrlRange Range object to the range address passed
Set CtrlRange = Range(RangeAddress)
'Convert Range Address to R1C1 Format
RangeAddress = CtrlRange.Address(, , xlR1C1)
End If
If InStr(RangeAddress, ":") = 0 Then
ColsinRange = 1
Exit Function
Else
topleft$ = Mid(RangeAddress, 1, InStr(RangeAddress, ":") - 1)
bottright$ = Mid(RangeAddress, InStr(RangeAddress, ":") +
1, Len(RangeAddress))
End If
'Determine Starting Column
For ii = 1 To Len(topleft$)
If Mid(topleft$, ii, 1) = "C" Then
startcol = Val(Mid(topleft$, ii + 1, Len(topleft$)))
Exit For
End If
Next ii
'Determine Ending Column
For ii = 1 To Len(bottright$)
If Mid(bottright$, ii, 1) = "C" Then
endcol = Val(Mid(bottright$, ii + 1, Len(bottright$)))
Exit For
End If
Next ii
ColsinRange = (endcol - startcol) + 1
End Function
First, these functions look at the range passed to them and determine if it is an R1C1 type of
range or an A1 type of range. It would create extra work to build in the ability to function using
both types of ranges, so the mechanism was constructed to convert an A1 type of range to an R1C1
type of range. If an absolute type (A1) of range is passed, it will be converted to an R1C1 type of
range. (Note, the mechanism built in looks for the “$” in the absolute type of range, so it will not
function to convert a relative style A1 range. This suffices in this case because the RefEdit control
always returns an absolute type of A1 range.)
A check is then done to determine if the range is limited to a single cell. If it is, then the
number of rows or columns is set to one. If it is not, then the range is broken into two parts: the
top left and bottom right corners of the range. Once that has been accomplished, the rows and
columns can be extracted from the two parts of the range. A simple subtraction then determines
the number of rows or columns.
A second button is present in this sample application titled “Select Range”. If this button is
pushed, the range present in the RefEdit box will be selected (or made active) on the active
Worksheet. This is accomplished with one simple line of code:
Range(RefEdit1.Text).Select
