Populating VBA dynamic arrays

Question

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

User · Answer

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

User · Answer

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

User · Answer

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   Creating an array big enough to handle anything you think will be thrown at it 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    amp  lngCnt     Next End Sub

User · Answer

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   lt    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

User · Answer

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

[arrays] Populating VBA dynamic arrays

Examples related to arrays

Examples related to vba