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:

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
    
   
5. Add a few records in the Customers worksheet
    
   
6. Save the workbook
7. To open Microsoft Visual Basic, on the Ribbon, click Developer and, in the Code section, click Visual Basic:
    
   
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

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:

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
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

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

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:

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.

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:

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

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 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:

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.

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

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.

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.

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

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

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

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.

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

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.

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.

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

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.

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.