Controlling Program Flow | Beginning Access 2007 VBA

So far, you have learned how to create one or more modules to contain your code as well as how to create procedures within those modules. Now you will see how to write commands within procedures that control the flow of the program. The flow of a program can be controlled through decision-making, loops, and in other ways. We’ll start by looking at writing decision-making code.

Decision Making

VBA has various statements you can use to make decisions in your code and then take an appropriate action depending on the result. The following gives you several decision-making examples to illustrate this concept.

If … Then

If...Then statements can be used to make decisions and perform certain actions depending on whether the conditions are met.

The syntax for an If...Then statement is:

  If CONDITION Then 'code if the condition is met End If

The syntax for an If…Then…Else statement is:

  If CONDITION Then 'code if the condition is met Else 'code if the condition is not met End If

The syntax for an If…Then…ElseIf statement is:

  If CONDITION Then 'code if the condition is met ElseIf CONDITION Then 'code if the ElseIf condition is met End If

ElseIf and Else can be used together as part of the same If...Then statement, or they can be used separately, as illustrated in the previous example. If…Then statements can also be nested within each other, as shown in the following example:

  If intCounter < 0 Then   'reset intCounter to 0   intCounter = 0 ElseIf intCounter > 0 and intCounter < 50 Then   If intCounter = 50 Then     Msgbox "The maximum number of sessions has been reached."   Else     Msgbox "There are still sessions remaining."   End If   intCounter = intCounter + 1 End If

Try It Out-Creating TestIfStatement Procedure

Now, it’s your turn to create a new procedure and makes uses of If…Then statements.

In the modBusinessLogic standard module, add the following TestIfStatement procedure:

  Sub TestIfStatement() 'declare variable to store sales tax value Dim curSalesTax As Currency  'call function to calculate sales tax curSalesTax = CalculateSalesTax(500, 0.05) 'evaluate sales tax and write proper message 'to debug window If curSalesTax <= 10 Then     Debug.Print "You are lucky - the amount of tax is nominal." ElseIf curSalesTax > 10 And curSalesTax <= 50 Then     Debug.Print "The amount of sales tax could have bought you a nice meal." Else     Debug.Print "You bought a really nice item for that tax amount." End If End Sub

From the Immediate Window, run the new TestIfStatement procedure. The result is displayed in Figure 2-27, in the Immediate Window.

image from book
Figure 2-27

How It Works

The TestIfStatement procedure calls the CalculateSalesTax function that you created previously and evaluates the result in an If...Then statement to determine which message to display in the Immediate Window. The procedure begins by declaring a variable to store the calculated sales tax value.

 Sub TestIfStatement() 'declare variable to store sales tax value Dim curSalesTax As Currency

The curSalesTax variable is assigned to the result of the CalculateSalesTax function. In other words, the CalculateSalesTax procedure is called with the hard-coded values, and the resulting value is placed in the curSalesTax variable.

  'call function to calculate sales tax  curSalesTax = CalculateSalesTax(500, 0.05)

An If... statement is then used to evaluate the curSalesTax value and write a particular message to the Immediate Window, depending on how the expression is evaluated.

  'evaluate sales tax and write proper message 'to debug window If curSalesTax <= 10 Then     Debug.Print "You are lucky - the amount of tax is nominal." ElseIf curSalesTax > 10 And curSalesTax <= 50 Then      Debug.Print "The amount of sales tax could have bought you a nice meal." Else      Debug.Print "You bought a really nice item for that tax amount." End If End Sub

In the current example, because a value of 500 is specified for the sales amount parameter of the CalculateSalesTax function and .05 is specified for the tax rate parameter, the resulting tax amount is 25. Thus, when you run the procedure, the Immediate Window displays the corresponding message.

IIf

The IIf function can be used to return one of two possible values depending on whether the condition being tested is true or false. The syntax for the IIf function is:

  IIf(expr, truepart, falsepart)

Here is an example:

  strResult = IIf(intWeight > 25, "Heavy", "Light")

If the intWeight value is greater than 25, then the IIF function will return the value "Heavy" and assign it to the strResult variable. Otherwise, the strResult variable will be assigned to the value "Light".

Conditional If

Conditional If…Then statements enable you to selectively compile and execute certain blocks of code. Conditional If statements can be used in various scenarios, such as when you want certain blocks of code to execute during testing but not in the release version, or when you’re distributing your application in different regions and want certain code to apply in some regions but not others. The following is an example of the general syntax:

  #If conLanguage = "English" Then   'The code specific to the English version of the software goes here. #ElseIf conLanguage = "Spanish" Then   'The code specific to the Spanish version of the software goes here. #Else   'The code specific to the remaining versions of the software goes here. #End If

Select…Case

Another way to implement decision making in your VBA code is to use a Select...Case statement. Select…Case statements can be used to easily evaluate the same variable multiple times and then take a particular action depending on the evaluation.

