Home

Conditional Selections

 

The Select...Case Statement

 

Introduction

If you have a large number of conditions to examine, the If...Then...Else statement will go through each one of them. The Visual Basic language offers the alternative of jumping to the statement that applies to the state of a condition. This is referred to as a select case condition and it uses the keywords Select and Case.

The formula of the Select Case statement is:

Select Case Expression
    Case Expression1
	Statement1
    Case Expression2
        Statement2
    Case Expression_X
        Statement_X
End Select

The statement starts with Select Case and ends with End Select. On the right side of Select Case, enter a value, the Expression factor, that will be used as a tag. The value of Expression can be a Boolean value (a Boolean type), a character or a string (a String type), a natural number (a Byte, an Integer, or a Long type), a decimal number (a Single or a Double type), a date or time value (a Date type), an enumeration (an Enum type), or else (a Variant type).

Inside the Select Case and the End Select lines, you provide one or more sections that each contains a Case keyword followed by a value. The value on the right side of a Case, Expresion1, Expresion2, or Expresion_X, must be the same type as the value of Expression or it can be implied from it. After the case and its expression, you can write a statement.

When this section of code is accessed, the value of Expression is considered. Then the value of Expression is compared to each Expression_X of each case:

  1. If the value of Expression1 is equal to that of Expression, then Statement1 is executed.
    If the value of Expression1 is not equal to that of Expression, then the interpreter moves to Expression2
  2. If the value of Expression2 is equal to that of Expression, then Statement2 is executed
  3. This will continue down to the last Expression_X

Here is an example:

Sub Exercise
    Dim Answer As Byte

    Answer = CByte(InputBox( _
                  "One of the following is not a Visual Basic keyword" & vbCrLf & _
                     "1) Function" & vbCrLf & _
                     "2) Except" & vbCrLf & _
                     "3) ByRef" & vbCrLf & _
                     "4) Each" & vbCrLf & vbCrLf & _
                     "Your Answer? "))

    Select Case Answer
        Case 1
            MsgBox("Wrong: Function is a Visual Basic keyword." & vbCrLf & _
                   "It is used to create a procedure of a function type")
        Case 2
            MsgBox("Correct: Except is not a keyword in " & vbCrLf & _
                   "Visual Basic but __except is a C++ " & vbCrLf & _
                   "keyword used in Exception Handling")
        Case 3
            MsgBox("Wrong: ByRef is a Visual Basic keyword used " & vbCrLf & _
                   "to pass an argument by reference to a procedure")
        Case 4
            MsgBox("Wrong: The ""Each"" keyword is used in " & vbCrLf & _
                   "Visual Basic in a type of looping " & vbCrLf & _
                   "used to ""scan"" a list of item.")
    End Select
End Sub

Here is an example of running the program:

Select

Select

Practical Learning: Introducing Select Cases

  1. Start Microsoft Excel
  2. From the resources that accompany these lessons, open the gdcs1 (or gdcs2) workbook you 
  3. Change the names of the first and the second worksheets to Employees and Customers respectively
  4. Add a few records in the Employees worksheet
     
    Georgetown Dry Cleaning Services
  5. Add a few records in the Customers worksheet
     
    Georgetown Dry Cleaning Services
  6. Save the workbook
  7. To open Microsoft Visual Basic, on the Ribbon, click Developer and, in the Code section, click Visual Basic:
     
    Georgetown Dry Cleaning Services
  8. Right-click the Time Left text box (the text box on the right side of Time Left) and click View Code
  9. In the Objects combo box, make sure txtTimeLeft is selected.
    In the Procedure combo box, select Exit and implement the event as follows:
     
    Private Sub txtTimeLeft_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Dim DateLeft As Date, TimeLeft As Date
        Dim DateExpected As Date, TimeExpected As Date
        
        If IsDate(txtTimeLeft) Then
            TimeLeft = CDate(txtTimeLeft)
        Else
            MsgBox "The value you entered is not a valid time"
            txtTimeLeft = Time
        End If
    End Sub

What Case Else?

The above code supposes that one of the cases will match the value of the Expression factor. This is not always so. If you anticipate that there could be no match between the Expression and one of the Expressions, you can use a Case Else statement at the end of the list. The statement would then look like this:

Select Case Expression
    Case Expression1
        Statement1
    Case Expression2
        Statement2
    Case Expressionk
        Statementk
    Case Else
        Statementk
End Select

