Access VBA Macro Programming

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 adynamicarray in exactly the same way as a normal array—using theDim

statement at the global, module, or local level, or usingStaticat the local level. You give it an

empty dimension list:

Dim temp()

You then use theReDimstatement within your procedure to resize the number of

elements within the array. TheReDimstatement 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—LBoundandUBound. 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)

LBoundwill return the value of 0;UBoundwill return the value of 10.

ReDimwill automatically clear all values in the array unless you use thePreserve

keyword:

ReDim Preserve temp( 100 )

Data already held intempwill now be preserved.

User-Defined Types

You can also define your own type of variable by employing existing variable types using the

Typekeyword. This must be entered in the declarations section of a module:

Type Employee

Name as String

Salary as Currency

24 Microsoft Access 2010 VBA Macro Programming