[ Team LiB ] |
Using the VBA Control StructuresVBA 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. If...Then...ElseThe 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.
Immediate If (IIf)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".
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.
Select CaseRather 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. LoopingSeveral 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. For...NextYou 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 With...End WithThe 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. |
[ Team LiB ] |