In this case, the statement after the Case Else will execute if none of the previous expressions matches the Expression factor. Here is an example:

Sub Exercise
    Dim Answer As Byte

    Answer = CByte(InputBox( _
                   "One of the following is not a Visual Basic keyword" & vbCrLf & _
                   "1) Function" & vbCrLf & _
                   "2) Except" & vbCrLf & _
                   "3) ByRef" & vbCrLf & _
                   "4) Each" & vbCrLf & vbCrLf & _
                   "Your Answer? "))

    Select Case Answer
        Case 1
            MsgBox("Wrong: Function is a Visual Basic keyword." & vbCrLf & _
                   "It is used to create a procedure of a function type")
        Case 2
            MsgBox("Correct: Except is not a keyword in " & vbCrLf & _
                   "Visual Basic but __except is a C++ " & vbCrLf & _
                   "keyword used in Exception Handling")
        Case 3
            MsgBox("Wrong: ByRef is a Visual Basic keyword used " & vbCrLf & _
                   "to pass an argument by reference to a procedure")
        Case 4
            MsgBox("Wrong: The ""Each"" keyword is used in " & vbCrLf & _
                   "Visual Basic in a type of looping " & vbCrLf & _
                   "used to ""scan"" a list of item.")
        Case Else
            MsgBox("Invalid Selection")
    End Select
End Sub

Here is an example of running the program:

Select

Select

Practical Learning: Using Select Case

  1. In the Objects combo box, select txtEmployeeNumber
  2. In the Procedure combo box, select Exit
  3. Implement the event as follows: 
     
    Private Sub txtEmployeeNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Dim EmployeeNumber As Long
        Dim EmployeeName As String
    
        EmployeeNumber = CLng(txtEmployeeNumber)
        
        Select Case EmployeeNumber
            Case 22804
                EmployeeName = "Helene Mukoko"
            Case 92746
                EmployeeName = "Raymond Kouma"
            Case 54080
                EmployeeName = "Henry Larson"
            Case 86285
                EmployeeName = "Gertrude Monay"
            Case 20860
                EmployeeName = "Paul Bertrand Yamaguchi"
            Case Else
                EmployeeName = "Unidentified Employee"
        End Select
        
        txtEmployeeName = EmployeeName
    End Sub
  4. On the Standard toolbar, click the Run Sub/UserForm button Run Sub/UserForm
  5. In the Employee # of the form, enter one of the numbers such as 54080 and press Tab
  6. Close the form and return to Microsoft Visual Basic

Combining Cases

As mentioned in our introduction, the Select Case can use a value other than an integer. For example you can use a character:

Sub Exercise
    Dim Gender As String

    Gender = "M"

    Select Case Gender
        Case "F"
            MsgBox("Female")
        Case "M"
            MsgBox("Male")
        Case Else
            MsgBox("Unknown")
    End Select

        Return 0
    End Function

End Sub

This would produce:

Select

Notice that in this case we are using only upper case characters. If want to validate lower case characters also, we may have to create additional case sections for each. Here is an example:

Sub Exercise
    Dim Gender As String

    Gender = "f"

    Select Case Gender
        Case "f"
            MsgBox("Female")
        Case "F"
            MsgBox("Female")
        Case "m"
            MsgBox("Male")
        Case "M"
            MsgBox("Male")
        Case Else
            MsgBox("Unknown")
    End Select
End Sub

This would produce:

Select

Instead of using one value for a case, you can apply more than one. To do this, on the right side of the Case keyword, you can separate the expressions with commas. Here are examples:

Sub Exercise
    Dim Gender As String

    Gender = "F"

    Select Case Gender
        Case "f", "F"
            MsgBox("Female")
        Case "m", "M"
            MsgBox("Male")
        Case Else
            MsgBox("Unknown")
    End Select
End Sub

Validating a Range of Cases

You can use a range of values for a case. To do this, on the right side of Case, enter the lower value, followed by To, followed by the higher value. Here is an example:

Sub Exercise
    Dim Age As Integer
    Age = 24

    Select Case Age
        Case 0 To 17
            MsgBox("Teen")
        Case 18 To 55
            MsgBox("Adult")
        Case Else
            MsgBox("Senior")
    End Select
End Sub

This would produce:

Select

Checking Whether a Value IS

Consider the following procedure:

Sub Exercise
    Dim Number As Short

    Number = 448

    Select Case Number
        Case -602
            MsgBox("-602")
        Case 24
            MsgBox("24")
        Case 0
            MsgBox("0")
    End Select
