How do I append an initially empty string array in VBA?

Question

I am trying to add a variable number of items to a string array.

My code is in a worksheet change function:

            Dim StartNums(0 To 2) As String

            doneColor = RGB(175, 175, 175)

            cmt = FRg.Comment.Text


            rowLen = InStr(1, cmt, vbLf)
            If rowLen = 0 Then
                rowLen = Len(cmt)
            End If
            numChunks = rowLen / 32
            numRows = Len(cmt) / rowLen
'            For i = 1 To 12 'FRg.Comment.Shape.TextFrame.Characters.Count
'                With FRg.Comment.Shape.TextFrame.Characters(i, 1)
'                    If .Font.Strikethrough = True Then
'                        .Font.Color = vbGreen
'                    End If
'                End With
'            Next i
            MsgBox ("About to fill StartNums, nothing should be in it yet")
            For j = 0 To numChunks - 1
                MsgBox ("going to add stuff for chunk " & j)
                If Not UBound(StartNums) = 2 Then
'                    MsgBox ("resizing an empty array")
'                    ReDim Preserve StartNums(3) As Variant
'                Else
                    'MsgBox ("resizing a non-empty array")
                    ReDim Preserve StartNums(UBound(StartNums) + 3) As String
                End If

                StartNums(UBound(StartNums) - 2) = (j * 32) + 5 + (0 * 9)
                StartNums(UBound(StartNums) - 1) = (j * 32) + 5 + (1 * 9)
                StartNums(UBound(StartNums) - 0) = (j * 32) + 5 + (2 * 9)
            Next j

Now whenever I go into the worksheet, the ReDim line has a Compile Error Array already dimensioned. I'm aware it's already dimensioned, which is why I'm ReDim-ing it.

How do I add 3 more spaces in an array?

if you know there will be 3 more spaces, why not just declare the correct size? you can have empty values in an array... — alowflyingpig
– alowflyingpig, Commented Jan 14, 2020 at 0:02

braX · Accepted Answer · 2020-01-16 17:50:26Z

4

When you initially Dim it, you can't give it the size if you plan on changing the size later:

so instead of

Dim StartNums(0 To 2) As String

You would use

Dim StartNums() As String

and then on the next line

ReDim StartNums(0 To 2)

note, you can also use this, as it's assumed to be 0 based by default.

ReDim StartNums(2)

That will accomplish the same thing, but then you can later use Redim Preserve to change the size of it.

edited Jan 16, 2020 at 17:50

answered Jan 13, 2020 at 23:41

braX

11.9k5 gold badges24 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

How do I append an initially empty string array in VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related