ReDim existing array with second dimension?

Question

I declared an array in my VBA function with dynamic size. Since I cannot ReDim define the first dimension of a two-dimensional or more-dimensional array, can I add a second dimension to the array of arrays?

This is how I dynamically set the size of my array.

Dim nameArray() As String
Dim arrayCount As Long

For i = 1 To 100
    ReDim Preserve nameArray(1 to arrayCount)
    nameArray(arrayCount) = "Hello World"
    arrayCount = arrayCount + 1
Next i

Now I would like to add a second dimension.

ReDim Preserve nameArray(1 To arrayCount, 1 To 5)

does not work.

Is there any workaround?

Answer 1

There is no built-in way to do this. Just create a new two-dimensional array and transfer the contents of your existing one-dimensional array to the first line of this new array.

This is what this function does:

Function AddDimension(arr() As String, newLBound As Long, NewUBound As Long) As String()
    Dim i As Long
    Dim arrOut() As String
    ReDim arrOut(LBound(arr) To UBound(arr), newLBound To NewUBound)
    For i = LBound(arr) To UBound(arr)
        arrOut(i, newLBound) = arr(i)
    Next i
    AddDimension = arrOut
End Function

Usage example:

nameArray = AddDimension(nameArray, 1, 5)