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(1) = "ALASKA"
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:
Dim astrDayAbbrev() As Variant
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.
Example: Loading and Printing and Array of Random Numbers
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:
Download the VB project code for the example above here.
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)
Input #intSalesFileNbr, adblDailySales(intX, intY)
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".
Print adblDailySales(intX, intY),
Next
' The Print statement by itself will force a line break.
Next
End Sub
Download the VB project code for the example above here.
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 "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:
Download the VB project code for the example above here.
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 "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.
Download the VB project code for the example above here.
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:
Download the VB project code for the example above here.
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:
Download the VB project code for the example above here.
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:
Download the VB project code for the example above here.
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:
Download the VB project code for the example above here.