Static and Dynamic Arrays

The arrays introduced thus far are static. A static array is an array of a specific size. You use a static array when you know in advance how big the array should be. The size of the static array is specified in the array's declaration statement. For example, the statement Dim Fruits(10) As String, declares a static array called Fruits that is made up of 10 elements.

But what if you're not sure how many elements your array will contain? If your procedure depends on user input, the number of user-supplied elements might vary every time the procedure is executed. How can you ensure that the array you declare is not wasting memory?

You may recall that after you declare an array, VBA sets aside enough memory to accommodate the array. If you declare an array to hold more elements than what you need, you'll end up wasting valuable computer resources. The solution to this problem is making your arrays dynamic. A dynamic array is an array whose size can change. You use a dynamic array when the array size will be determined each time the procedure is run.

Fixed-Dimension Arrays

A static array contains a fixed number of elements. The number of elements in a static array will not change once it has been declared.

A dynamic array is declared by placing empty parentheses after the array name. For example:

Dim Fruits() As String

Before you use a dynamic array in your procedure, you must use the ReDim statement to dynamically set the lower and upper bounds of the array.

The ReDim statement redimensions arrays as the procedure code executes. The ReDim statement informs Visual Basic about the new size of the array. This statement can be used several times in the same procedure. Let's see how your procedure could use a dynamic array.

©Hands-On 7-6: Using a Dynamic Array

1. In a new module, enter the following DynArray procedure.

2. Execute the DynArray procedure (choose Run | Run Sub/UserForm).

Sub DynArray()

Dim counter As Integer Dim myArray() As Integer ReDim myArray(5) Dim myValues As String

' populate myArray with values For counter = 1 To 5

myArray(counter) = counter + 1 myValues = myValues & myArray(counter) & Chr(13)

' declare a dynamic array ' specify the initial size of the array

Part I


' change the size of myArray 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) & Chr(13) Next counter

MsgBox myValues For counter = 1 To 10

MsgBox myArray(counter) Next counter End Sub

In the DynArray procedure, the statement Dim myArray() As Integer declares a dynamic array called myArray. Although this statement declares the array, it does not allocate any memory to the array. The first ReDim statement specifies the initial size of myArray and reserves for it 10 bytes of memory to hold its five elements. As you know, every Integer value requires 2 bytes of memory. The For.. .Next loop populates myArray with data and writes the array's elements to the variable myValues. The value of the variable counter equals 1 at the beginning of the loop.

The first statement in the loop (myArray(counter) = counter +1) assigns the value 2 to the first element of myArray. The second statement (myValues = myValues & myArray(counter) & Chr(13)) enters the current value of myArray's element followed by a carriage return (Chr(13)) into the variable myValues. The statements inside the loop are executed five times. Visual Basic places each new value in the variable myValues and proceeds to the next statement: Redim Preserve myArray(10).

Normally, when you change the size of the array, you lose all the values that were in that array. The ReDim statement alone reinitializes the array. However, you can append new elements to an existing array by following the ReDim statement with the Preserve keyword. In other words, the Preserve keyword guarantees that the redimensioned array will not lose its existing data.

The second For.. .Next loop assigns values to the sixth through tenth elements of myArray. This time the values of the array's elements are obtained by multiplication: counter * counter.

Dimensioning Arrays

Arrays must be dimensioned in a Dim or ReDim statement before they are used. This means that you can't assign a value to an array element until you have declared the array with the Dim or ReDim statement.

Introduction to Access 2003 VBA Programming

0 0


  • Ayden
    How many elements in an array access 2003 vba?
    8 years ago

Post a comment