Say thanks for Loops and Arrays.
Visit our e-book
project site.
Visit our e-book
project site.
Loops provide for the repetition of tasks in a program. When you are coding a program and find yourself doing the same thing over and over, you should think about how a loop or a subprogram might be used instead. Using a loop will save you lines and lines of programming if the task or operation has to be repeated (or even if it only has the potential to be repeated). This document presents a basic overview for Access 2000 VBA programmers of loops and arrays through a series of examples. If you have questions about the syntax of the examples, you will find a short reference section borrowed from Access Help at the end of this document.
Lets look at an example of a repetitious task where loops would save lines of code.
strAnswer = InputBox("Are you ready to continue? (Y/N)")
' check for Y or N
If strAnswer = "Y" then Call Continue
If strAnswer = "N" then Exit Sub
' Y or N was not found so continue asking the user
strAnswer = InputBox("Are you ready to continue? (Y/N)")
If strAnswer = "Y" then Call Continue
If strAnswer = "N" then Exit Sub
'... and so on and on and on...
Do
strAnswer = InputBox("Are you ready to continue? (Y/N)")
If strAnswer = "Y" then Call Continue
If strAnswer = "N" then Exit Sub
Loop Until strAnswer = "Y" or strAnswer = "N"
The advantage of the Do ...Loop Until loop seems very clear in this example. This is not always the case in programming situations, however, so we will use examples to make the practical side of loops apparent. The remainder of this document is organized into the syntax choices for programming with loops. As seen above, examples provide most of the information. A short reference section borrowed from Access Help is also available.
The Do ...Loop above had the logic at the bottom of the loop, and this structure forced it to run a minimum of one time. When the logic is positioned at the top of the loop, however, then the loop can potentially be skipped altogether. In the next example, a loop can execute as many times as necessary, and it will only execute if the user fails to follow instructions. This illustrates how loops can act as If statements.
strAnswer = InputBox("Are you ready to continue? (Y/N)")
Do While (strAnswer <> "Y" And strAnswer <> "N")
strAnswer = InputBox("Answer Y or N") ' executed only if user doesn't type Y or N
Loop
If strAnswer = "Y" then Call Continue
If strAnswer = "N" then Exit Sub
Do Loop syntax supports positioning logic at the beginning of the loops (Do While... Loop) instead of at the ending of the loops (Do... Until Loop). Positioning logic at the beginning or ending of a loop determines whether your program should or should not execute the code in the loop at least once.
Another syntax, called the While Wend loop, positions logic at the beginning of the loop. It is really no different from the Do While so only brief mention is made here and an example follows shortly.
Do Loops and While Wend loops are not always the best choice of loop syntax. Because the loop does not have a built in counter, you would have to program it needed for the loop. This works, of course, but requires two unnecessary lines of code.
intCounter = 0
While intCounter < 10
intCounter = intCounter + 1
StrBanner = strBanner & "** Happy Millenium **"
Wend
Suppose you need a variable that consists of "** Happy Millenium **" repeated 10 times for a banner. Here is how the For...Next loop can fill that need.
For intCounter = 1 to 10 StrBanner = strBanner & "** Happy Millenium **" Next
The Do Loop and For Next both work well in these programming examples. Since they are both loops, however, couldn't we use a For in place of a Do, and vice versa? The advantages and disadvantages of the two structures should become apparent in the examples below.
Using For when Do is appropriate. Right now you may think either For or Do is okay, but if you found the constant 999 in your program two months after you had written it, it would probably seem puzzling. In this case, Do is preferable, in that it is more self-documenting (i.e., the condition for satisfying the loop and continuing in the program will be clearer).
For intCounter = 1 to 999
strAnswer = InputBox("Are you ready to continue? (Y/N)")
If strAnswer = "Y" then Call Continue
If strAnswer = "N" then Exit Sub
Next
Another loop format is For Each... Next. Access 2000 provides for both indexed (or numbered) and non-indexed references to its object collections. Forms, tables, queries, buttons, labels, text boxes, list boxes and so forth are objects in Access collections. By using loops, you can reference the objects without knowing their specific object names. This would be very handy, for example, if you wanted to make all labels and text boxes in your application appear in Tahoma font, size 12. Setting these values on all the property sheets by hand is hit and miss, but a loop will not miss a single object.
Let us now create a procedure to force all labels and text boxes to show in size 12 Tahoma font. This example uses the For Each...Next loop to search the Is Label property and the Is TextBox property of all controls on the open form. This illustrates another type of loop that has no counter.
Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is Label Or TypeOf ctl Is TextBox Then
ctl.FontName = "Tahoma"
ctl.FontSize = 12
End If
Next ctl
The code above can be placed in the Sub Form_Load event procedure for each form. It can be used exactly as it appears, or it can be placed in a public procedure in a standard module and then called by a line of code in Form_Load, such as: Call Tahoma12(Me). In the latter case, you change Me.Controls to frm.Controls and give Sub Tahoma12 one argument (frm as Form). Almost without exception, it is better programming practice to place code that will pertain to more than one form in a general procedure that appears inside a standard module.
If you are not already familiar with arrays, you may want to think of an array as nothing more than a list. We all have lists in our lives, such as shopping lists, to-do lists, birthday lists, lists of stock prices for every day last week, and so forth. All items in an array, like the items in a list, have a position somewhere between first and last. Items are numbered from lowest to highest in arrays, which makes the For Next loop particularly useful in programming arrays.
The following example would calculate the weekly average price for a stock, based on an array containing the stock's price from each of five days. In these array examples the array variable is bolded.
For intCounter = 1 to 5
AveragePrice = AveragePrice + Price(intCounter)
Next
AveragePrice = AveragePrice / 5
Suppose you want the name of each day of the week to appear in an array. The following function creates an array of names in a Variant and then provides the names to the caller. Variants can be integers, strings, and so forth or arrays of these.
Public Function DayOfWeek(intDay As Integer) As String
Dim WeekDay As Variant
WeekDay = Array("Sun","Mon","Tue","Wed","Thu","Fri","Sat")
DayOfWeek = WeekDay(intDay)
End Function
Another practical example involves an array of fiscal quarter closing dates for a business. In the following program, an array of dates is built in a Variant and formatted, and one date is then returned to the caller.
Public Function DateQuarterEnds(intQuarter As Integer) As Date
Dim DateQuarter As Variant
DateQuarter = Array(Format("03/31/00", "Short Date"), _
Format("06/30/00", "Short Date"), _
Format("09/30/00", "Short Date"), _
Format("12/31/00", "Short Date"))
DateQuarterEnds = DateQuarter(intQuarter)
End Function
Another very important use for arrays is as arguments in function and sub calls, as shown in the following example. The purpose here is to allow you to use the procedure with an unknown number of array elements (from 0 to N) in the argument list.
Here is a function that returns the position number of the smallest numeric value in an array of values. Two noteworthy benefits of arrays in this case are: (1) that the array can be of any length; and (2) that all items in the array can be referenced by their positions and values. Here is code that would allow you to find the lowest grade in a list (or array) of student grades.
Public Function Smallest(Grades() As Single) As Integer ' find the position of the lowest grade in an array of grades Dim intPosition As Integer, intLowestPosition As Integer Dim sglLowest As Single sglLowest = 9999999.9 For intPosition = LBound(Grades) To UBound(Grades) If sglLowest > Grades(intPosition) Then sglLowest = Grades(intPosition) intLowestPosition = intPosition End If Next Smallest = intLowestPosition End Function ' next an example of how to include Arrays in calls Public Sub TestSmallest() Dim Grades(0 To 2) As Single Grades(0) = 95# Grades(1) = 75# Grades(2) = 50# Debug.Print Smallest(Grades); Grades(Smallest(Grades)) ' Result: 2 50 representing the position and value of the smallest grade End Sub
In the For Next loop, two new built-in functions are introduced. L- and U-Bound return the lower and upper limits of the array. Because you are using these functions, the program does not have to know the exact size of the Grades array. More about these functions next.
The functions are extremely useful with arrays. Did you notice in the example that Function Smallest could be used for any number of grades in any course? So, the program will have a longer shelf-life, and will be easier to maintain. Here is some more on LBound and UBound for one-dimension arrays.
'LBound(array) returns the starting or smallest index of the one-dimension array 'UBound(array) returns the ending or largest index of the one-dimension array For intPosition = LBound(Grades) To UBound(Grades)
In the Declarations section of VBA code, you can include Option Base 1. This would instruct Access always to start numbering your arrays with a lower bound of 1. If you do not add this declaration, Access begins its arrays at 0. Even if you omit the declaration, however, you can override zero by using the syntax: Dim Grades(1 to 3).
Option base can be confusing because some of the built-in Access functions use the declaration, while other functions ignore it completely and always begin at zero. The Array function and the Dim function use the Base, but the ParamArray function ignores the base and always uses 0.
Students are sometimes confused with character position numbering and element numbering in arrays. Strings never begin numbering at position 0. In fact, 0 will cause an error.
Good Advice: embrace option base 0. If you elect to use base 1 you will have to keep up with when and where the base 0 defaults occur. If you elect to use base 0 always there are fewer complications.
Not all arrays you use have to be of only one dimension. As an Access user, you already are familiar with datasheets for displaying data in tables and queries. The datasheets contain columns and rows representing fields and records, respectively. A multi-dimensional array [Dim StateData(0 to 2, 0 to 49)] could contain the state name, a 2-letter abbreviation of the name, and the sales tax rate. Notice it is three fields, but the dimension is two because of Option Base 0. There could be 50 records for the 50 states. If you had this array its best use might be to provide very fast sales tax lookups.
So, how do you get the data into a multidimensional array? Example 10 illustrates the manual method and requires typing and careful proofreading, because the data was included in the logic (see Sub TestSmallest() above). In practice, we keep data in tables and logic in programs. When you mix the two you usually have some bad consequences at a later time: program maintenance. For example, if the state sales tax rate were in a table, then you would not have to change the program whenever the tax rates change.
Access provides a means of converting table data into two-dimension arrays. By using recordset programming, you can have all records or a few records placed into an array. For example, you could get the next 50 records from a table into StateDate(2,49) in this way:
'3 columns and 50 rows into StateDate(2,49)
Dim rs as DAO.Recordset
Set rs = CurrentDB.Openrecordset( _
'SELECT Name, ShortName, TaxRate FROM tblSalesTax', dbOpenDynaset)
StateData = rs.GetRows(50)
rs.Close
Set rs = Nothing
These functions serve multi-dimensional arrays as well as single-dimension arrays. To learn the lower and upper bounds of the columns of the array, ask for LBound(StateData,1) and UBound(StateData,1). The 1 means the first dimension (fields or columns). Similarly, use LBound(StateData,2) and UBound(StateData,2) for the second dimension (rows or records).
intNumberFields = UBound(StateData,1) + 1 ' number of fields/columns intNumberRows = UBound(StateData,2) + 1 ' number of records/rows intIndexofFirstCol = LBound(StateData,1) ' fields starting for-loop index intIndexofLastCol = UBound(StateData,1) ' fields ending for-loop index intIndexofFirstRow = LBound(StateData,2) ' records starting for-loop index intIndexofLastRow = UBound(StateData,2) ' records ending for-loop index
We are used to seeing Access datasheets. They are analogous to 2-dimension arrays with columns (fields) and rows (records). A datasheet with a single column (field) is analogous to a 1-dimension array. A cube is analogous to a 3-dimension array. Arrays can have 1, 2, 3, ..., 6 dimensions.
Students sometimes become confused about the number of dimensions and the number of elements per dimension. Elements refer to the number of columns or rows or slices in an array. For example the two-dimension array of sales tax has three fields (3 column elements) and fifty records (50 row elements). In our examples we have been declaring this array with the following syntax: Dim StateData(0 to 2, 0 to 49). It is common for programmers to refer to the tax array as a "3 by 50" array to communicate succinctly the number of dimensions and elements per dimension.
For counter = start To end [Step step]
[statements]
[Exit For]
[statements]
Next [counter]
| Part | For ... Next statement syntax |
| counter | Required. Numeric variable used as a loop counter. The variable can't be a Boolean or an array element. |
| start | Required. Initial value of counter. |
| end | Required. Final value of counter. |
| step | Optional. Amount counter is changed each time through the loop. If not specified, step defaults to one. The step argument can be either positive or negative. The value of the step argument determines loop processing as follows: (1) Loop executes if counter <= end and Step Value is Positive or 0, and (2) Loop executes if Step Value is Negative and counter >= end"); |
| statements | Optional. One or more statements between For and Next that are executed the specified number of times. |
Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]
Loop
' Or, you can use this syntax:
Do
[statements]
[Exit Do]
[statements]
Loop [{While | Until} condition]
| Part | Do ... Loop statement syntax |
| condition | Optional. Numeric expression or string expression that is True or False. If condition is Null, condition is treated as False. |
| statements | One or more statements that are repeated while, or until, condition is True. |
While condition
[statements]
Wend
| Part | While ... Wend statement syntax |
| condition | Required. Numeric expression or string expression that evaluates to True or False. If condition is Null, condition is treated as False. |
| statements | Optional. One or more statements executed while condition is True. |
For Each element In group
[statements]
[Exit For]
[statements]
Next [element]
| Part | For ... Each Loop statement syntax |
| element | Required. Variable used to iterate through the elements of the collection or array. For collections, element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, element can only be a Variant variable. |
| group | Required. Name of an object collection or array (except an array of user-defined types). |
| statements | Optional. One or more statements that are executed on each item in group. |