Page 83 - Excel Progamming Weekend Crash Course

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

58 Friday Evening

Listing 4-1 Demonstrating the use of a dynamic array

Public Sub SumColumn()

Dim data() As Single
Dim total As Single
Dim count As Integer
Dim i As Integer
Dim finished As Boolean
count = 0

Do
count = count + 1
ReDim Preserve data(count)
data(count) = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Value = “” Then finished = True
Loop Until finished
For i = 1 To UBound(data)
total = total + data(i)
Next

ActiveCell.Value = total
End Sub

Determining Array Size

VBA provides two functions that enable you to determine the size of an array.
Specifically, you can determine the largest and smallest legal index for an
array. The functions are:

UBound(arrayname, dimension)
LBound(arrayname, dimension)
UBound returns the largest, and LBound the smallest, legal index for the speci-
fied array. The dimension argument is optional and specifies the dimension of
the array. The default is 1. For example:

Dim MyArray(1 To 5, 10 To 25)
x = LBound(MyArray) ‘ Returns 1
x = UBound(MyArray, 2) ‘ Returns 25