how to redim preserve jagged array (arrays in array) in VBA?

Question

I have an array of arrays (not a 2D array) in EXCEL VBA and I need to add elements to the inner arrays. e.g.:

Option Explicit

Sub ArrayofArrays()

Dim OuterArray() As Variant
ReDim OuterArray(0 To 0)

Dim InnerArray() As Variant
ReDim InnerArray(0 To 0)

InnerArray(0) = "Foo"
OuterArray(0) = InnerArray

ReDim Preserve OuterArray(LBound(OuterArray) To UBound(OuterArray) + 1)
End Sub

I could now access the inner element by:

debug.print OuterArray(0)(0)

which prints me "Foo"

But how can I extend the array inside OuterArray?

The last line only adds an empty element to the OuterArray:

ReDim Preserve OuterArray(LBound(OuterArray) To UBound(OuterArray) + 1)

But what I want is this:

¦___OuterArray(0)

¦_____________OuterArray(0)(0): "Foo"

¦_____________OuterArray(0)(1): "Bar"

Thanks!

You need to redim the inner array, give him the value and after that insert the inner in the outer. Make sense? — skulden
– skulden, Commented Jan 18, 2019 at 12:38
Your answer is the same like Tom's answer. Thanks I made it work with the temp array copying back to then to the outerArray — cekar
– cekar, Commented Jan 18, 2019 at 13:21

Tom · Accepted Answer · 2019-01-18 12:43:59Z

5

VBA won't allow you to directly ReDim the inner array however, you can achieve it quite easily by using an intermediary helper array (in this example named tmp)

Option Explicit
Sub ArrayofArrays()
    Dim tmp As Variant
    Dim OuterArray() As Variant
    ReDim OuterArray(0 To 0)

    Dim InnerArray() As Variant
    ReDim InnerArray(0 To 0)

    InnerArray(0) = "Foo"
    OuterArray(0) = InnerArray

    tmp = OuterArray(0)
    ReDim Preserve tmp(LBound(tmp) To UBound(tmp) + 1)
    OuterArray(0) = tmp
    Erase tmp

    OuterArray(0)(1) = "Bar"
    Debug.Print OuterArray(0)(1)
End Sub

edited Jan 18, 2019 at 12:43

answered Jan 18, 2019 at 12:37

Tom

9,9184 gold badges33 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

how to redim preserve jagged array (arrays in array) in VBA?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related