VBA provides several different constructs for looping and decision processing. The sections that follow cover the most commonly used constructs. You can find the examples in the form called frmControlStructures at www.samspublishing.com.
The If...Then...Else construct evaluates whether a condition is true. In the following example, anything between If and Else occurs if the statement evaluates to True, and any code between Else and End If execute if the statement evaluates to False:
Private Sub cmdIfThenElse_Click() If IsNull(Me.txtName.Value) or IsNull(Me.txtAge.Value) Then MsgBox "Name or Age is Blank" Else MsgBox "Your Name Is " & Me.txtName.Value _ & " And Your Age Is " & Me.txtAge.Value End If End Sub
The Else in this code is optional. This code tests whether the text boxes called txtName and txtAge contain a Null (absence of a value). The code displays a different message, depending on whether one of the text boxes contains a Null value.
VBA also permits one-line If statements; they look like this:
If IsNull(Me.txtvalue.Value) Then MsgBox "You must Enter a Value"
However, I don't recommend this format for an If statement because it reduces read ability.
Another useful form of an If statement is ElseIf, which enables you to evaluate an unlimited number of conditions in one If statement. The following code is an example (not included in Chap23Ex.mdb):
Sub MultipleIfs(intNumber As Integer) If intNumber = 1 Then MsgBox "You entered a One" ElseIf intNumber = 2 Then MsgBox "You entered a Two" ElseIf intNumber >= 3 And intNumber <= 10 Then MsgBox "You entered a Number Between 3 and 10" Else MsgBox "You Entered Some Other Number" End If End Sub
The compiler evaluates the conditions in an If statement in the order in which they appear. For this reason, it's best to place the most common conditions first. After a condition is met, execution continues immediately after the End If. If no conditions are met and there's no Else statement, execution also continues immediately after the End If.
If multiple conditions exist, it's almost always preferable to use a Select Case statement, described later in this hour, rather than an If statement. Case statements generally make code easier to read and maintain than If...Then...Else statements.
An Immediate If (IIf) is a variation of an If statement. It's actually a built-in function that returns one of two values, depending on whether the condition you are testing for is true or false. Here's an example (not included in Chap23Ex.mdb):
Function EvalSales(curSales As Currency) As String EvalSales = IIf(curSales >= 100000, "Great Job", "Keep Plugging") End Function
This function evaluates the curSales parameter to see whether its value is greater than or equal to $100,000. If the value is greater than or equal to $100,000, the code returns the string "Great Job" from the function; otherwise, the code returns the string "Keep Plugging".
The code evaluates both the true and false portions of the IIf, so if there's a problem with either part of the expression (for example, a divide-by-zero condition), an error occurs.
You most often use the IIf function in a calculated control on a form or report, or to create a new field in a query. Probably the most common example is an IIf expression that determines whether the value of a control is Null. If it is, you can have the expression return a zero or an empty string; otherwise, you can have the expression return the value in the control. The following expression, for example, evaluates the value of a control on a form:
=IIf(IsNull(Forms!frmOrders.txtFreight.Value),0, _ Forms!frmOrders.txtFreight.Value)
This expression displays either a zero or the value for freight in the control called txtFreight.
Although you can use the IIf function to handle Null values, the built-in Nz function is a more efficient solution to this problem and avoids the inherent pitfalls of IIf. The section that follows shows you how to use the Nz function.
The IIf function executes slowly. It is best to avoid using it whenever possible.
Rather than use multiple If...Then...Else statements, it's often much clearer to use a Select Case statement, as shown here and found under the Select Case command button of the frmControlStructures form:
Private Sub cmdCase_Click() Dim intAge As Integer intAge = Nz(Me.txtAge.Value, 0) Select Case intAge Case 0 MsgBox "You Must Enter a Number" Case 1 to 18 MsgBox "You Are Just a Kid" Case 19, 20, 21 MsgBox "You are Almost an Adult" Case 22 to 40 MsgBox "Good Deal" Case Is > 40 MsgBox "Getting Up There!" Case Else MsgBox "You Entered an Invalid Number" End Select End Sub
This subroutine first uses the Nz function to convert a Null or empty value in the txtAge control to 0; otherwise, the code stores the value in txtAge in the intAge variable. The Select Case statement then evaluates intAge. If the value is 0, the code displays a message box that says You Must Enter a Number. If the value is between 1 and 18 inclusive, the code displays a message box that says You Are Just a Kid. If the user enters 19, 20, or 21, the code displays the message You are Almost an Adult. If the user enters a value between 22 and 40 inclusive, the code displays the message Good Deal. If the user enters a value greater than 40, the code displays a message Getting Up There!. If the user enters any other number, he or she gets a message indicating that it is an invalid number.
Several looping structures are available in VBA; this section discusses most of them. Take a look at the following example of a looping structure (found under the Do While...Loop command button of the frmControlStructures form):
Sub cmdDoWhileLoop_Click() Do While Nz(Me.txtAge.Value)< 35 Me.txtAge.Value = Nz(Me.txtAge.Value) + 1 Loop End Sub
In this structure, if the value in the txtAge text box is greater than or equal to 35, the code in the loop is not executed. If you want the code to execute unconditionally at least one time, you need to use the following construct (found under the Do...Loop While command button of the frmControlStructures form):
Sub cmdDoLoopWhile_Click() Do Me.txtAge = Nz(Me.txtAge.Value) + 1 Loop While Nz(Me.txtAge.Value) < 35 End Sub
This code executes one time, even if the value in the txtAge text box is set to 35. Do While...Loop in the previous example evaluates before the code executes, so it doesn't ensure code execution. The code evaluates Do...Loop While at the end of the loop and it therefore guarantees execution.
Alternatives to Do While...Loop and the Do...Loop While are Do Until...Loop and Do...Loop Until. Do Until...Loop (found under the Do Until...Loop command button of the frmControlStructures form) works like this:
Sub cmdDoUntil_Click() Do Until Nz(Me.txtAge.Value) = 35 Me.txtAge.Value = Nz(Me.txtAge.Value) + 1 Loop End Sub
This loop continues to execute until the value in the txtAge text box becomes equal to 35. The Do...Loop Until construct (found under the Do...Loop Until command button of the frmControlStructures form) is another variation:
Sub cmdLoopUntil_Click() Do Me.txtAge.Value = Nz(Me.txtAge.Value) + 1 Loop Until Nz(Me.txtAge.Value) = 35 End Sub
As with the Do...Loop While construct, the Do...Loop Until construct doesn't evaluate the condition until the end of the loop, so the code in the loop is guaranteed to execute at least once.
It is not a good idea to reference a control over and over again in a loop. Notice that the code in the looping examples references the txtAge control each time through the loop. I did this to keep the examples simple. To eliminate the performance problem associated with this technique, you should use the code that follows (found under the cmdEfficient command button on the frmControlStructures form):
Private Sub cmdEfficient_Click() Dim intCounter As Integer intCounter = Nz(Me.txtAge.Value) Do While intCounter < 35 intCounter = intCounter + 1 Loop Me.txtAge.Value = intCounter End Sub
With any of the looping constructs, it's easy to unintentionally cause a loop to execute endlessly. This can also be illustrated with the code samples shown previously and the following example, which shows an endless loop (not included in Chap23Ex.mdb):
Sub EndlessLoop() Dim intCounter As Integer intCounter = 5 Do Debug.Print intCounter intCounter = intCounter + 1 Loop Until intCounter = 5 End Sub
This code snippet sets intCounter equal to 5. The code in the loop increments intCounter and then tests to see whether intCounter equals 5. If it doesn't, the code in the loop executes another time. Because intCounter will never become equal to 5 (it starts at 6 within the Do loop), the loop executes endlessly. You need to use Ctrl+Break to exit the loop; however, Ctrl+Break doesn't work in Access's runtime version. The user must instead use the Task Manager to end the task.
You use the For...Next construct when you have an exact number of iterations you want to perform. It looks like this and is found under the For...Next command button of the frmControlStructures form:
Sub cmdForNext_Click() Dim intCounter As Integer For intCounter = 1 To 5 Me.txtAge.Value = Nz(Me.txtAge.Value) + 1 Next intCounter End Sub
Note that intCounter is self-incrementing. The start value and the stop value can both be variables. You can give a For...Next construct a step value, as shown in the following example, in which the code increments the counter by the value of Step each time it processes the loop:
Sub ForNextStep() ' Note that this code is not in database Chap23Ex.mdb Dim intCounter As Integer For intCounter = 1 To 5 Step 2 Me.txtAge.Value = Nz(Me.txtAge.Value) + 1 Next intCounter End Sub
The With...End With statement executes a series of statements on a single object or user-defined type. Here's an example (found under the With...End With command button of the frmControlStructures form):
Private Sub cmdWithEndWith_Click() With Me.txtAge .BackColor = 16777088 .ForeColor = 16711680 .Value = "40" .FontName = "Arial" End With End Sub
This code performs four operations on the txtAge text box, found on the form it's run on. The code modifies the BackColor, ForeColor, Value, and FontName properties of the txtAge text box.
The With...End With statement offers two main benefits. The first is simply less typing: You don't need to repeat the object name for each action you want to perform on the object. The more important benefit involves performance. Because code refers to the object once rather than multiple times, this code runs much more efficiently than code without the With...End With construct. The benefits are even more pronounced when the With...End With construct is found in a loop.