Functions Language: Conditional

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:

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
If the value of Expression2 is equal to that of Expression, then Statement2 is executed
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:

Practical Learning: Introducing Select Cases

Start Microsoft Excel
From the resources that accompany these lessons, open the gdcs1 (or gdcs2) workbook you
Change the names of the first and the second worksheets to Employees and Customers respectively
Add a few records in the Employees worksheet
Add a few records in the Customers worksheet
Save the workbook
To open Microsoft Visual Basic, on the Ribbon, click Developer and, in the Code section, click Visual Basic:
Right-click the Time Left text box (the text box on the right side of Time Left) and click View Code

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:

Practical Learning: Using Select Case

In the Objects combo box, select txtEmployeeNumber
In the Procedure combo box, select Exit

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

On the Standard toolbar, click the Run Sub/UserForm button
In the Employee # of the form, enter one of the numbers such as 54080 and press Tab
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:

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:

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:

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:

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:

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 Learning: Nesting Conditions

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

Press F5 to test the form
Enter the employee number as 92746, and press Tab
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:

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

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:

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:

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:

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

Practical Learning: Negating a Condition

In the Object combo box, select txtDateLeft
In the Procedure combo box, select Exit

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

Press F5 to test the form
Enter the employee number as 92746, and press Tab
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

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:

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

In the Objects combo box, select txtCustomerPhone
In the Procedure combo box, select Exit

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

On the Standard toolbar, click the Run Sub/UserForm button
In the Employee # of the form, enter one of the numbers such as 54080 and press Tab
In the Customer Phone text box, enter one of the phone numbers and press Tab
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

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

On the Standard toolbar, click the Run Sub/UserForm button
In the Employee # of the form, enter one of the numbers such as 54080 and press Tab
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:

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

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

On the Standard toolbar, click the Run Sub/UserForm button
Process an order
Close the form and return to Microsoft Visual Basic

Functions Language

Pages

Conditional

No comments:

Post a Comment

Related Scripting

Disqus for Functions

Pages

Conditional

No comments:

Post a Comment

Related Scripting

Disqus for Functions

Receive all updates via Facebook. Just Click the Like Button Below▼

Receive all updates via Facebook. Just Click the Like Button Below
▼