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

Question

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

You need to show us your declarations and how this variable is initialized. — RBarryYoung
– RBarryYoung, Commented Aug 23, 2015 at 0:18
@Zak - Now we just need to see your function declaration for FilterSheetArrayForColumns(). My guess is you're receiving it as a() As Variant instead of a As Variant. Variants are special. The variable can be used by itself to represent an array. — Bond
– Bond, Commented Aug 23, 2015 at 0:56
so what's the difference between passing an array as array and as array() ? — Kaz
– Kaz, Commented Aug 23, 2015 at 1:00
@Zak - I've added an answer to try to explain the difference. — Bond
– Bond, Commented Aug 23, 2015 at 1:10

Bond · Accepted Answer · 2015-08-23 02:29:54Z

6

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.

edited Aug 23, 2015 at 2:29

answered Aug 23, 2015 at 1:10

Bond

16.3k6 gold badges34 silver badges56 bronze badges

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

14 Comments

Kaz Over a year ago

Thanks. Can you explain why/how array and array() are different from one another, in terms of how the program perceives / operates on them?

Kaz Over a year ago

I'd like to understand the underlying concepts involved.

Kaz Over a year ago

and if I can't pass array to a function expecting array() does that also mean I can't pass array() to a function expecting array ?

Bond Over a year ago

There isn't much of a difference between the two, other than how they're passed. A Variant variable can be used to store any type of array. For example, you could Dim s(0) As String and then assign it to a Variant like so: Dim v As Variant: v = s. So it may help conceptually to think of it as something generic that can hold more than just an intrinsic value. I'm sure you've seen examples of objects being assigned to variants as well.

Bond Over a year ago

There is a minor difference between: Dim v1() As Variant and Dim v2 As Variant: v2 = Array(). The latter can have its bounds safely tested (LBound = 0, UBound = -1) which can be beneficial if you need to dynamically resize it based on its current size: ReDim v2(UBound(v2) + 1), for example. This is not possible using the former method.

|

user4039065 · Accepted Answer · 2015-08-23 00:26:12Z

1

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

answered Aug 23, 2015 at 0:26

user4039065

1 Comment

Kaz Over a year ago

Apologies, details added.

Collectives™ on Stack Overflow

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

2 Answers 2

14 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related