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

Question

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?

The values you are adding to allArrays are not of the Range type. — Phylogenesis
– Phylogenesis, Commented Aug 25, 2017 at 14:03
a, b and c are not of type range, but of type Array of range. allArrays expects Range variables, not arrays. — Vincent G
– Vincent G, Commented Aug 25, 2017 at 14:03

John Coleman · Accepted Answer · 2017-08-25 14:05:24Z

2

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.

answered Aug 25, 2017 at 14:05

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

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

7 Comments

Ans Over a year ago

How would I access the elements -ranges? I tried allArrays(1)(1) and allArrays(1, 1). I pass it to a function that expects Range. The former gives a ByRef error (passed type wasn't Range), the later wrong number of dimensions error.

John Coleman Over a year ago

@Ans you would use allArrays(1)(1). As far as the ByRef problem goes, you could just change the function so that it expects a Variant (this flexible type has a way of clearing up ByRef problems). If worse comes to worse, you could declare a range variable R, and then Set R = allArrays(1)(1) and pass R to the function.

Ans Over a year ago

Why does this mistake happens though? Doesn't it mean that something is wrong - otherwise the Range type would have been passed? And what is being passed if it's not Range?

John Coleman Over a year ago

What is passed is a Range subtype of a Variant type. It seems like a bug in the VBA language that it isn't good enough. If all of your range arrays (a,b,c) have the same length, you might want to load them into a 2-dimensional range array, but that would require loops to load rather than simple assignment statements.

John Coleman Over a year ago

@Ans If it works, do it. I don't know of any dangers, I use collections all the time (although I don't usually don't wrap them in a type).

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related