End Sub

Obviously this Select Case statement will work in rare cases only when the expression of a case exactly match the value sought for. In reality, for this type of scenario, you could validate a range of values. The Visual Basic language provides an alternative. You can check whether the value of the Expression responds to a criterion instead of an exact value. To create it, you use the Is operator with the following formula:

Is Operator Value

You start with the Is keyword. It is followed by one of the Boolean operators we saw in the previous lessons: =, <>, <, <=, >, or >=. On the right side of the Boolean operator, type the desired value. Here are examples:

Sub Exercise
    Dim Number As Integer

    Number = -448

    Select Case Number
        Case Is < 0
            MsgBox("The number is negative")
        Case Is > 0
            MsgBox("The number is positive")
        Case Else
            MsgBox("0")
    End Select
End Sub

Although we used a natural number here, you can use any appropriate logical comparison that can produce a True or a False result. You can also combine it with the other alternatives we saw previously, such as separating the expressions of a case with commas.

Select...Case and the Conditional Built-In Functions

With the Select...Case statement, we saw how to check different values against a central one and take action when one of those matches the tag. Here is an example:

Sub Exercise
    Dim Number As Integer, MembershipType As String

    Number = 2

    Select Case Number
        Case 1
            MembershipType = "Teen"
        Case 2
            MembershipType = "Adult"
        Case Else
            MembershipType = "Senior"
    End Select

    MsgBox("Membership Type: " & MembershipType)
End Sub

This would produce:

Select

We also saw that the Visual Basic language provides the Choose() function that can check a condition and take an action. The Choose() function is another alternative to a Select...Case statement. Once again, consider the syntax of the Choose function:

Function Choose( _
   ByVal Index As Double, _ 
   ByVal ParamArray Choice() As Variant _
) As Object

This function takes two required arguments. The first argument is equivalent to the Expression of our Select Case formula. As mentioned already, the first argument must be a number. This is the central value against which the other values will be compared. Instead of using Case sections, provide the equivalent ExpressionX values as a list of values in place of the second argument. The values are separated by commas. Here is an example:

Choose(Number, "Teen", "Adult", "Senior")

As mentioned already, the values of the second argument are provided as a list. Each member of the list uses an index. The first member of the list, which is the second argument of this function, has an index of 1. The second value of the argument, which is the third argument of the function, has an index of 2. You can continue adding the values of the second argument as you see fit.

When the Choose() function has been called, it returns a value of type Variant. You can retrieve that value, store it in a variable and use it as you see fit. Here is an example:

Sub Exercise
    Dim Number As Integer, MembershipType As String

    Number = 1

    MembershipType = Choose(Number, "Teen", "Adult", "Senior")

    MsgBox("Membership Type: " & MembershipType)
End Sub

This would produce:

Select

 
 

Managing Conditional Statements

 

Conditional Nesting

So far, we have learned to create normal conditional statements and loops. Here is an example:

Sub Exercise
    Dim Number%

    Rem Request a number from the user
    Number% = InputBox("Enter a number that is lower than 5")

    Rem Find if the number is positive or 0
    If Number% >= 0 Then
        Rem If the number is positive, display it
        MsgBox (Number%)
    End If 
End Sub

When this procedure executes, the user is asked to provide a number. If that number is positive, a message box displays it. If the user enters a negative number, nothing happens. In a typical program, after validating a condition, you may want to take action. To do that, you can create a section of program inside the validating conditional statement. In fact, you can create a conditional statement inside of another conditional statement. This is referred to as nesting a condition. Any condition can be nested inside of another and multiple conditions can be included inside of another.

Here is an example where an If...Then condition is nested inside of another If...Then statement:

Sub Exercise
    Dim Number%

    Rem Request a number from the user
    Number% = InputBox("Enter a number that is lower than 5")

    Rem Find if the number is positive or 0
    If Number% >= 0 Then
        Rem If the number is positive, accept it
        If Number% < 12 Then
            MsgBox (Number%)
        End If
    End If
End Sub

