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() — omegastripes
– omegastripes, Commented Mar 26, 2019 at 18:36

Community · Accepted Answer · 2020-06-20 09:12:55Z

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, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 26, 2019 at 18:12

basodre

5,7901 gold badge18 silver badges25 bronze badges

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

5 Comments

Samuel Hulla Over a year ago

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

Charles Williams Over a year ago

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

Samuel Hulla Over a year ago

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 Over a year ago

I guess, easiest way would be to indeed simply Transpose it

omegastripes Over a year ago

@Rawrplus WorksheetFunction.Transpose works until array length 2^16, then only transpose via loops will works, which is much slower.

freeflow · Accepted Answer · 2019-03-26 19:25:45Z

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

Collectives™ on Stack Overflow

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 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related