Search code examples
arraysvbaexcelbasic

Excel VBA array not populating from loop

Here is the code. What I am trying to do is to create an array containing all index positions of a string (named "difficultstring") where some spaces exist (the string looks like " text text text ..."):

Dim cutarray
cutarray = Array(0)

For spacething = 2 To Len(difficultstring)
    If Mid(difficultstring, spacething, 1) = " " And Mid(difficultstring, spacething - 1, 1) <> " " Then
    ReDim cutarray(UBound(cutarray) + 1)
    cutarray(UBound(cutarray)) = spacething
End If
Next spacething

But for some reason, when I try to use the cutarray array to do something later, or to display values from cutarray, it looks like there isn't any numbers in it. I checked len(cutstring) and it returned value of 43 - this means Excel tried to populate this string but no numbers were actually populated (when I pulled cutstring(0), cutstring(1), etc. nothing shows up.

Can anyone help? Thanks a lot!!!!!!!

Solution

  • You need the "Preserve" command on your ReDim statement, like @Gary's Student referenced. Try:

    ReDim Preserve cutarray(UBound(cutarray) + 1)