CODEWITHSUNDEEP
vbaexcelarguments
Kaz
Kaz

Reputation: 1332

Why can't I pass an array(within an array) as an argument?

I have an array of arrays arrAggregatedArrays(1 to 8)

I can call a sub like this:

call sub(ArrNewClient)

But I get a compile error: "Type Mismatch" if I try this:

call sub(arrAggregatedArrays(1))

Why? And is there a way around it?

and why does it not recognise arrAggregatedArrays(1) as an array even though it will perform functions like UBound on it like normal?

Public arrAggregatedArrays()     As Variant      '/ Holds all the sheet-Data Arrays

'/ Declared in a seperate module

      ReDim arrAggregatedArrays(1 To 8)
            arrAggregatedArrays(1) = arrNewClient
            arrAggregatedArrays(2) = arrExistingClient
            arrAggregatedArrays(3) = arrGroupSchemes
            arrAggregatedArrays(4) = arrOther
            arrAggregatedArrays(5) = arrMcOngoing
            arrAggregatedArrays(6) = arrJhOngoing
            arrAggregatedArrays(7) = arrAegonQuilterArc
            arrAggregatedArrays(8) = arrAscentric

      Call FilterSheetArrayForColumns(arrAggregatedArrays(1))

Public Sub FilterSheetArrayForColumns(ByRef arrCurrentArray() As Variant)

and a screenshot:

Compile Error

Upvotes: 7

Views: 294

Answers (2)

Bond
Bond

Reputation: 16321

You can create a Variant array in one of two ways:

Dim v1() As Variant
Dim v2: v2 = Array()

With the former, you receive the array as a subroutine parameter using the v1() notation, like with any other data type array in VBA. With the latter, you'll need to receive it as a normal variable, without the array notation.

Variants are special because they can hold many types, including array types, which is why the v = Array() syntax works. When done this way, they should be treated like any other variable and passed that way in and out of subroutines.

Upvotes: 6

user4039065
user4039065

Reputation:

As mentioned in comments, you need to show more on the implementation you are using. This works for me.

Sub arr_test()
    Dim arr As Variant

    arr = Array(Array(1, 2, 3), Array(2, 3, 4), _
                Array(3, 4, 5), Array(4, 5, 6))

    Debug.Print LBound(arr, 1) & ":" & UBound(arr, 1)
    Debug.Print LBound(arr(1), 1) & ":" & UBound(arr(1), 1)

    Call arr_sub(arr)
    Call arr_sub(arr(1))

End Sub

Sub arr_sub(tarr As Variant)
    Debug.Print "arr_sub - "; LBound(tarr, 1) & ":" & UBound(tarr, 1)
End Sub

Results from the Immediate window:

arr_test
0:3
0:2
arr_sub - 0:3
arr_sub - 0:2

Upvotes: 1

Related Questions