Passing Array to Function Requiring Transformation in VBA

Question

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

Answer 1

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