Page 82 - Excel Progamming Weekend Crash Course

d540629 ch04.qxd 9/2/03 9:28 AM Page 57

Session 4 — Syntax and Data in VBA 57

The size argument specifies the number of array dimensions and the number of elements
in each dimension, just as for static arrays. Here are some examples:
Dim Dynamic1() As String
Dim Dynamic2() As Integer
Dim Dynamic3() As Object
...
ReDim Dynamic1(100) ‘ 1 dimension, 101 elements 0 through 100
ReDim Dynamic2(-5 To 5) ‘ 1 dimension, 11 elements -5 through 5
ReDim Dynamic3(5, 5, 5) ‘ 3 dimensions, 216 total elements

You cannot change the type of a dynamic array with a ReDim statement, but you can
change its size as many times as needed. When you use ReDim, any data in the array is nor-
mally lost. You can preserve existing array data (with some limitations) by including the
Preserve keyword in the ReDim statement:
ReDim Preserve Dynamic1(100)

The limitations on preserving data with Preserve are:
If you make an array smaller, data in the trimmed elements is lost.
For multidimensional arrays, you can change the size of only the last dimension.
You cannot change the number of dimensions.
The program shown in Listing 4-1 demonstrates the use of a dynamic array. To use this
program, place the cursor at the top of a column of numbers in any worksheet and then run
the program. The program goes down the column, copying the number from each cell and
placing it in the array. For each cell, ReDim is used to add another element to the array.
When an empty cell is found, the program then goes through the array and calculates the
total of all the values. This result is placed in the cell at the bottom of the column.
In case you need a little review, here are the steps required to create and run this
program.

1. In Excel, place a column of numbers in a worksheet.
2. Press Alt+F11 to open the VBA Editor.
3. In the Project Explorer, double-click the name of the worksheet in which you
placed the numbers. A code-editing window opens.
4. Place the code from Listing 4-1 into the editing window.
5. Switch back to the Excel screen, and make sure the Excel cursor is at the top cell
in the column of numbers.
6. Press Alt+F8 to open the Macros dialog box.
7. Select the macro named SumColumn and then click Run.
The program uses some VBA elements with which you are not yet familiar. You may be
able to figure them out, but if not, you should not be concerned. The point here is to
demonstrate a dynamic array.