Solved: Size of Array

[Minglism]

Hi folks,

Just a basic question. I have to store many data in an array. But I don't know how many data there are going to be. Since I have to declare the size of the array before hand, I was just curious if there is any way to store my data in an array without declaring the size.

[CreganTur]

What you're wanting is called a Dynamic array.

Basically with a dynamic array you start by dimensioning the array without specifying its size. Then you use the ReDim keyword to dimension it to a size for use (since you MUST declare a size before you can use the array).

Then use can use the ReDim keyword later on to change the size again. Using the Preserve keyword will keep any values currently in the array. If you ReDim without using Preserve, then you loose anything previously in the array.

Here's an example (place in a Module, put your cursor anywhere inside, then press F5):

Sub DynArray()
Dim counter As Integer
Dim myArray() As Integer '<<<delcare a dynamic array
ReDim myArray(5) '<<<specify initial size of array
Dim myValues As String
'populate myArray with values
For counter = 1 To 5
myArray(counter) = counter + 1
myValues = myValues & myArray(counter) & vbCrLf
Next
'Change the size of my array to hold 10 elements
ReDim Preserve myArray(10)
'add new values to myArray
For counter = 6 To 10
myArray(counter) = counter * counter
myValues = myValues & myArray(counter) & vbCrLf
Next
MsgBox myValues
For counter = 1 To 10
MsgBox myArray(counter)
Next

End Sub

[mdmackillop]

You can also store data as a Variant. This allows you to loop through all values, but you cannot refer to an item by its index. ie Arr(3) will fail.


Dim Arr, a
Arr = Range("A1:A20").Value
For Each a In Arr
Debug.Print a
Next

[Paul_Hossler]

Might add that unless you use Option Base 1 in the module, myArray will really start at 0, and there will be 6 entries.

Use LBound(myArray) and UBound(myArray) to play safe

ALso, only the last dimension of a multi-dim array can be changed.

Paul

[CreganTur]

Paul,

thanks for catching that! I completely forgot that the example I posted used Option Base 1.

I've stopped using Option Base 1, so I don't think about it anymore.

[Bob Phillips]

I've stopped using Option Base 1, so I don't think about it anymore.

You should never rely on it being base 0, you should use LBound and UBound even if you explicitly set Option Base to 0.