Databases Reference
In-Depth Information
Whenever functions are to be built utilizing a range of data, the foregoing function should be
used to ensure that the selected range does not contain any empty Cells. If empty Cells are
encountered in the selected range, the function will return the Boolean value True. The function
works by calculating the total number of Cells in the range bounded by the passed parameters:
toprow, leftcol, bottomrow, rightcol. The CountA method is then utilized to determine the number
of nonempty Cells within the bounded range. If the total number of Cells minus the nonempty
Cells is equal to zero, then every cell in the range bounded by the passed parameters contains
something.
1.5
USING EXPLICIT REFERENCING
Notice that, in many of the earlier examples, the Workbooks and Worksheets that a range was to
focus on were indicated explicitly in the coding as in the following example.
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSheet.Name).Range
(CellRange$).Value = “Cell Contents”
In this statement, the portion of code
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSheet.Name)
is redundant because, if the Workbook and Worksheet to be accessed are not specified, the active
Workbook and Worksheet will be acted upon. For example, the Range method can be called to act
upon the active Workbook and Worksheet without specifying them as in this example:
Range("A1").Select
Here, row 1 column A will be selected in the active Workbook and Worksheet. So, what then is
the sense in specifying the Workbook and Worksheet within the code? The obvious point is that it
makes the code more readable to anyone who is trying to debug the application. Such a coding
scheme has far wider implications.
The Workbook name and Worksheet name, much like the range method, can also be represented
by a string variable. The significance of this is that they can be passed as a parameter to any
subroutine or function. The implication of this is that subroutines can be written to perform generic
functions on any Worksheet regardless of the Workbook it resides in. The next subroutine illustrates
how this can be done.
Sub ExplicitReference(ByVal wkbook$, ByVal wksheet$)
'An Example of Using Explicit Referencing
Workbooks(wkbook$).Worksheets(wksheet$).Range("A1").Value = "A"
Workbooks(wkbook$).Worksheets(wksheet$).Range("A2").Value = "B"
Workbooks(wkbook$).Worksheets(wksheet$).Range("A3").Value = "C"
End Sub
Here the letters A,B,C will be written down the first column (A) of the Worksheet, and Workbook
specified with the passed parameters
wkbook$
(Workbook) and
wksheet$
(Worksheet). But
why specify the Workbook and Worksheet if one will be acting upon the active Workbook and
Worksheet pair anyway? Although this does require a little bit of extra effort on the part of the
programmer, it adds enormous utility to the code that is written in that it can be “recycled” to be
utilized in other applications. Subroutines written in this manner can perform their operations on
any Workbook/Worksheet pair and are not limited in their scope of operation. It is always best to
think in terms of not just writing the code to accomplish the task at hand but how to write the code
in such a manner that its usefulness is not limited. In the instance where the active Workbook/Worksheet
pair are to be utilized, a call can be made to the subroutine in the following manner:
Call ExplicitReference(ActiveWorkbook.Name, ActiveSheet.Name)