Practical LearningPractical Learning: Nesting Conditions

  1. Change the code of the Exit event of the txtEmployeeNumber as follows:
     
    Private Sub txtEmployeeNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Dim EmployeeNumber As Long
        Dim EmployeeName As String
    
        EmployeeNumber = 0
        
        If IsNumeric(txtEmployeeNumber) Then
            EmployeeNumber = CLng(txtEmployeeNumber)
            
            Select Case EmployeeNumber
                Case 22804
                    EmployeeName = "Helene Mukoko"
                Case 92746
                    EmployeeName = "Raymond Kouma"
                Case 54080
                    EmployeeName = "Henry Larson"
                Case 86285
                    EmployeeName = "Gertrude Monay"
                Case 20860
                    EmployeeName = "Paul Bertrand Yamaguchi"
                Case Else
                    EmployeeName = ""
            End Select
        
            txtEmployeeName = EmployeeName
        Else
            txtEmployeeNumber = ""
            txtEmployeeName = ""
            MsgBox "You must enter the employee number of " & _
                   "the staff member who is processing this cleaning order", _
                   vbOKOnly Or vbInformation, _
                   "Georgetown Dry Cleaning Services"
        End If
    End Sub
  2. Press F5 to test the form
  3. Enter the employee number as 92746, and press Tab
  4. Close the form and return to Microsoft Visual Basic

The Goto Statement

The Goto statement allows a program execution to jump to another section of a procedure in which it is being used. In order to use the Goto statement, insert a name on a particular section of your procedure so you can refer to that name. The name, also called a label, is made of one word and follows the rules we have applied to names (the name can be anything), then followed by a colon ":". Here is an example:

Sub Exercise()

    ' Do some thing(s) here

SomeLabelHere:
    ' Do some other thing(s) here
End Sub

After creating the label, you can process it. In the code before the label, you can do something. In that section, if a condition happens that calls for jumping to the label, then use a GoTo statement to send the flow to the corresponding label by typing the name of the label on the right side of GoTo. Here is an example:

Sub Exercise
    Dim Number%

    Rem Request a number from the user
    Number% = InputBox("Enter a number that is lower than 5")

    Rem Find if the number is positive or 0
    If Number% < 0 Then
        GoTo NegativeNumber
    Else
        Rem If the number is positive, display it
        MsgBox (Number%)
    End If
    
NegativeNumber:
    MsgBox "You entered a negative number"
End Sub

In the same way, you can create as many labels as you judge them necessary in your code and refer to them when you want. The name must be unique in its scope. This means that each label must have a unique name in the same procedure. Here is an example with various labels:

Sub Exercise
    Dim Answer As Byte

    Answer = InputBox(" -=- Multiple Choice Question -=-" & vbCrLf & _
                      "To create a constant in your code, " & _
                      "you can use the Constant keyword" & vbCrLf & _
                      "Your choice (1=True/2=False)? ")


    If Answer = 1 Then GoTo Wrong
    If Answer = 2 Then GoTo Right

Wrong:
    MsgBox("Wrong: The keyword used to create a constant is Const")
    GoTo Leaving

Right:  MsgBox("Right: Constant is not a keyword")

Leaving:

End Sub

Here is an example of executing the program with Answer = 1:

Go To

Go To

Here is another example of executing the same program with Answer = 2:

Go To

Go To

Negating a Conditional Statement

So far, we have learned to write a conditional statement that is true or false. You can reverse the true (or false) value of a condition by making it false (or true). To support this operation, the Visual Basic language provides an operator called Not. Its formula is:

Not Expression

When writing the statement, type Not followed by a logical expression. The expression can be a simple Boolean expression. Here is an example:

Sub Exercise
    Dim IsMarried As Boolean

    MsgBox("Is Married: " & IsMarried)
    MsgBox("Is Married: " & Not IsMarried)
End Sub

This would produce:

Not

Not

In this case, the Not operator is used to change the logical value of the variable. When a Boolean variable has been "notted", its logical value has changed. If the logical value was True, it would be changed to False and vice versa. Therefore, you can inverse the logical value of a Boolean variable by "notting" or not "notting" it.

Now consider the following program we saw in Lesson 11:

Sub Exercise
    Dim IsMarried As Boolean
    Dim TaxRate As Double

    TaxRate = 33.0

    MsgBox("Tax Rate: " & TaxRate & "%")

    IsMarried = True
    If IsMarried = True Then
        TaxRate = 30.65

        MsgBox("Tax Rate: " & TaxRate & "%")
    End If
End Sub

This would produce:

If Condition If Condition

Probably the most classic way of using the Not operator consists of reversing a logical expression. To do this, you precede the logical expression with the Not operator. Here is an example:

