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

How are you calling `ShowHideSheets`? I.e. do you really need `ParamArray`? — BigBen, Oct 10 '19 at 17:50
@BigBen I just call it as a function inside a sub when needed. "ShowHideSheets(ARRAYDESIRED)" — iamstrained, Oct 10 '19 at 18:05
If you're only ever passing *one* array, then you don't need `ParamArray`. An array of parameters is different than a parameter that is an array. — BigBen, Oct 10 '19 at 18:06

rickmanalexander · Accepted Answer · 2019-10-10T19:29:29.397

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

My guess is that OP actually doesn't need `ParamArray` here at all. — BigBen, Oct 10 '19 at 18:12
@BigBen I doubt it as well, but in the interest of keeping his code very close to "as is", a simple conversion to `variant` would work. — rickmanalexander, Oct 10 '19 at 18:43
Based on [this answer](https://stackoverflow.com/a/33131162/9245853) you can't unhide an array of sheets in one step, so passing an array of sheet names would make the line `ActiveWorkbook.Sheets(VisibleToggle(i)).Visible = xlSheetVisible` fail. — BigBen, Oct 10 '19 at 18:54
@BigBen Is `VisibleToggle(i)` not a single element of the array, where said element is a single sheet Name? — rickmanalexander, Oct 10 '19 at 19:05
No, it is a variant array. `Debug.Print TypName(VisibleToggle(i))` - returns `Variant()`. You're calling `CVar` on something that's already a `Variant`. — BigBen, Oct 10 '19 at 19:06
@BigBen you mean it is still a jagged array. Yea I just realized, lol, See my edit. — rickmanalexander, Oct 10 '19 at 19:25

Passing Array to Function Requiring Transformation in VBA

1 Answers1