MS-Access / Getting Started

Dynamic Arrays

Sometimes you do not know how large an array needs to be. A good example is if you are recursively storing pathnames from a disk device in each array element. You do not know how many subdirectories there are and how long a branch will extend. You could set your array to 1,000 elements, taking up valuable memory in the process, and then find that you only needed 500 elements. Alternatively, there could be 2,000 subdirectory pathnames, so you would run out of room.

You create a dynamic array in exactly the same way as a normal array-using the Dim statement at the global, module, or local level, or using Static at the local level. You give it an empty dimension list:

Dim temp()

You then use the ReDim statement within your procedure to resize the number of elements within the array. The ReDim statement can only be used within a procedure, and you cannot change the number of dimensions:

ReDim temp(100)

You could write code to check the number of values collected and then resize the array if it is getting close to the upper boundary. Two functions are helpful when working with arrays-LBound and UBound. These functions can be used to return the lower and upper limits of the dimensions of an array by specifying the array number as a subscript:

Dim temp(10)

MsgBox LBound(MyTemp)

MsgBox UBound(MyTemp)

LBound will return the value of 0; UBound will return the value of 10.
ReDim will automatically clear all values in the array unless you use the Preserve keyword:

ReDim Preserve temp(100)

Data already held in temp will now be preserved.

[Previous] [Contents] [Next]