Sub Exercise
    Dim IsMarried As Boolean
    Dim TaxRate As Double

    TaxRate = 33.0
    MsgBox("Tax Rate: " & TaxRate & "%")

    IsMarried = True

    If Not IsMarried Then
        TaxRate = 30.65
        MsgBox("Tax Rate: " & TaxRate & "%")
    End If
End Sub

This would produce:

If Condition

In the same way, you can negate any logical expression.

Practical LearningPractical Learning: Negating a Condition

  1. In the Object combo box, select txtDateLeft
  2. In the Procedure combo box, select Exit
  3. Implement the event as follows:
     
    Private Sub txtDateLeft_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        If Not IsDate(txtDateLeft) Then
            MsgBox "The value you entered is not a valid date"
            txtDateLeft = Date
        End If
    End Sub
  4. Press F5 to test the form
  5. Enter the employee number as 92746, and press Tab
  6. Close the form and return to Microsoft Visual Basic

Loop Repeaters

 

Introduction

A loop is a technique used to repeat an action. The Visual Basic language presents many variations of loops. They combine the Do and the Loop keywords.

The Do...Loop While Loop

A typical loop can be used to perform an action while a condition is maintained true. To support this type of loop, the Visual Basic language provides the Do...Loop While statement.

The formula of the Do... Loop While loop is:

Do
    Statement(s)
Loop While Condition

Do...Loop

This interpreter first executes the Statement or Statements. After executing the Statement(s) section, the interpreter checks the Condition. If the Condition is true, then the interpreter returns to the Statement(s) and execute(s) it(them). The interpreter keeps doing this check-execution gymnastic. As long as the Condition is true, the Statement(s) section will be executed and the Condition will be tested again. If the Condition is false or once the condition becomes false, the statement will not be executed and the program will move on. Here is an example:

Sub Exercise
    Dim Answer As String

    Do
        Answer = InputBox("Are we there yet (1=Yes/0=No)? ")
    Loop While Answer <> "1"

    MsgBox("Wonderful, we have arrived")
End Sub

Here is an example of running the program:

Do...Loop While

Do...Loop While

Do...Loop While

Do...Loop While

Do...Loop While

As you may guess already, the Condition must provide a way for it to be true or to be false. Otherwise, the looping would be executed continually.

Practical Learning: Using a Do...Loop While

  1. In the Objects combo box, select txtCustomerPhone
  2. In the Procedure combo box, select Exit
  3. Implement the event as follows: 
     
    Private Sub txtCustomerPhone_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        ' This variable will be used to check the cells based on a row
        Dim RowIndex As Integer
        ' This variable holds the customer phone number from the form
        Dim CustomerPhoneFromForm As String
        ' This variable holds the customer phone number from the worksheet
        Dim CustomerPhoneFromWorksheet As String
        
        Dim CustomerName As String
        
        ' Get the customer phone from the form
        CustomerPhoneFromForm = txtCustomerPhone
        
        ' Trim the left
        CustomerPhoneFromForm = LTrim(txtCustomerPhone)
        ' Trim the right side
        CustomerPhoneFromForm = RTrim(CustomerPhoneFromForm)
        ' Replace all spaces (in the middle of the number
        CustomerPhoneFromForm = Replace(CustomerPhoneFromForm, " ", "")
        ' Replace the left parentheses, if any
        CustomerPhoneFromForm = Replace(CustomerPhoneFromForm, "(", "")
        ' Replace the right parentheses, if any
        CustomerPhoneFromForm = Replace(CustomerPhoneFromForm, ")", "")
        ' Replace the dash -, if any
        CustomerPhoneFromForm = Replace(CustomerPhoneFromForm, "-", "")
        
        ' The phone number records start on row 6
        RowIndex = 6
    
        Do
    	' Get the customer phone from the second column
            CustomerPhoneFromWorksheet = _
    		Worksheets("Customers").Cells(RowIndex, 2).Value
    
            CustomerName = (Worksheets("Customers").Cells(RowIndex, 3).Value)
                
            ' Trim the left
            CustomerPhoneFromWorksheet = LTrim(CustomerPhoneFromWorksheet)
            ' Trim the right side
            CustomerPhoneFromWorksheet = RTrim(CustomerPhoneFromWorksheet)
            ' Replace all spaces (in the middle of the number
            CustomerPhoneFromWorksheet = _
    		Replace(CustomerPhoneFromWorksheet, " ", "")
            ' Replace the left parentheses, if any
            CustomerPhoneFromWorksheet = _
    		Replace(CustomerPhoneFromWorksheet, "(", "")
            
            ' Replace the right parentheses, if any
            CustomerPhoneFromWorksheet = _
    		Replace(CustomerPhoneFromWorksheet, ")", "")
            ' Replace the dash -, if any
            CustomerPhoneFromWorksheet = _
    		Replace(CustomerPhoneFromWorksheet, "-", "")
    
            If CustomerPhoneFromWorksheet = CustomerPhoneFromForm Then
                txtCustomerName = CustomerName
            End If
    
    	' Move to (continue with) the next row
            RowIndex = RowIndex + 1
    
        Loop While RowIndex <= 100
    End Sub
  4. On the Standard toolbar, click the Run Sub/UserForm button Run Sub/UserForm
  5. In the Employee # of the form, enter one of the numbers such as 54080 and press Tab
  6. In the Customer Phone text box, enter one of the phone numbers and press Tab
  7. Close the form and return to Microsoft Visual Basic

