How do I Create 'ragged' arrays in Excel VBA?

[David Empey]

The following code seems to work, but is it safe?

Sub RaggedArray

Dim A() as Variant, B() as Long, i as Long, j as Long

ReDim A(1 to 10)
For i = 1 to 10
ReDim B(1 to I)
A(i) = B
For j = 1 to i
A(i)(j) = 10 * i + j
Next j
Next i

End Sub

This seems to create an array A whose elements are arrays
of varying lengths, which is what I want, but can I be
sure the elements of A won't be overwritten by some other
piece of code that needs to use memory? Does Visual
Basic know the elements of A exist?

Am I even asking a sensible question?

 

[John Coleman]

Interesting example. I think that what happens is the following: VBA's
garbage collection is based on reference counting. When you run the
statement A(i) = B you are establishing a reference to the current
array B - a reference which won't be removed until *A(i)* is
reassigned (or destroyed). When ReDim B(1 to i) is run a brand new
array is allocated for B. This usually results in the old array being
garbage collected - but here you still have a reference keeping it
alive. Your code seems to work but it is not clear to me that you are
guaranteed that a ReDim B always allocates a separate memory block for
the new array and never overwrites the old B (unless it is ripe for
garbage collection) . In other words - I'm not sure if you have found
a subtle use of a documented behavior or a convienent use of an
undocumented. I suspect the former, but am not 100% sure. Maybe
someone with more knowledge of VBA's memory management will chip in.

-John Coleman

 

[John Coleman]

One further thought - it is possible to both make your intentions
clearer and lay to rest some lingering concerns by doing something
like:

Sub RaggedArray()


Dim A() As Variant, i As Long, j As Long


ReDim A(1 To 10)
For i = 1 To 10
A(i) = MakeArray(1, i)
For j = 1 To i
A(i)(j) = 10 * i + j
Next j
Next i

End Sub

Function MakeArray(lower As Long, upper As Long) As Variant
Dim A As Variant
ReDim A(lower To upper)
MakeArray = A
End Function

The cost would be a little less memory efficiency since MakeArray
returns a variant array. If it matters, you could do something like:

Sub RaggedArray()


Dim A() As Variant, i As Long, j As Long


ReDim A(1 To 10)
For i = 1 To 10
A(i) = MakeLongArray(1, i)
For j = 1 To i
A(i)(j) = 10 * i + j
Next j
Next i

End Sub

Function MakeLongArray(lower As Long, upper As Long) As Variant
Dim A() As Long
ReDim A(lower To upper)
MakeLongArray = A
End Function

Which seems to be functionally equivalent to your original code.

Hth

-John Coleman

 

[RB Smissaert]

I don't think there is anything wrong with this code and as far as I know
this is just
completely standard and will behave fine. No need to worry about memory
management.
If it compiles and behaves as expected in a few tests then that is it.

If you want you could make a collection of arrays, which may have some
advantage.

RBS

 

[David Empey]

In other words - I'm not sure if you have found
a subtle use of a documented behavior or a convienent use of an
undocumented. I suspect the former, but am not 100% sure. Maybe
someone with more knowledge of VBA's memory management will chip in.

That's exactly my question. So far I haven't run into any trouble
(that I know of) doing it this way, but I'd like to be sure.
Thanks for your suggestions.

 

[Tom Ogilvy]

I disagree with John's description of references in this instance.

When you run the

statement A(i) = B you are establishing a reference to the current
array B - a reference which won't be removed until *A(i)* is
reassigned (or destroyed).

I don't believe this is correct. This should convince you that A(i) is
different from B. In otherwords, the array B is copied to A(i), not
referenced: (since B has not been reassigned, if it were only referenced,
A(i)(j) = B(j). But it doesn't. )

Sub RaggedArray()

Dim A() As Variant, B() As Long, i As Long, j As Long
Dim l As Long
ReDim A(1 To 10)
For i = 1 To 10
ReDim B(1 To i)
For j = 1 To i
B(j) = Int(Rnd() * 100 + 1)
Next
A(i) = B
For j = 1 To i
A(i)(j) = 10 * i + j
Next j
If i = 5 Then
For j = 1 To i
Debug.Print i, j, A(i)(j), B(j)
Next
End If

Next i

End Sub

So you should have no concerns using this approach.

 

[Peter T]

As others have said that's quite normal, known as an 'Array of Arrays'. Very
useful when needed, not particularly efficient if not. The array holder must
be a Variant as in your example.

Try this with your example -

'code
Next i
Dim C as variant
C = A
A(1) = C
Stop ' Alt-v,s look at A(1) in locals
End Sub

Regards,
Peter T

 

[John Coleman]

You are correct. An even simpler example to show that Array
assignments don't create an alias would be something like

Dim A(), B() as long
Redim A(1 to 10)
Redim B(1 to 10)
A(1) = B
B(1) = 5
msgbox A(1)(1) 'won't return 5

my speculation was based on trying to figure out why VBA seems to
require B here (perhaps hidden in a function call as in my second
post) instead of a simple

Dim A(1 to 10) as variant
For i = 1 to 10
ReDim A(i)(1 to i) 'ReDim (A(i))(1 to i) isn't any better
Next i

(which is a syntax error).

If A(i) is a Variant and ReDim is a valid statement for Variants, then
why not for A(i)? My first guess was that B was playing some essential
role as the target of a reference, but it looks more like it is just
plugging a gap in VBA's syntax (although if you want the sub-arrays to
be of type other than variant something like B or a function call
would be required).
Thanks for the correction

-John Coleman

 

[David Empey]

So you should have no concerns using this approach.

Tom, John, RB, Peter: thanks very much for your
responses. I'm glad to know the approach should
be safe.