How to create multi-dimensional array of unknown size?

Question

I've got a simple problem:

I've got a set of data, which I'm sifting through and adding into an array upon criteria match
Issue is, I don't know how many matches there might be, so I need the array to be of unspecified size.
The second index of the array is static.

In an (pseudo-language) example:

if <matched criteria> = True {
    i = i + 1
    array( i,  1 ) => "John Doe" ' name
    array( i,  2 ) => "New York" ' location
    array( i,  3 ) => "02. 08. 1992" ' birthdate
}

Issue is, in vba you have to kind of pre-declare the arrays (especially with Option Explicit enabled). My thought process was to declare an array, that would start with first index at 0 and I would gradually ReDim it upon need.

Here is an simplified example of my code:

Dim cell as Range
Dim arr(0, 1 to 3) as String
Dim i As Integer: i = 0

For each cell in Range("A1:A100")
  If criteria_match(cell) = True Then
      arr(i, 1) = Cells(cell.row, 4)
      arr(i, 2) = Cells(cell.row, 5)
      arr(i, 3) = Year(Cells(cell.row, 6))
      i = i + 1
      ReDim Preserve arr(i, 1 to 3)
  End If
Next cell

Issue is, this throws an exception:

Is there perhaps any way, I could steadily increase the size of the first array index depending on the need?

I would suggest using nested 1d arrays structure, which can be converted to 2d array if necessary with [`Denestify()`](https://stackoverflow.com/a/45892922/2165759) — omegastripes, Mar 26 '19 at 18:36

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

4

Don't size the array in the variable declaration statement.

Change:

Dim arr(0, 1 to 3) as String

to:

Dim arr() as String
ReDim arr(1 to 3, i)

Redimension it as necessary.

Edit: For more information, see this link: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/array-already-dimensioned

To briefly summarize, when you size the array in the declaration statement, it creates a static array (which can't be resized). When you don't declare a size, then it becomes a dynamic array, which can be resized.

An important note to make: ReDim Preserve can only be applied on the last dimension of the array

eg. ReDim Preserve arr(1 to 3, i) will work.
Meanwhile, ReDim Preserve arr (i, 1 to 3) will not.

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 26 '19 at 18:12

basodre

5,720
1
15
23

Ah yeah. Funnily enough, I actually went through the documentation and even found this link already, but I presumed `Dim arr() As String` only stands as dynamic definition for a single-dimensional array. I tried `arr( , ) As String` but that resulted in error. Either way, got it now, thanks – Samuel Hulla Mar 26 '19 at 18:33
4

Note that you can only Redim Preserve the last dimension of an array: so you would have to transpose your array so that i is the last dimension – Charles Williams Mar 26 '19 at 18:34
Well, yeah, I have simply shifted the array other way around based on the comment and it works, but perhaps it would be curious to investigate, if it somehow were possible to somehow dimension the array, so that the first index is dynamic and second static – Samuel Hulla Mar 26 '19 at 18:56
I guess, easiest way would be to indeed simply `Transpose` it – Samuel Hulla Mar 26 '19 at 19:03
1

@Rawrplus `WorksheetFunction.Transpose` works until array length 2^16, then only transpose via loops will works, which is much slower. – omegastripes Mar 26 '19 at 19:20

freeflow · Answer 2 · 2019-03-26T19:25:45.027

Use a Type for the data and a collection for the variables based on the type.

Class Person

Public Name As String
Public Location As String
Public DoB As Date

In a Module

Sub Test()

    Dim this_person As Person
    Dim Persons As Collection
    Dim my_cell                 As Excel.Range
    Set Persons = New Collection

    For Each my_cell In Range("A1:A100")

      If Criteria_Match(my_cell) Then

        Set this_person = New Person
        With this_person

            .Name = ActiveWorkbook.ActiveWorksheet.Cells(my_cell.Row, 4).Value2
            .Location = ActiveWorkbook.ActiveWorksheet.Cells(my_cell.Row, 5).Value2
            .DoB = Year(ActiveWorkbook.ActiveWorksheet.Cells(my_cell.Row, 6).Value2)

        End With

        Persons.Add this_person

      End If

    Next

End Sub

How to create multi-dimensional array of unknown size?

Is there perhaps any way, I could steadily increase the size of the first array index depending on the need?

2 Answers2

Linked