The Do...Loop Until Statement

While still supporting the ability to perform an action while a condition is true, the Visual Basic language provides an alternative to the Do... Loop While we saw earlier. The other solution uses the following formula:

Do
    Statement(s)
Loop Until Condition

Once again, the Statement(s) section executes first. After executing the Statement(s), the interpreter checks the Condition. If the Condition is true, the interpreter returns to the Statement(s) section to execute it. This will continue until the Condition becomes false. Once the Condition becomes false, the interpreter gets out of this loop and continues with the section under the Loop Until line.

Here is an example:

Sub Exercise
    Dim Answer As String

    Do
        Answer = InputBox("Are we there yet (1=Yes/0=No)? ")
    Loop Until Answer = "1"

    MsgBox("Wonderful, we have arrived")
End Sub

The Do While... Loop Statement

As mentioned above, the Do While... Loop expression executes a statement first before checking a condition that would allow it to repeat. If you want to check a condition first before executing a statement, you can use another version as Do While... Loop. Its formula is:

Do While Condition
  Statement(s)
Loop

In this case, the interpreter checks the Condition first. If the Condition is true, the interpreter then executes the Statement(s) and checks the Condition again. If the Condition is false, or when the Condition becomes false, the interpreter skips the Statement(s) section and continues with the code below the Loop keyword.

Here is an example:

Sub Exercise
    Dim Number As Integer

    Do While Number < 46
        Number = CInt(InputBox("Enter a number"))
        Number = Number + 1
    Loop
        
    MsgBox ("Counting Stopped at: " & Number)
End Sub

The Do Until... Loop Statement

Instead of performing an action while a condition is true, you may want to do something until a condition becomes false. To support this, the Visual Basic language provides a loop that involves the Until keywork. The formula to use is:

Do Until Condition
    Statement(s)
Loop

This loop works like the Do While... Loop expression. The interpreter examines the Condition first. If the condition is true, then it executes the Statement(s) section.

Here is an example:

Sub Exercise
    Dim Answer As String
    Answer = "0"

    Do Until Answer = "1"
        Answer = InputBox("Are we there yet (1=Yes/0=No)? ")
    Loop

    MsgBox("Wonderful, we have arrived")
End Sub

Loop Counters

 

Introduction

The looping statements we reviewed above are used when you do not know or cannot anticipate the number of times a condition needs to be checked in order to execute a statement. If you know with certainty how many times you want to execute a statement, you can use another form of loops that use the For...Next expression.

The For...To...Next Loop

One of the loop counters you can use is For...To...Next. Its formula is:

For Counter = Start To End
  Statement(s)
Next

Used for counting, the expression begins counting at the Start point. Then it examines whether the current value (after starting to count) is lower than End. If that's the case, it then executes the Statement(s). Next, it increments the value of Counter by 1 and examines the condition again. This process goes on until the value of Counter becomes equal to the End value. Once this condition is reached, the looping stops.

Here is an example:

Sub Exercise
    Dim Number As Integer

    For Number = 5 To 16
        MsgBox(Number)
    Next

    MsgBox("Counting Stopped at: " & Number)
End Sub

