Reputation: 164
I've solved this with a work-around, but I'm sure I'm missing something basic here and would appreciate understanding why this is occurring in VBA Arrays.
I have a set of tabs in excel which are "admin sheets" - these I define in a public array to be able to share that single variable across a tool's various functions and subroutines.
I have a function which gets passed Variant arrays like the admin sheets variable, but if I do not transform the input array using an intermediate array (Redim to size and fit the underlying input) it creates an array that is annoying to iterate over.
Like: FunctionArray(0)(n) where all N objects of the passed aray get aligned to the first object in the function's input. I then have to transform it to an array so that FinalArray(n) holds the values as i originally wanted.
Surely I'm doing something wrong here.
Public AdminSheets As Variant
'here we define our tabs that need to be admin-only items.
AdminSheets = Array("Control Panel", "WBS Setup", "Staff Tables", "BOE Summary Output", "TEMPLATE", "PQ_Output", "Output")
Public Function ShowHideSheets(ParamArray TargetSheets() As Variant)
'Simply buckets the re-visible toggle of all sheets that are Admin Sheets.
'modify target array to fit consumable format for the iterator generically below.
'this step is not truly necessary if i change the iterator below, but i do it to make the array look the way i prefer when interfacing with Arrays. Without this step, the iterator below breaks.
Application.ScreenUpdating = False
Dim VisibleToggle() As String
ReDim VisibleToggle(UBound(TargetSheets(0)))
For X = 0 To UBound(VisibleToggle)
VisibleToggle(X) = TargetSheets(0)(X)
Next X
'here we do what i care about - modulate the tool to hide sheets from end users who don't need to see things.
Dim i As Integer
For i = 0 To UBound(VisibleToggle)
If ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetHidden Then
ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetVisible
ElseIf ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetVeryHidden Then
ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetVisible
ElseIf ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetVisible Then
ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetVeryHidden
End If
Next i
Application.ScreenUpdating = True
End Function
Upvotes: 0
Views: 158
Reputation: 601
Edit:
This is what I get for not testing first, lol.
Also, just a note Functions return things and subs "do stuff", so ShowHideSheets
should be a sub.
Saying that, as @BigBen said, you don't need a ParamArray
, but if you want to keep it, then you need to call it like so
Public Sub Test1()
ShowHideSheets "Control Panel", "WBS Setup", _
"Staff Tables", "BOE Summary Output", _
"TEMPLATE", "PQ_Output", "Output"`
End Sub
the other option is to do the following:
Public Sub Test2()
AdminSheets = Array("Control Panel", "WBS Setup", "Staff Tables", "BOE Summary Output", "TEMPLATE", "PQ_Output", "Output")
ShowHideSheets AdminSheets
End Sub
Public Sub ShowHideSheets(ByRef TargetSheets As Variant)
'Simply buckets the re-visible toggle of all sheets that are Admin Sheets.
'modify target array to fit consumable format for the iterator generically below.
'this step is not truly necessary if i change the iterator below, but i do it to make the array look the way i prefer when interfacing with Arrays. Without this step, the iterator below breaks.
'here we define our tabs that need to be admin-only items.
Application.ScreenUpdating = False
'here we do what i care about - modulate the tool to hide sheets from end users who don't need to see things.
Dim i As Integer
For i = 0 To UBound(TargetSheets)
If ActiveWorkbook.Sheets(TargetSheets(i)).Visible = xlSheetHidden Then
ActiveWorkbook.Sheets(TargetSheets(i)).Visible = xlSheetVisible
ElseIf ActiveWorkbook.Sheets(TargetSheets(i)).Visible = xlSheetVeryHidden Then
ActiveWorkbook.Sheets(TargetSheets(i)).Visible = xlSheetVisible
ElseIf ActiveWorkbook.Sheets(TargetSheets(i)).Visible = xlSheetVisible Then
ActiveWorkbook.Sheets(TargetSheets(i)).Visible = xlSheetVeryHidden
End If
Next i
Application.ScreenUpdating = True
End Sub
Original:
ParamArrays
become Jagged Arrays
when when passed from a function argument to another function. You can avoid this by converting it to a variant.
See below:
Public AdminSheets As Variant
'here we define our tabs that need to be admin-only items.
AdminSheets = Array("Control Panel", "WBS Setup", "Staff Tables", "BOE Summary Output", "TEMPLATE", "PQ_Output", "Output")
Public Function ShowHideSheets(ParamArray TargetSheets() As Variant)
'Simply buckets the re-visible toggle of all sheets that are Admin Sheets.
'modify target array to fit consumable format for the iterator generically below.
'this step is not truly necessary if i change the iterator below, but i do it to make the array look the way i prefer when interfacing with Arrays. Without this step, the iterator below breaks.
Application.ScreenUpdating = False
Dim VisibleToggle() As Variant
VisibleToggle() = CVar(TargetSheets) 'convert the paramarray to a variant
'here we do what i care about - modulate the tool to hide sheets from end users who don't need to see things.
Dim i As Integer
For i = 0 To UBound(VisibleToggle)
If ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetHidden Then
ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetVisible
ElseIf ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetVeryHidden Then
ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetVisible
ElseIf ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetVisible Then
ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetVeryHidden
End If
Next i
Application.ScreenUpdating = True
End Function
Upvotes: 2