Arrays

General Array Processing – Part 1

Declaring Arrays

Arrays are declared in the same manner as other variables (i.e., using the keywords "Dim", "Private", "Public", etc.), except that the array bounds are coded in parentheses following the variable name (if a fixed-length array is being declared) or an empty pair of parentheses follow the variable name (if a variable-length, or dynamic array is being declared).

For fixed-length arrays, you must specify the upper bound of the array; you can optionally specify the lower bound.  By default, the lower bound is 0 (however, see the note on the "Option Base" statement further below). For variable-length arrays, you do not specify any bounds; you just code an empty set of parentheses.

Arrays can have up to 60 dimensions (although it is rare that you would have more than three dimensions; one or two is the norm).

The syntax for declaring an array is:

[Dim | Private | Public | Static | Global] arrayname([lowerbound To] upperbound[, …])   [As datatype]

Sample declarations for a one-dimensional array:

 Array Declaration Notes Dim aintCount(9) As Integer declares a 10-element array, indexed 0 to 9 Dim aintCount(0 To 9) As Integer same as above, with explicit lower bound Dim aintCount(1 To 10) As Integer declares a 10-element array, indexed 1 To 10 Dim aintCount(3 To 12) As Integer declares a 10-element array, indexed 3 To 12 Dim aintCount(-4 To 5) As Integer declares a 10-element array, indexed -4 To 5 Dim aintCount() As Integer declares a variable-length array whose bounds will be determined at run-time

Note from the above declarations that the lower bound is not restricted to 0 or 1, and it can even be negative.

To refer to an individual element of an array in a procedural statement, place the desired index in parentheses next to the array name. For example, the following statement will display the 5th element of aintCount  on the form (assuming the first or second declaration above):

Print aintCount(4)

Sample declarations for multi-dimensional arrays:

 Array Declaration Notes Dim asngSales(1 To 4, 1 To 5) As Single declares a 2-dimensional array (four rows indexed 1 to 4, by five columns indexed 1 to 5) Dim asngResults(3, 1 To 12, 2 To 6) As Single declares a 3-dimensional array (the first dimension has four elements indexed 0 to 3, within that, the second dimension has 12 elements indexed 1 to 12, and within that, the third dimension has five elements indexed 2 to 6)

To refer to an individual element of a multi-dimensional array in a procedural statement, place the desired indices in parentheses next to the array name (you must have one index per dimension, separated by commas).  Examples:

Print asngSales(2, 3)

Print asngResults(0, 11, 5)

Option Base

As explained above, by default, if you do not explicitly code a lower bound, the lower bound of an array is 0. However, there is a statement called Option Base, which, if you code Option Base 1, will force VB to default the lower bound of arrays to 1. The Option Base statement can only have the number 0 or 1 after it, and 0 is the default – so you never need to code Option Base 0. The Option Base statement, if used, would be coded at the beginning of a code module and is enforced only at the module level. Recommendation: Do not use Option Base; explicitly code the lower bound of your array declarations.

Initializing Arrays

Unfortunately, VB (up through version 6) does not provide a mechanism for initializing arrays (or any variable) at the same time they are declared –initialization can only be done with executable assignment statements. Older versions of BASIC (prior to VB) provided a pair of statements (READ and DATA) that would enable you to load a set of constant data items into an array fairly painlessly using a For/Next loop. But alas, READ and DATA were removed from the language when VB came along.

The most direct (although tedious) way to load constant data into an array is with individual assignment statements:

Dim astrStateName(1 To 50) As String

astrStateName(2) = "ALABAMA"

.

.

.

astrStateName(49) = "WEST VIRGINIA"

astrStateName(50) = "WYOMING"

Another way to do this is with the Array function, which is convenient to use, but incurs the overhead of the Variant datatype. The way to use the function is as follows:

• First, declare a Variant variable to hold your array. It may be declared with or without the open parentheses. Recall that since Variant is the default datatype, the "As Variant" clause is optional:

Dim astrDayAbbrev() As Variant

• Second, use the Array function to assign a list of data items to the variable :

astrDayAbbrev = Array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")