Practical Learning: Using a For Loop

  1. Locate the Exit event of the txtEmployeeNumber control and change it as follows: 
     
    Private Sub txtEmployeeNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Dim RowCounter As Integer
        Dim EmployeeNumberFromForm As Long
        Dim EmployeeNumberFromWorksheet As Long
        Dim EmployeeName As String
    
        ' If the user had entered an employee number on the form,
        ' retrieve it
        If IsNumeric(txtEmployeeNumber) Then
            EmployeeNumberFromForm = CLng(txtEmployeeNumber)
        Else
            EmployeeNumberFromForm = 0
        End If
            
        ' We assume the employee numbers are stored in the second column,
        ' from row 6 to row 106. That is, about 100 employees
        For RowCounter = 6 To 106
            ' Get the employee number on the current cell
            EmployeeNumberFromWorksheet = _
                Worksheets("Employees").Cells(RowCounter, 2).Value
            EmployeeName = Worksheets("Employees").Cells(RowCounter, 3).Value
            
            ' If you find an employee number that is the same as
            ' the user entered into the form, get its corresponding name
            If EmployeeNumberFromWorksheet = EmployeeNumberFromForm Then
                ' and display it on the form
                txtEmployeeName = EmployeeName
            End If
        Next
    End Sub
  2. On the Standard toolbar, click the Run Sub/UserForm button Run Sub/UserForm
  3. In the Employee # of the form, enter one of the numbers such as 54080 and press Tab
  4. Close the form and return to Microsoft Visual Basic

Stepping the Counting Loop

The formula above will increment the counting by 1 at the end of each statement. If you want to control how the incrementing processes, you can set your own, using the Step option. Here is the formula:

For Counter = Start To End Step Increment
  Statement(s)
Next

You can set the incrementing value to your choice. If the value of Increment is positive, the Counter will be added its value. Here is an example:

Sub Exercise
    Dim Number As Integer

    For Number = 5 To 42 Step 4
        MsgBox(Number)
    Next

    MsgBox("Counting Stopped at: " & Number)
End Sub

You can also set a negative value to the Increment factor, in which case the Counter will be subtracted the set value.

For Each Item In the Loop

Since the For...Next loop is used to execute a group of statements based on the current result of the loop counting from Start to End, an alternative is to state various steps in the loop and execute a group of statements for each one of the elements in the group. This is mostly used when dealing with a collection of items.

The formula is:

For Each Element In Group
    Statement(s)
Next Element

The loop will execute the Statement(s) for each Element in the Group.

Exiting a Procedure or a Loop

 

Exiting a Procedure

In the conditional statements and loops we have created so far, we assumed that the whole condition would be processed. Here is an example:

Sub Exercise
    Dim Number As Integer

    For Number = 1 To 6
        MsgBox(Number)
    Next
End Sub

This would produce:

Exiting Exiting Exiting
Exiting Exiting Exiting

In some cases, you may want to exit a conditional statement or a loop before its end. To assist with with this, the Visual Basic language provides the Exit keyword. This keyword works like an operator. It can be applied to a procedure or a For loop. Consider the following procedure:

Sub Exercise()
    MsgBox("Patricia Katts")
    MsgBox("Gertrude Monay")
    MsgBox("Hermine Nkolo")
    MsgBox("Paul Bertrand Yamaguchi")
End Sub

When the procedure is called, it displays four message boxes that each shows a name. Imagine that at some point you want to ask the interpreter to stop in the middle of a procedure. To do this, in the section where you want to stop the flow of a procedure, type Exit Sub. Here is an example:

Sub Exercise()
    MsgBox("Patricia Katts")
    MsgBox("Gertrude Monay")
    Exit Sub
    MsgBox("Hermine Nkolo")
    MsgBox("Paul Bertrand Yamaguchi")
End Sub

This time, when the program runs, the procedure would be accessed and would start displaying the message boxes. After displaying two, the Exit Sub would ask the interpreter to stop and get out of the procedure.

Because a function is just a type of procedure that is meant to return a value, you can use the Exit keyword to get out of a function before the End Function line. To do this, in the section where you want to stop the flow of the function, type Exit Function.

  • Change the code of the FindEmployee macro as follows:
     
    Sub FindEmployeee()
    '
    ' FindEmployeee Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+E
    '
        Dim EmployeeNumber As Long, EmployeeName As String
        
        If IsEmpty(Range("C4")) Then
            MsgBox "You must enter the employee number in cell C4"
            Range("D4").FormulaR1C1 = ""
            Exit Sub
        Else
            EmployeeNumber = CLng(Range("C4"))
        End If
        
        If EmployeeNumber = 22804 Then
            Range("D4").FormulaR1C1 = "Helene Mukoko"
        ElseIf EmployeeNumber = 92746 Then
            Range("D4").FormulaR1C1 = "Raymond Kouma"
        ElseIf EmployeeNumber = 54080 Then
            Range("D4").FormulaR1C1 = "Henry Larson"
        ElseIf EmployeeNumber = 86285 Then
            Range("D4").FormulaR1C1 = "Gertrude Monay"
        Else
            Range("D4").FormulaR1C1 = "Unknown"
        End 

