Databases Reference
In-Depth Information
This application can continue running in the background and, as the user makes changes in the
environment, the user can click on the form and press the “Take System Snapshot” button at any
time to update the information contained within the GUI (or form).
The snapshot application updates when the user presses the “Take System Snapshot” button.
To give the reader an understanding of how the application can extract all of this information from
the Excel environment, the fundamental tasks that comprise the application will be discussed in
order of operations.
3.4
UTILIZING ARRAYS TO STORE DATA
The first thing that happens each time the “Take System Snapshot” button is pressed is that a series
of variables are declared that will store the information extracted from the Excel environment at
that particular moment. The code to do this is:
Dim HiddenWkbooks() As String, VisibleWkbooks() As String
Dim HiddenWorksheets() As String, VisibleWorksheets() As String
Dim TotalHidden As Integer, TotalVisible As Integer, ii As
Integer
Dim ChosenWorkbook As String
Notice that many arrays are declared without dimensions! It is possible to do this when the number
of dimensions that will be utilized is unknown. However, before the array can be utilized, its formal
size must be declared. This is done by means of the
statement. One caveat of the
ReDim
ReDim
statement is that, when an array is resized utilizing the
statement, all of the elements are
ReDim
reset to their default values
statement is used.
This statement resizes the array and erases all the elements:
unless
the
Preserve
ReDim visibleWorkbooks(1 To TotalVisibleWorkbooks)
This statement resizes an array and preserves all the elements:
ReDim Preserve visibleWorkbooks(1 To TotalVisibleWorkbooks)
Notice also that, in this instance, the lower bound and upper bound of each array element is
specified, in this case from 1 to a variable named
. This is an
exceptionally nice feature when one considers that it is often nice to begin a variable at a certain
index other than 1 or 0, which can be prespecified with the
TotalVisibleWorkbooks
statement.
In the snapshot example, four arrays have been defined, each of which will hold the contents
to be dumped into the combo boxes (or drop-down boxes utilized on the form). A variable is also
specified to keep track of the Chosen Workbook to be examined and the total number of visible
and hidden elements.
While on the subject of arrays, it is appropriate to discuss the functions that actually discern
the names and states of Workbooks and Worksheets that reside within the Excel environment at
any given time. These functions utilize arrays to return the names of Workbooks and Worksheets
that meet a certain criteria. Functions will be discussed at length in Chapter 4, but it is necessary
to utilize them in this example to develop a robust application. First, the Workbook functions:
Option Explicit
Function TotalVisibleWorkbooks(visibleWorkbooks() As String)
As Integer
Dim bookCounter As Workbook
For Each bookCounter In Application.Workbooks
If Windows(bookCounter.Name).Visible = True Then
TotalVisibleWorkbooks = TotalVisibleWorkbooks + 1
