[RESOLVED] Dynamic arrays

[BrianPaul]

I have an array that I'm not sure of what size it will be. How should I go about declaring it? I know I can declare it as dynamic and then ReDim it, but I won't know what size to make it until data is retrieved and added to the array. Should I just declare it's size to be much larger that it will be? If so, how can I tell the size of the array (meaning what part of the array actually has data)?

[LaVolpe]

Generally, in cases like this, one declares it as dynamic as you have mentioned.

Remember that you can resize the array and keep the contents by using the keyword Preserve...

Dim myArray() As Long
....
  When data is received, increase the array enough to include the new data
ReDim Preserve myArray(0 To [some new size])
   When more data is received, you should know how much is being received
ReDim Preserve myArray(0 To [some new size])
    etc, etc

Now there are more efficient ways of doing this. But maybe a bit more detail might help. This should get you started.

Oversizing the array is a good technique most of the time. To determine how big it is, use UBound(myArray). When all the data has been received and you need to have the array exactly that size, the use ReDim Preserve one last time to resize it to fit.

P.S. There is a topic on arrays in the FAQs section