VBA how to use a dynamic array in a loop

Question

I'm parsing a tab delimited txt file. Each line in the file contains 3 fields: 1. nCells - which is constant and must be the same for each line 2. product index 3. counter (how many products were purchased)

I want to create an array that contains the product counter for each product index. The problem is that I fill this array while parsing the file, and I don't know the "nCells" property before entering the parsing loop.

Should I define an array of size 1 outside the parsing loop, and then, after parsing nCells, do something like:

If i = 1 Then ReDim array(1 to nCells)

Or is there a better way?

For example: For the following input file:

3    1    20
3    1    30
3    2    10
3    3    15

I want to create an array with 3 cells which contains 50 in cell#1 (20+30), 10 in cell#2 and 15 in cell#3. The problem is that I don't know in advance that I have 3 products and that my array should contain 3 cells. I only discover this while parsing the first row of the file. so I cannot declare a static array outside the loop, I have to declare a dynamic array inside the loop.

Thanks, Li

I am not sure I understand. Can you show us a sample data and what you are trying to achieve? — Siddharth Rout, Oct 21 '13 at 12:33
Sorry, I've added an example to the question, hope it helps. — user429400, Oct 21 '13 at 12:44
Oh Ok. The best way would be to read the Text File in an Array in one go and then read each line from that array into another array using REDIM PRESERVE. WOuld you like to give it a try? — Siddharth Rout, Oct 21 '13 at 12:47
thanks :) I'm not sure that I understand... why do I need to read each line into the first array? I just need to acumulate the product counters for each product, I don't need to store the rest of the data... — user429400, Oct 21 '13 at 13:29

score 0 · Answer 1 · answered Nov 09 '13 at 23:30

I would use a collection as this will just expand to the required size.

Sub a() Dim myColl As Collection Dim i As Integer Set myColl = New Collection Dim v As Variant

For i = 1 To 3
    myColl.Add Array(i, Now)
Next
For Each v In myColl
    Debug.Print v(0), v(1)
Next
Set myColl = Nothing

End Sub

score 0 · Answer 2 · answered Nov 10 '13 at 05:12

You can use a Dictionary to create a histogram of parts

Public Sub Histogram()
    Dim d As New Dictionary, parts() As String
    Dim ts As TextStream, line As String
    Set ts = fso.OpenTextFile("BookData.txt", ForReading, False)
    While Not ts.AtEndOfStream
        line = ts.ReadLine
        parts = Split(line, vbTab) ' 0-index string array 
        ' 2nd column contains the unique product string.
        ' Use product as key for dictionary and quantity for value
        If d.Exists(parts(1)) Then
            d(parts(1)) = d(parts(1)) + CInt(parts(2))
        Else
            d.Add parts(1), CInt(parts(2))
        End If
    Wend
    ts.Close
    Dim i As Integer
    For i = 1 To d.Count
        Debug.Print d.Keys(i - 1), d(d.Keys(i - 1))
    Next i
    ' Produces the following output with your example data
    ' 1    50
    ' 2    10
    ' 3    15
End Sub

This ignores the first column parts(0) of each row.

VBA how to use a dynamic array in a loop

2 Answers2