The above statement would cause astrDayAbbrev to become an array with a lower bound of 0 or 1 (depending on the setting of Option Base) and an upper bound of 6 or 7, accordingly. From that point on, astrDayAbbrev could be used like a standard array.

The following example uses a For/Next loop to load an array of 10 integers with random numbers between 1 and 100, then uses a second For/Next loop to print out the results.

Dim aintRandomNum(1 To 10) As Integer

Dim intX                   As Integer

Randomize

' load the "aintRandomNum" array with random numbers between 1 and 100 ...

For intX = 1 To 10

aintRandomNum(intX) = Int(100 * Rnd + 1)

Next

' display the contents of the "aintRandomNum" array ...

For intX = 1 To 10

Print aintRandomNum(intX)

Next

If desired, set up a "Try It" project and place the code shown in the above example in the cmdTryIt_Click event. When you run the project, your results will resemble the following:

Example: Loading a Two-Dimensional Array from a File and Printing Its Contents

The next example reads in a file containing four weeks worth of sales data. Each record represents one week of data. There are seven amount fields in each record, representing sales amounts for Sunday through Saturday of each week. The format of the file is sequential, comma-delimited. Its contents is as follows:

1234,1765,3244,2453,2364,4567,3256

4646,1231,3466,2344,3446,3242,1231

1454,3466,1314,3464,2312,3466,6578

5453,4356,3453,3423,2355,2356,5534

The sample program reads this data into a two-dimensional (7 X 4) array, and then prints its contents on the form. The results of the run is shown below:

The code behind the "Try It" button (heavily documented) is as follows:

Private Sub cmdTryIt_Click()

' Declare a 2-dimensional array consisting of 4 rows and 7 columns.

' The first dimension (1 to 4) represents 4 weeks. The second dimension

' (1 to 7) represents 7 days in each week.

Dim adblDailySales(1 To 4, 1 To 7) As Double

' Declare variables for file processing ...

Dim strSalesFileName                As String

Dim strBackSlash                    As String

Dim intSalesFileNbr                 As Integer

' Declare variables to be used to access elements of the array ...

Dim intX                            As Integer

Dim intY                            As Integer

' Set up the file name ...

