Databases Reference
In-Depth Information
ReDim Preserve visibleWorkbooks(1 To
TotalVisibleWorkbooks)
visibleWorkbooks(TotalVisibleWorkbooks) =
bookCounter.Name
'Debug.Print TotalVisibleWorkbooks,
visibleWorkbooks(TotalVisibleWorkbooks)
End If
Next bookCounter
End Function
Function TotalHiddenWorkbooks(hiddenWorkbooks() As String) As
Integer
Dim bookCounter As Workbook
For Each bookCounter In Application.Workbooks
If Windows(bookCounter.Name).Visible <> True Then
TotalHiddenWorkbooks = TotalHiddenWorkbooks + 1
ReDim Preserve hiddenWorkbooks(1 To TotalHiddenWorkbooks)
hiddenWorkbooks(TotalHiddenWorkbooks) = bookCounter.Name
'Debug.Print TotalHiddenWorkbooks,
hiddenWorkbooks(TotalHiddenWorkbooks)
End If
Next bookCounter
End Function
The most striking thing about these two functions is probably the statement declaring the
variable
bookCounter
as type
Workbook
. What does this mean? This is an object variable
declaration statement. Excel is made up essentially of a collection of objects such as Workbooks,
Worksheets, forms, objects that reside in forms, etc. Utilizing objects variables allows access to all
of an object's properties and methods, but there is one barrier to utilizing this functionality. The
use of object variables is very poorly documented, and it is not intuitive on how to accomplish
many tasks utilizing them. However, utilizing object variables can allow the developer to access
the entire range of properties and methods related to an object and take action on them with ease.
Such is the case with these functions.
In these two functions, a loop is constructed utilizing the
bookCounter
object variable that
loops through every Workbook present (or loaded) in the Excel environment (even if it is a hidden
Workbook). Every Workbook is then checked as to its visible status. If the Workbook is visible
and the function is to report visible Workbooks, then the total number of visible Workbooks is
incremented, and the array that stores the names of the visible Workbooks is redimensioned to a
size reflecting the total number of visible Workbooks. The name of the Workbook is then stored
in the array. When all of the Workbooks have been looped through, the function ends, returning
the total number of visible Workbooks as an integer.
It also returns the array that contains all of
the Workbook names
. This is accomplished by passing an array to the function
By Reference
, which
will be discussed in detail in the next section of this chapter. The functions to discern the names
and states of the visible and hidden Worksheets that are present within the Excel environment work
in a similar fashion.
Function TotalVisibleWorksheets(VisibleWorksheets() As String,
wkbook As String) As Integer
Dim wks As Worksheet
