Multi-Dimensional Arrays

So far you have only looked at arrays with a single dimension. You can actually define arrays with up to 60 dimensions, although few people would use more than two or three dimensions. The following statements declare two-dimensional arrays:

Dim avData(10,20)

Dim avData(1 To 10,1 to 20)

You can think of a two-dimensional array as a table of data. The preceding example defines a table with 10 rows and 20 columns.

Arrays are very useful in Excel for processing the data in worksheet ranges. It can be far more efficient to load the values in a range into an array, process the data, and write it back to the worksheet, than to access each cell individually.

The following procedure shows how you can assign the values in a range to a Variant. The code uses the LBound and UBound functions to find the number of dimensions in avData. Note that there is a second parameter in LBound and UBound to indicate which index you are referring to. If you leave this parameter out, the functions refer to the first index:

Sub Array3()

Dim avData As Variant, vUBound As Variant Dim Message As String, i As Integer avData = Range("A1:A20").Value i = 1

Message = "Lower Bound = " & LBound(avData, i) & vbCr Message = Message & "Upper Bound = " & UBound(avData, i) & vbCr MsgBox Message, , "Index Number = " & i i = i + 1

On Error Resume Next vUBound = UBound(avData, i) If Err.Number <> 0 Then Exit Do On Error GoTo 0 Loop

Message = "Number of Non Blank Elements =" _

& WorksheetFunction.CountA(avData) & vbCr

MsgBox Message End Sub

The first time around, the Do...Loop, Array3 determines the upper and lower bounds of the first dimension of avData, as i has a value of 1. It then increases the value of i to look for the next dimension. It exits the loop when an error occurs, indicating that no more dimensions exist.

By substituting different ranges into Array3, you can determine that the array created by assigning a range of values to a Variant is two-dimensional, even if there is only one row or one column in the range. You can also determine that the lower bound of each index is 1, regardless of the Option Base setting in the declarations section.

Dynamic Arrays

When writing your code, it is sometimes not possible to determine the size of the array that will be required. For example, you might want to load the names of all the .xls files in the current directory into an array. You won't know in advance how many files there will be. One alternative is to declare an array that is big enough to hold the largest possible amount of data—but this would be inefficient. Instead, you can define a dynamic array and set its size when the procedure runs.

You declare a dynamic array by leaving out the dimensions:

Dim avData()

You can declare the required size at run time with a ReDim statement, which can use variables to define the bounds of the indexes:

ReDim avData(iRows, iColumns)

ReDim avData(iminRow to imaxRow, iminCol to imaxCol)

ReDim will re-initialize the array and destroy any data in it, unless you use the Preserve keyword. Preserve is used in the following procedure that uses a Do...Loop to load the names of files into the dynamic array called asFNames, increasing the upper bound of its index by one each time to accommodate the new name.

The Dir function returns the first filename found that matches the wildcard specification in sFType. Subsequent usage of Dir, with no parameter, repeats the same specification, getting the next file that matches, until it runs out of files and returns a zero-length string:

Sub FileNames()

Dim sFName As String Dim asFNames() As String Dim sFType As String Dim i As Integer sFType = "*.xls" sFName = Dir(sFType) Do Until sFName = "" i = i + 1

ReDim Preserve asFNames(1 To i) asFNames(i) = sFName sFName = Dir Loop

MsgBox "No files found" Else

For i = 1 To UBound(asFNames)

MsgBox asFNames(i) Next i End If End Sub

If you intend to work on the files in a directory and save the results, it is a good idea to get all the filenames first, as in the FileNames procedure, and use that list to process the files. It is not a good idea to rely on the Dir function to give you an accurate file list while you are in the process of reading and overwriting files.

0 0

Post a comment