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
– BigBen, Commented Oct 10, 2019 at 17:50
@BigBen I just call it as a function inside a sub when needed. "ShowHideSheets(ARRAYDESIRED)" — iamstrained
– iamstrained, Commented Oct 10, 2019 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
– BigBen, Commented Oct 10, 2019 at 18:06

ARickman · Accepted Answer · 2019-10-10 19:29:29Z

2

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

edited Oct 10, 2019 at 19:29

answered Oct 10, 2019 at 18:04

ARickman

6011 gold badge7 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

BigBen Over a year ago

My guess is that OP actually doesn't need ParamArray here at all.

ARickman Over a year ago

@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.

BigBen Over a year ago

No, it is a variant array. Debug.Print TypName(VisibleToggle(i)) - returns Variant(). You're calling CVar on something that's already a Variant.

ARickman Over a year ago

@BigBen you mean it is still a jagged array. Yea I just realized, lol, See my edit.

BigBen Over a year ago

Completely agree that this should be a Sub.

|

Collectives™ on Stack Overflow

Passing Array to Function Requiring Transformation in VBA

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related