CODEWITHSUNDEEP
arraysvbaexcel
Ans
Ans

Reputation: 1234

Excel vba: Store Arrays in array, nested arrays, jagged array

I have 3 arrays, each contains 3 ranges. I want to store those arrays in one array. I try to do the following:

 Function test()        
            Dim a() As Range
            Dim b() As Range
            Dim c() As Range
            a = getA(dataWorkbook)  'a(1)=Range(...), a(2)=Range(...), a(3)=Range(...)
            b = getB(dataWorkbook)
            c = getC(dataWorkbook)

            Dim allArrays(1 To 3) As Range
           ' allArrays(1) = a
           ' allArrays(2) = b
           ' allArrays(3) = c

            test="HELLO"
End Function

However if I uncomment lines with allArrays assignment, the function returns !VALUE instead of "HELLO". What I'm doing wrong?

Upvotes: 1

Views: 4614

Answers (1)

John Coleman
John Coleman

Reputation: 51998

The best way to create a jagged array is by using a Variant:

Dim allArrays As Variant
ReDim allArrays(1 To 3)
'rest of code will work as intended

You can simplify your code and just use the Array function:

Dim allArrays As Variant
allArrays = Array(a, b, c)

although in this case allArrays will be 0-based rather than 1-based.

Upvotes: 2

Related Questions