VBA - Insert data in array without dimensioning it

Question

Sometimes I find myself in front of the problem of filling an array without having to dimension it first. Most of the times, I can verify - with a loop - how many times a condition has been verified, ReDim my array accordingly and then with that same very loop fill the array with data.
This is (especially with humongous arrays) very time consuming and not optimized.

Today I found myself programmatically "reading" a text file, in which I had to verify how many times a line was repeated before an empty one, without having any clue on how many there could be. I know only that the minimum number of such lines is 1.

The file looks like this:

TITLE Very_Much_Potato
INFO lol?
FREQUENCY 123456.7
DEF_DIAG  1 Potato1.TXT
DEF_DIAG  2 Potato2.TXT

ELEMENT  1   0.00    0.00    -300.00     0.250   0.0     6.0     1   2   0.00    0.0     0.0    
ELEMENT  2   0.00    0.00    -200.00     0.500   20.0    6.0     1   2   0.00    0.0     0.0    
ELEMENT  3   0.00    0.00    -100.00     0.750   40.0    6.0     1   2   0.00    0.0     0.0    
ELEMENT  4   0.00    0.00    0.00    1.000   60.0    6.0     1   2   0.00    0.0     0.0    
ELEMENT  5   0.00    0.00    100.00  0.750   80.0    6.0     1   2   0.00    0.0     0.0    
ELEMENT  6   0.00    0.00    200.00  0.500   100.0   6.0     1   2   0.00    0.0     0.0    
ELEMENT  7   0.00    0.00    300.00  0.250   120.0   6.0     1   2   0.00    0.0     0.0    

END

I use the following code to "read it":

Dim i As Integer
Dim fd As FileDialog, FilePath As String
Dim fst As Object, StringData As String
Dim Name As String, Frequency As Double, DefDiag() As String

Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .Title = "Select the file you would like to import"
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Potato File", "*.pot"
    .FilterIndex = 1
    If .Show <> -1 Then
        End
    End If
    FilePath = .SelectedItems(1)
End With

Set fst = CreateObject("ADODB.Stream")
fst.Charset = "utf-8"
fst.lineseparator = 10      ' sets enter as end-of-line separator
fst.Open
fst.LoadFromFile FilePath

StringData = fst.ReadText(-2)  ' -2 to read until lineseparator
Name = Mid(StringData, 7)
StringData = fst.ReadText(-2)
StringData = fst.ReadText(-2)
Frequency = CDbl(Mid(StringData, 11))
Do Until fst.ReadText(-2) = ""
    StringData = fst.ReadText(-2)  ' DEF_DIAG
    DefDiag(i) = Right(StringData, Len(StringData) - InStrRev(StringData, " "))
    i = i + 1
Loop

Naturally it stops where the DefDiag array is beginning to get filled. Any Idea on how not to dimension it?

Answer 1

I don't think that it's possible to fill an array without dimension in VBA, but you can use ReDim Preserve to redim the array while looping:

...

i=1

Do Until fst.ReadText(-2) = ""
    StringData = fst.ReadText(-2)  ' DEF_DIAG
    ReDim Preserve DefDiag(i)
    DefDiag(i) = Right(StringData, Len(StringData) - InStrRev(StringData, " "))
    i = i + 1
Loop