ReDim existing array with a second dimension?

Question

I declared an array in my VBA function that has a dynamic size. As I cannot ReDim the first dimension of a two- or more-dimensional array, can I add a second dimension to a set array?

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)

doesn't work.

Is there a workaround?

Answer 1

There isn't any built-in way to do this. Just create a new two-dimensional array and transfer the contents of your existing one-dimensional array into the first row of that 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

Example usage:

nameArray = AddDimension(nameArray, 1, 5)

Answer 2

There is one (also works to delete a dimension), but you will have to think in terms of worksheet dimensions...

use transposition

While I highly prefer the previous 'by hand' method from Jean-François Corbett's and I don't like to rely on Excel build-in function (especially this one!), I would just like to clarify another way for future readers coming here:

---

adding a dimension to a 1d line vector (a row) means transposing it in Excel

---

Here, nameArray(1 to arrayCount) is a row (index is a column number) and because of it, if you add a dimension it will become a column since 2d arrays are indexed as (row,column). So, you can just do this:

nameArray = Application.Worksheetfunction.Transpose(nameArray) 'transforms the array to nameArray(1 To arrayCount, 1 To 1), so then:
redim preserve nameArray(1 To arrayCount, 1 To 5)

without any other manipulation.

BUT beware of the very confusing Excel's Transpose function (at least it is the case for me!): the advantage here is that it automatically adds a dimension and redimensions the array for you.

---

It works as expected only because you are using a 'based 1' index '1d array'.

---

IF this is not the case, all indices will be shifted by 1, (that's how Transpose is build in to be coherent with cells and ranges). That is: if you start with

nameArray(0 to arrayCount)

you will end up with

nameArray(1 to arrayCount + 1, 1 to 5)

with precautions

While I am at it, it may be off-topic and their are many topics about it, but their are other traps in this Transpose function one should never be enough warned about (not to mention it can consume more time and resources than Jean-François Corbett's solution) :

• if you are dealing with '1d column' in an excel 2d array (a column), that is an array:

nameArray(1 to arrayCount, 1 to 1) [*]

and if you make the transpose of it, the column dimension will be "skipped" and the result will be:

nameArray(1 to arrayCount)

It makes sense since you will end up with an Excel row (so why bother with an extra dim?). But I have to say this is not the intuitive behaviour I would expect, which should be more something like nameArray(1 to 1, 1 to arrayCount).

---

Note that, a contrario, it can be used to delete a dimension and redimension the array automatically from 2d to 1d: redim preserve the last dimension to 1 to 1 and then transpose! This is the resulting array just above.

---

• But finally all is not lost: suppose you transpose this array of 1 line:

nameArray(0 to 0, 0 to arrayCount)

you correctly get an array of 1 column:

nameArray(1 to arrayCount + 1, 1 to 1)

(well, almost) - and look back at ref [*] now...

---

Thus, if you are to use this build-in function and if, moreover you need more dimensions or worst, need to compose transpositions, it can become a bit tricky...

For all this or if you simply need to know correct indexing and number of dimensions of your arrays (not only number of columns), I would suggest a very useful function from users John Coleman and Vegard, the post just below.

While all this should appear logical and trivial for people used to work with Excel sheets, it's very not the case when you are more used to matrix manipulations and I think suggesting the use of this Transpose function should come with some precisions.