Arrays

Arrays are VBA variables that can hold more than one item of data. An array is declared by including parentheses after the array name. An integer is placed within the parentheses, defining the number of elements in the array:

Dim avData(2)

You assign values to the elements of the array by indicating the element number as follows:

The number of elements in the array depends on the array base. The default base is 0, which means that the first data element is item 0. Dim avData(2) declares a three-element array if the base is 0. Alternatively, you can place the following statement in the declarations section at the top of your module to declare that arrays are 1-based:

Option Base 1

With a base of 1, DimavData(2) declares a two-element array. Item 0 does not exist. You can use the following procedure to test the effect of the Option Base statement:

Sub Array1()

Dim aiData(10) As Integer Dim sMessage As String, i As Integer

For i = LBound(aiData) To UBound(aiData)

aiData(i) = i Next i sMessage = "Lower Bound = " & LBound(aiData) & vbCr sMessage = sMessage & "Upper Bound = " & UBound(aiData) & vbCr sMessage = sMessage & "Num Elements = " & WorksheetFunction.Count(aiData) & vbCr sMessage = sMessage & "Sum Elements = " & WorksheetFunction.Sum(aiData) MsgBox sMessage End Sub

Arrayl uses the LBound (lower bound) and UBound (upper bound) functions to determine the lowest and highest index values for the array. It uses the Count worksheet function to determine the number of elements in the array. If you run this code with Options Base 0, or no Options Base statement, in the declarations section of the module, it will show a lowest index number of 0 and 11 elements in the array. With Options Base 1, it shows a lowest index number of 1 and 10 elements in the array.

Note the use of the intrinsic constant vbCr, which contains a carriage return character. vbCr is used to break the message text to a new line.

If you want to make your array size independent of the Option Base statement, you can explicitly declare the lower bound as well as the upper bound as follows:

Dim avData(1 To 2)

Arrays are very useful for processing lists or tables of items. If you want to create a short list, you can use the Array function as follows:

Dim avData As Variant

avData = Array("North",

"South",

"East",

"West")

You can then use the list in a For...Next loop. For example, you could open and process a series of workbooks called North.xls, South.xls, East.xls, and West.xls:

Sub Array2()

Dim avData As Variant, wkb As Workbook

Dim i As Integer

avData = Array("North", "South", "East", "West")

For i = LBound(avData) To UBound(avData)

Set wkb = Workbooks.Open(Filename:=avData(i) & 1

' .xls")

'Process data here

wkb.Close SaveChanges:=True

Next i

End Sub

0 0

Post a comment