The syntax for a Select…Case statement is:

  Select Case VARIABLE Case VALUE1   'code to run if VARIABLE equals Value1 Case VALUE2   'code to run if VARIABLE equals Value2 Case Else   'code to run for remaining cases End Select

Try It Out-Create the TestCaseStatement Procedure

Let’s create a new procedure that makes use of a Select...Case statement to illustrate this in further detail.

Add the following TestCaseStatement procedure to the modBusinessLogic standard module.

  Sub TestCaseStatement(strCountry As String) 'evaluate the value of strCountry and display applicable result in debug window Select Case strCountry Case "Italy"     Debug.Print "The buildings dating back to 400 BC are incredible." Case "China"     Debug.Print "Great bargains for shoppers." Case "Russia"     Debug.Print "A beautiful country with a growing democracy." Case "Germany"     Debug.Print "Fantastic food - you will not starve there." Case Else     Debug.Print "You should travel more when you have the chance." End Select End Sub

Run the TestCaseStatement procedure from the Immediate Window and specify "Italy" as the parameter, as shown in Figure 2-28. Click Enter to run the procedure. The resulting value is then displayed in the Immediate Window.

Figure 2-28

How It Works

The TestCaseStatement procedure accepts a string variable called strCountry as a parameter.

  Sub TestCaseStatement(strCountry As String)

The strCountry variable is evaluated in the Select...Case statement to determine which one of a variety of messages should be displayed.

  'evaluate the value of strCountry and display applicable result in debug window Select Case strCountry Case "Italy"     Debug.Print "The buildings dating back to 400 BC are incredible." Case "China"     Debug.Print "Great bargains for shoppers." Case "Russia"     Debug.Print "A beautiful country with a growing democracy." Case "Germany"     Debug.Print "Fantastic food - you will not starve there." Case Else       Debug.Print "You should travel more when you have the chance." End Select

Because you ran the procedure using "Italy" as the value for Country, the message for Italy was displayed in the Immediate Window. Try running the procedure with different values for Country and see how the results differ.

Loops

Various types of loops can be used to iterate through a particular action until a particular scenario occurs. For example, loops can be used to run particular code a specified number of times. Now you will learn about a few different ways to declare loops using VBA code.

For … Next and For Each … Next

For…Next loops can be used to run the same code a particular number of times. For Each...Next loops can be used to run the same code for each object in a particular collection, such as for each form in the Forms collection. The basic syntax for a For…Next loop is shown here.

  For counter = start To end   'statements go here Next [counter]

The basic syntax for a For Each...Next loop is shown here.

  For Each element In Collection   'statements go here Next [element]

Try It Out-Creating a For … Next Loop

Let’s jump right in by creating our own For…Next loop to see how this works.

Place this code for the TestLoop procedure in the modBusinessLogic standard module.

  Sub TestLoop() 'declare variable to store Counter Dim intCounter As Integer 'increment intCounter from 1 to 5 and 'display output in debug window For intCounter = 1 To 5     Debug.Print intCounter Next intCounter End Sub

Run the TestLoop procedure from the Immediate Window. The results of running the procedure are shown in Figure 2-29.

image from book
Figure 2-29

How It Works

The TestLoop procedure begins by declaring an intCounter variable to store the number of times the loop has been iterated.

 Sub TestLoop() 'declare variable to store Counter Dim intcounter As Integer

The For...Next statement comes next, with code specifying that the loop should run with intCounter starting at 1 and repeat multiple times until intCounter reaches 5. The Next statement increments the intCounter value by one. When intCounter reaches 5, the Debug.Print statement will execute for the last time and then the loop is exited.

  'increment intCounter from 1 to 5 and 'display output in debug window For intCounter = 1 To 5     Debug.Print intCounter     Next intCounter

Do … Loop

The Do…Loop statement can be used instead of For...Next to accomplish the same purpose. The two types of Do…Loops are Do…While and Do...Until. Do…While may never run any statements if the condition is not initially true, while Do…Until will always run at least once.

The generic syntax for Do…Loop is shown here.

  Do [{While | Until} condition]   'statements go here Loop

Or, you can use this syntax:

  Do   'statements go here Loop [{While | Until} condition]

The following code uses a Do…While statement to accomplish the same result as the For…Next loop described previously.

  Do While intCounter <= 5   Debug.Print intCounter intCounter = intCounter + 1 Loop

An example of a Do Until loop is shown here.

  Do Until intCounter = 6   Debug.Print intCounter   intCounter = intCounter + 1 Loop

Tip

If the condition in a Do Until statement is never met, then the loop is known as an infinite loop, which will execute indefinitely.

While … Wend

The While…Wend statement executes repeatedly while a certain condition is met. When the condition is no longer met, the loop terminates. Here is an example:

  intCounter = 1 While intCounter <= 5    Debug.Print intCounter intCounter = intCounter + 1 Wend