Databases Reference
In-Depth Information
Workbooks(wkbook).Activate
For Each wks In Worksheets
'Debug.Print wks.Name, wks.Visible
If wks.Visible = -1 Then
TotalVisibleWorksheets = TotalVisibleWorksheets + 1
ReDim Preserve VisibleWorksheets(1 To
TotalVisibleWorksheets)
VisibleWorksheets(TotalVisibleWorksheets) = wks.Name
'Debug.Print TotalVisibleWorksheets,
VisibleWorksheets(TotalVisibleWorksheets)
End If
Next wks
'Debug.Print TotalVisibleWorksheets
End Function
Function TotalHiddenWorksheets(HiddenWorksheets() As String,
wkbook As String) As Integer
Dim wks As Worksheet
Workbooks(wkbook).Activate
For Each wks In Worksheets
If wks.Visible = 0 Then
TotalHiddenWorksheets = TotalHiddenWorksheets + 1
ReDim Preserve HiddenWorksheets(1 To
TotalHiddenWorksheets)
HiddenWorksheets(TotalHiddenWorksheets) = wks.Name
Debug.Print TotalHiddenWorksheets,
HiddenWorksheets(TotalHiddenWorksheets)
End If
Next wks
End Function
Here, a Worksheet object variable is used to access the Worksheet properties and loop through
all the Worksheets in a particular Workbook. The Workbook to be examined is passed into the
function as the string parameter
wkbook
. Each Workbook to be examined is then made into the
active Workbook in the Excel environment. Similar to the previous two functions, an array is
utilized to store the names of Worksheets that are either hidden or visible, depending on the
function.
3.5
PASSING PARAMETERS BY VALUE OR BY REFERENCE?
In the previous section, the four functions utilized arrays to carry information extracted in the
functions back to the main body of the program, or alternatively, it could be said that arrays were
utilized to store information extracted in the functions. In the previous four functions, these arrays
were passed by reference. This section will articulate the meaning of passing a parameter by
reference and passing a parameter by value. To illustrate the difference between the two parameter-
passing paradigms, a simple example will be given. In this example, index cards will represent a
passed parameter (variable), and people will represent the subroutines.
