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
This question is tagged with
~ Asked on 2012-01-13 13:01:10
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
~ Answered on 2012-01-13 13:08:41
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
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
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.
~ Answered on 2013-08-21 18:23:44