Vfrdtyky

The following code gives me error 9 "subscript out of range". I meant to declare a dynamic array so that the dimension changes as I add elements to it. Do I have to create a "spot" on the array before I store something in it like in JS?

Sub test_array()

    Dim test() As Integer

    Dim i As Integer

    For i = 0 To 3

        test(i) = 3 + i

    Next i

End Sub

in your for loop use a Redim on the array like here:

For i = 0 to 3

  ReDim Preserve test(i)

  test(i) = 3 + i

Next i

Yes, you're looking for the ReDim statement, which dynamically allocates the required amount of space in the array.

The following statement

Dim MyArray()

declares an array without dimensions, so the compiler doesn't know how big it is and can't store anything inside of it.

But you can use the ReDim statement to resize the array:

ReDim MyArray(0 To 3)

And if you need to resize the array while preserving its contents, you can use the Preserve keyword along with the ReDim statement:

ReDim Preserve MyArray(0 To 3)

But do note that both ReDim and particularly ReDim Preserve have a heavy performance cost. Try to avoid doing this over and over in a loop if at all possible; your users will thank you.

However, in the simple example shown in your question (if it's not just a throwaway sample), you don't need ReDim at all. Just declare the array with explicit dimensions:

Dim MyArray(0 To 3)

As Cody and Brett mentioned, you could reduce VBA slowdown with sensible use of Redim Preserve. Brett suggested Mod to do this.

You can also use a user defined Type and Sub to do this. Consider my code below:

Public Type dsIntArrayType

   eElems() As Integer

   eSize As Integer

End Type



Public Sub PushBackIntArray( _

    ByRef dsIntArray As dsIntArrayType, _

    ByVal intValue As Integer)



    With dsIntArray

    If UBound(.eElems) < (.eSize + 1) Then

        ReDim Preserve .eElems(.eSize * 2 + 1)

    End If

    .eSize = .eSize + 1

    .eElems(.eSize) = intValue

    End With



End Sub

This calls ReDim Preserve only when the size has doubled. The member variable eSize keeps track of the actual data size of eElems. This approach has helped me improve performance when final array length is not known until run time.

Hope this helps others too.

In addition to Cody's useful comments it is worth noting that at times you won't know how big your array should be. The two options in this situation are

1. Creating an array big enough to handle anything you think will be thrown at it


2. 
   Sensible use of Redim Preserve
   

The code below provides an example of a routine that will dimension myArray in line with the lngSize variable, then add additional elements (equal to the initial array size) by use of a Mod test whenever the upper bound is about to be exceeded

Option Base 1



Sub ArraySample()

    Dim myArray() As String

    Dim lngCnt As Long

    Dim lngSize As Long



    lngSize = 10

    ReDim myArray(1 To lngSize)



    For lngCnt = 1 To lngSize*5

        If lngCnt Mod lngSize = 0 Then ReDim Preserve myArray(1 To UBound(myArray) + lngSize)

        myArray(lngCnt) = "I am record number " & lngCnt

    Next

End Sub

I see many (all) posts above relying on LBound/UBound calls upon yet potentially uninitialized VBA dynamic array, what causes application's inevitable death ...

Erratic code:


Dim x As Long
Dim arr1() As SomeType
...
x = UBound(arr1) 'crashes


Correct code:


Dim x As Long
Dim arr1() As SomeType
...
ReDim Preserve arr1(0 To 0)
...
x = UBound(arr1)


... i.e. any code where Dim arr1() is followed immediatelly by LBound(arr1)/UBound(arr1) calls without ReDim arr1(...) in between, crashes. The roundabout is to employ an On Error Resume Next and check the Err.Number right after the LBound(arr1)/UBound(arr1) call - it should be 0 if the array is initialized, otherwise non-zero. As there is some VBA built-in misbehavior, the further check of array's limits is needed. Detailed explanation may everybody read at Chip Pearson's website (which should be celebrated as a Mankind Treasure Of VBA Wisdom ...)

Heh, that's my first post, believe it is legible.