Exiting a For Loop Counter

You can also exit a For loop. To do this, in the section where you want to stop, type Exit For. Here is an example to stop a continuing For loop:

Sub Exercise()
    Dim Number As Integer

    For Number = 1 To 12
        MsgBox(Number)

        If Number = 4 Then
            Exit For
        End If
    Next
End Sub

When this program executes, it is supposed to display numbers from 1 to 12, but an If...Then condition states that if it gets to the point where the number is 4, it should stop. If you use an Exit For statement, the interpreter would stop the flow of For and continue with code after the Next keyword.

Exiting a Do Loop

You can also use the Exit operator to get out of a Do loop. To do this, inside of a Do loop where you want to stop, type Exit Do.

Practical Learning: Exiting Code

  1. Locate the Exit event of the txtCustomerPhone control and change it as follows: 
     
    Private Sub txtCustomerPhone_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        ' This variable will be used to check the cells based on a row
        Dim RowIndex As Integer
        ' This variable holds the customer phone number from the form
        Dim CustomerPhoneFromForm As String
        ' This variable holds the customer phone number from the worksheet
        Dim CustomerPhoneFromWorksheet As String
        
        Dim CustomerName As String
        
        ' Get the customer phone from the form
        CustomerPhoneFromForm = txtCustomerPhone
        
        ' Trim the left side
        CustomerPhoneFromForm = LTrim(txtCustomerPhone)
        ' Trim the right side
        CustomerPhoneFromForm = RTrim(CustomerPhoneFromForm)
        ' Replace all spaces (in the middle of the phone number)
        CustomerPhoneFromForm = Replace(CustomerPhoneFromForm, " ", "")
        ' Replace the left parenthesis, if any
        CustomerPhoneFromForm = Replace(CustomerPhoneFromForm, "(", "")
        ' Replace the right parenthesis, if any
        CustomerPhoneFromForm = Replace(CustomerPhoneFromForm, ")", "")
        ' Replace the dash -, if any
        CustomerPhoneFromForm = Replace(CustomerPhoneFromForm, "-", "")
        
        ' The phone number records start on row 6
        RowIndex = 6
    
        Do
            If IsEmpty(Worksheets("Customers").Cells(CellIndex, 2).Value) Then
                Exit Sub
            End If
                
             CustomerPhoneFromWorksheet = _
    		Worksheets("Customers").Cells(RowIndex, 2).Value
    
             CustomerName = (Worksheets("Customers").Cells(RowIndex, 3).Value)
                
            ' Trim the left
            CustomerPhoneFromWorksheet = LTrim(CustomerPhoneFromWorksheet)
            ' Trim the right side
            CustomerPhoneFromWorksheet = RTrim(CustomerPhoneFromWorksheet)
            ' Replace all spaces (in the middle of the number
            CustomerPhoneFromWorksheet = _
    		Replace(CustomerPhoneFromWorksheet, " ", "")
            ' Replace the left parentheses, if any
            CustomerPhoneFromWorksheet = _
    		Replace(CustomerPhoneFromWorksheet, "(", "")
              
            ' Replace the right parentheses, if any
            CustomerPhoneFromWorksheet = _
    		Replace(CustomerPhoneFromWorksheet, ")", "")
            ' Replace the dash -, if any
            CustomerPhoneFromWorksheet = _
    		Replace(CustomerPhoneFromWorksheet, "-", "")
                                          
            If CustomerPhoneFromWorksheet = CustomerPhoneFromForm Then
                txtCustomerName = CustomerName
                Exit Do
            End If
                   
            RowIndex = RowIndex + 1
        Loop While RowIndex <= 100
    End Sub
  2. On the Standard toolbar, click the Run Sub/UserForm button Run Sub/UserForm
  3. Process an order
  4. Close the form and return to Microsoft Visual Basic
 
   

Previous Copyright © 2008-2010 FunctionX, Inc. Next