strBackSlash = IIf(Right\$(App.Path, 1) = "\", "", "\")

strSalesFileName = App.Path & strBackSlash & "SALES.DAT"

' Get an available file handle ...

intSalesFileNbr = FreeFile

' Open the sales file ...

Open strSalesFileName For Input As #intSalesFileNbr

' Use a nested For/Next loop to load the sales data from the file

' (one field at a time) into the appropriate element of the array.

' The "outer" loop (using intX) executes four times, once for each

' record in the file ...

For intX = 1 To 4

' The "inner" loop (using intY) executes seven times for every one

' execution of the outer loop - once for each field (sales amount)

' in the record ...

For intY = 1 To 7

' Note that the Input # statement specifies only one item:

' adblDailySales(intX, intY). This will cause the current field

' of the current record to be stored in that element of the array.

' Thus, this nested loop will cause the "adblDailySales" array to

' be loaded in the following sequence:

' (1,1) (1,2) (1,3) (1,4) (1,5) (1,6) (1,7)

' (2,1) (2,2) (2,3) (2,4) (2,5) (2,6) (2,7)

' (3,1) (3,2) (3,3) (3,4) (3,5) (3,6) (3,7)

' (4,1) (4,2) (4,3) (4,4) (4,5) (4,6) (4,7)

Next

Next

' Close the sales file ...

Close #intSalesFileNbr

' Print headings on the form. Printing will be done using the comma separator.

Print "Week #", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"

Print "------", "---", "---", "---", "---", "---", "---", "---"

' Use a nested For/Next loop to print the sales data from the array.

' The "outer" loop (using intX) executes four times, once for each

' "row" of the array ...

For intX = 1 To 4

' Print "intX" itself, which identifies the week being printed. By ending the

' Print statement with a comma, the print position will not advance to the next

' line; rather, the next item will print in the next print "zone".

Print intX,

' The "inner" loop (using intY) executes seven times for every one

' execution of the outer loop - once for each "column" (sales amount)

' in the current "row" ...

For intY = 1 To 7

' Print the current element of the array. Again, by ending the Print

' statement with a comma, the print position will not advance to the next

' line; rather, the next item will print in the next print "zone".

Next

' The Print statement by itself will force a line break.

Print

Next

End Sub

Examples: Using ReDim and ReDim Preserve

When you don't know in advance how many array elements you will need, such as when loading data from a file or from keyboard input, you can use dynamic arrays. You change the size of a dynamic array with the ReDim statement (with or without the Preserve option). The syntax is:

ReDim [Preserve] varname(subscripts) [As type]

The ReDim statement (without the Preserve option) resizes an array, but does not retain any values previously loaded into the array, which makes it not as useful as ReDim Preserve.

You could use ReDim by itself  when you don't know how big the array should be at design-time, but you obtain the size prior to loading the array.

Following is an example which calculates the average temperature from a series of temperatures input by the user – but the user is prompted in advance for the number of temperatures to be entered.

Dim aintTemperatures() As Integer

Dim intNbrToEnter      As Integer

Dim intX               As Integer

Dim lngTotTemp         As Long

Dim dblAvgTemp         As Double

intNbrToEnter = Val(InputBox("Number of temperatures to be entered:"))

If intNbrToEnter = 0 Then Exit Sub

ReDim aintTemperatures(1 To intNbrToEnter)

For intX = 1 To intNbrToEnter

aintTemperatures(intX) = Val(InputBox("Temperature #" & intX & ":"))

Print "Temperature #" & intX & ": " & aintTemperatures(intX)

lngTotTemp = lngTotTemp + aintTemperatures(intX)

Next

dblAvgTemp = lngTotTemp / intNbrToEnter

Print

Print "The average temperature entered was " & Format\$(dblAvgTemp, "Fixed")

If desired, set up a "Try It" project and place the code shown in the above example in the cmdTryIt_Click event. When you run the project, your results will resemble the following:

First, you will be prompted to input the number of values to be entered:

Next, you will be prompted repeatedly (the number of times you specified in the first prompt – 7 in this case) for a temperature to enter:

When you finish entering all the temperatures, the average will be printed:

ReDim with the Preserve option is useful when you don't know in advance how many items will be input. When you use ReDim Preserve,  you typically are dynamically changing (increasing) the upper bound of the array in a loop as you load the array.  Following is an example of how the previous example could be modified to use ReDim Preserve. Note that within the input loop, the variable "intX" is incremented by 1. Then ReDim Preserve is used to re-size the aintTemperatures array using the new value of intX.

Dim aintTemperatures() As Integer

Dim intCurrTemp        As Integer

Dim intNbrOfEntries    As Integer

Dim intX               As Integer

Dim lngTotTemp         As Long

Dim dblAvgTemp         As Double

intCurrTemp = Val(InputBox("Enter a temperature (0 to quit):"))

Do Until intCurrTemp = 0

intX = intX + 1

ReDim Preserve aintTemperatures(1 To intX)

aintTemperatures(intX) = intCurrTemp

Print "Temperature #" & intX & ": " & aintTemperatures(intX)

lngTotTemp = lngTotTemp + aintTemperatures(intX)

intCurrTemp = Val(InputBox("Enter a temperature (0 to quit):"))

Loop

If intX = 0 Then Exit Sub

dblAvgTemp = lngTotTemp / intX

Print

Print "The average temperature entered was " & Format\$(dblAvgTemp, "Fixed")

If desired, set up a "Try It" project and place the code shown in the above example in the cmdTryIt_Click event. When you run the project, your results will be similar to the previous example.

Example: Loading a UDT Array from a File and Printing Its Contents

It is often useful to work with the contents of a set of data (be it the contents of a small text file or the recordset results of a database query) in a UDT array, where the structure of the UDT array mirrors the structure of the fields of the data set to be worked with.

In the next example, the contents of the "EMPLOYEE.DAT" file (comma-delimited version) that we have worked with in some of the previous topics will be loaded into a UDT array, and then its contents will be printed on the form.

The code listing for the sample program is shown below.  Explanations of pertinent parts of the program will then follow.

Option Explicit

Private Type EmployeeRecord

EmpName    As String

DeptNbr    As Integer

JobTitle   As String

HireDate   As Date

HrlyRate   As Single

End Type

Private maudtEmpRecord() As EmployeeRecord

Private Sub cmdClear_Click()

Cls

End Sub

Private Sub cmdExit_Click()

End

End Sub

Private Sub cmdTryIt_Click()

Dim strEmpFileName  As String

Dim strBackSlash    As String

Dim intEmpFileNbr   As Integer

Dim intRecCount     As Integer

Dim intX            As Integer

strBackSlash = IIf(Right\$(App.Path, 1) = "\", "", "\")

strEmpFileName = App.Path & strBackSlash & "EMPLOYEE.DAT"

intEmpFileNbr = FreeFile

Open strEmpFileName For Input As #intEmpFileNbr

intRecCount = 0

Do Until EOF(intEmpFileNbr)

intRecCount = intRecCount + 1

ReDim Preserve maudtEmpRecord(1 To intRecCount)

With maudtEmpRecord(intRecCount)

Input #intEmpFileNbr, .EmpName, _

.DeptNbr, _

.JobTitle, _

.HireDate, _

.HrlyRate

End With

Loop

Close #intEmpFileNbr

For intX = LBound(maudtEmpRecord) To UBound(maudtEmpRecord)

With maudtEmpRecord(intX)

Print .EmpName; _

Tab(25); Format\$(.DeptNbr, "@@@@"); _

Tab(35); .JobTitle; _

Tab(55); Format\$(.HireDate, "mm/dd/yyyy"); _

Tab(70); Format\$(Format\$(.HrlyRate, "Standard"), "@@@@@@@")

End With

Next

End Sub

In the General Declarations section (after "Option Explicit", but before any Sub or Function is defined), the User-Defined Type "EmployeeRecord" is declared:

Private Type EmployeeRecord

EmpName    As String

DeptNbr    As Integer

JobTitle   As String

HireDate   As Date

HrlyRate   As Single

End Type

This is followed by the declaration of the UDT variable "maudtEmpRecord" ("As" datatype "EmployeeRecord"). Note that "maudtEmpRecord" is declared with a pair of empty parentheses after its name, indicating that this will be a dynamic array.

Private maudtEmpRecord() As EmployeeRecord

In the input loop that loads the records from the file into the UDT array, note that the technique used in the previous example to increase the upper bound of the dynamic array is used here as well.  The variable "intRecCount" is incremented by 1, then ReDim Preserve is used to re-size the maudtEmpRecord array using the new value of intRecCount.

intRecCount = intRecCount + 1

ReDim Preserve maudtEmpRecord(1 To intRecCount)

This is followed by the code to load the fields of the current input record into the corresponding items of the current element of the maudtEmpRecord array. Note that a With/End With block is used to "factor out" the reference to "maudtEmpRecord(intRecCount)".

With maudtEmpRecord(intRecCount)

Input #intEmpFileNbr, .EmpName, _

.DeptNbr, _

.JobTitle, _

.HireDate, _

.HrlyRate

End With

If With/End With was not used, the statement above would have to be written as follows:

Input #intEmpFileNbr, maudtEmpRecord(intRecCount).EmpName, _

maudtEmpRecord(intRecCount).DeptNbr, _

maudtEmpRecord(intRecCount).JobTitle, _

maudtEmpRecord(intRecCount).HireDate, _

maudtEmpRecord(intRecCount).HrlyRate

The last portion of the program loops through the UDT array and prints its contents on the form. The "For" statement introduces the LBound and UBound functions, which determine the lower- and upper- bounds, respectively, of an array. (In this case, "LBound(maudtEmpRecord)" would evaluate to 1, and "LBound(maudtEmpRecord)" would evaluate to the value of intRecCount.) The LBound and UBound functions are examined in greater detail a little further below.  Again, a With/End With block is used to "factor out" the reference to "maudtEmpRecord(intX)".

For intX = LBound(maudtEmpRecord) To UBound(maudtEmpRecord)

With maudtEmpRecord(intX)

Print .EmpName; _

Tab(25); Format\$(.DeptNbr, "@@@@"); _

Tab(35); .JobTitle; _

Tab(55); Format\$(.HireDate, "mm/dd/yyyy"); _

Tab(70); Format\$(Format\$(.HrlyRate, "Standard"), "@@@@@@@")

End With

Next

When the sample program is run, its output is similar to that of previous file-processing sample programs:

More on ReDim and ReDim Preserve (from the MSDN/VB Help)

The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts).  You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array.  ReDim sets Variant arrays to Empty, numeric arrays to 0, string arrays to "" and object arrays to Nothing.

If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.

ReDim X(10, 10, 10)

. . .

ReDim Preserve X(10, 10, 15)

Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error.

If you make an array smaller than it was, data in the eliminated elements will be lost.

The Erase Statement

The Erase statement is used to clear the contents of a dynamic array. The syntax is:

Erase arrayname

Example:

Erase aintTemperatures

The LBound and UBound Functions

You can use the LBound and UBound functions to determine the lower- and upper- bounds, respectively, of an array. The syntax is:

LBound(arrayname[()][, dimension])

UBound(arrayname[()][, dimension])

Note that the array name may be specified with or without a set of empty parentheses. The second parameter, which is optional, specifies which dimension of the array you want the bound of, which can be useful if you have a multi-dimensional array. If dimension is omitted, 1 is assumed.

For example, if you have an array declared as:

Dim aintTemperatures(1 To 7) As Integer

Then the statement

Print LBound(aintTemperatures)

would print 1

And the statement

Print UBound(aintTemperatures)

would print 7.

For another example, if you have an array declared as:

Dim aintTemperatures(1 To 7, -5 To 4) As Integer

Then the statement

Print LBound(aintTemperatures, 2)

would print -5

And the statement

Print UBound(aintTemperatures, 2)

would print 4.

The LBound and UBound functions are useful when you need to loop through a dynamic array after it has been loaded. For example, say you loaded the dynamic array in an initial Sub when the program began, and then later, in some other Sub, you need to process its values. If you used local variables to set the bounds of the array with ReDim Preserve in the initial Sub, you won't know how many elements the array contains when you need to process the array in the other Sub (because the values of the local variables would not be retained once you leave the initial Sub). LBound and UBound can help in that situation:

For intX = LBound(maintTemperatures) To UBound(maintTemperatures)

' process maintTemperatures(intX)

Next

The LBound and UBound functions are also needed when you must process an array that has been passed to a Sub or Function, because the bounds of the array cannot be specified in the Sub or Function header's argument list.  The reason that the bounds of an array cannot be specified in a Sub or Function header's argument list is that the same Sub or Function can be used to process arrays of different sizes. Therefore, you should use the LBound and UBound functions in the Sub or Function to determine the bounds of the array that has been passed.

Examples: Handling an Empty Array

If a dynamic array has no elements (i.e., a ReDim or ReDim Preserve statement was never executed on it), when you subsequently attempt to reference an element of that array or if you attempt to use LBound or UBound on that array, a run-time error '9' (Subscript out of range) error will occur.

For example, let's take the sample employee file processing program above and modify it to read in an empty file:

strEmpFileName = App.Path & strBackSlash & "EMPTY.DAT"

The statements within the input loop that starts with:

Do Until EOF(intEmpFileNbr)

will never be executed, because EOF (end-of-file) will be triggered as soon as the file is opened. This means that the maudtEmpRecord array will never be loaded.

When the program tries to reference it with this statement:

For intX = LBound(maudtEmpRecord) To UBound(maudtEmpRecord)

the run-time error will occur:

There are two ways this error can be handled. The first is to add error-handling code to the procedure, as shown below:

Private Sub cmdTryIt_Click()

Dim strEmpFileName  As String

Dim strBackSlash    As String

Dim intEmpFileNbr   As Integer

Dim intRecCount     As Integer

Dim intX            As Integer

On Error GoTo cmdTryIt_Click_Error

strBackSlash = IIf(Right\$(App.Path, 1) = "\", "", "\")

strEmpFileName = App.Path & strBackSlash & "EMPTY.DAT"

intEmpFileNbr = FreeFile

Open strEmpFileName For Input As #intEmpFileNbr

intRecCount = 0

Do Until EOF(intEmpFileNbr)

intRecCount = intRecCount + 1

ReDim Preserve maudtEmpRecord(1 To intRecCount)

With maudtEmpRecord(intRecCount)

Input #intEmpFileNbr, .EmpName, _

.DeptNbr, _

.JobTitle, _

.HireDate, _

.HrlyRate

End With

Loop

Close #intEmpFileNbr

' The following statement will cause the error-handling code

' to execute ...

For intX = LBound(maudtEmpRecord) To UBound(maudtEmpRecord)

With maudtEmpRecord(intX)

Print .EmpName; _

Tab(25); Format\$(.DeptNbr, "@@@@"); _

Tab(35); .JobTitle; _

Tab(55); Format\$(.HireDate, "mm/dd/yyyy"); _

Tab(70); Format\$(Format\$(.HrlyRate, "Standard"), "@@@@@@@")

End With

Next

cmdTryIt_Click_Exit:

Exit Sub

cmdTryIt_Click_Error:

If Err.Number = 9 Then

Print "File was empty."

Else

MsgBox "The following error occurred:" & vbNewLine _

& "Error # " & Err.Number & " - " & Err.Description, _

vbCritical, _

"Error"

End If

Resume cmdTryIt_Click_Exit

End Sub

When the program runs, the appropriate message will be displayed, as shown below:

The second way to handle this is to use the SafeArrayGetDim Windows API. In my view, this is a cleaner and more precise way of handling the empty array issue.

Once again, the code for the sample program is shown in its entirety below, with the modifications needed to use the SafeArrayGetDim function bolded. Note that the API declaration for the function must be made in the General Declarations section of the program. Later on, the following syntax is used:

If SafeArrayGetDim(arrayname) > 0 Then ...

The function will return 0 if the array is empty, a positive number if the array is populated.

Option Explicit

Private Declare Function SafeArrayGetDim Lib "oleaut32.dll" _

(ByRef saArray() As Any) As Long

Private Type EmployeeRecord

EmpName    As String

DeptNbr    As Integer

JobTitle   As String

HireDate   As Date

HrlyRate   As Single

End Type

Private maudtEmpRecord() As EmployeeRecord

Private Sub cmdClear_Click()

Cls

End Sub

Private Sub cmdExit_Click()

End

End Sub

Private Sub cmdTryIt_Click()

Dim strEmpFileName  As String

Dim strBackSlash    As String

Dim intEmpFileNbr   As Integer

Dim intRecCount     As Integer

Dim intX            As Integer

strBackSlash = IIf(Right\$(App.Path, 1) = "\", "", "\")

strEmpFileName = App.Path & strBackSlash & "EMPTY.DAT"

intEmpFileNbr = FreeFile

Open strEmpFileName For Input As #intEmpFileNbr

intRecCount = 0

Do Until EOF(intEmpFileNbr)

intRecCount = intRecCount + 1

ReDim Preserve maudtEmpRecord(1 To intRecCount)

With maudtEmpRecord(intRecCount)

Input #intEmpFileNbr, .EmpName, _

.DeptNbr, _

.JobTitle, _

.HireDate, _

.HrlyRate

End With

Loop

Close #intEmpFileNbr

If SafeArrayGetDim(maudtEmpRecord) > 0 Then

For intX = LBound(maudtEmpRecord) To UBound(maudtEmpRecord)

With maudtEmpRecord(intX)

Print .EmpName; _

Tab(25); Format\$(.DeptNbr, "@@@@"); _

Tab(35); .JobTitle; _

Tab(55); Format\$(.HireDate, "mm/dd/yyyy"); _

Tab(70); Format\$(Format\$(.HrlyRate, "Standard"), "@@@@@@@")

End With

Next

Else

Print "File was empty."

End If

End Sub

When the program runs, the appropriate message will be displayed, as shown below: