 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

"One of the following is not a Visual Basic keyword" & vbCrLf & _
"1) Function" & vbCrLf & _
"2) Except" & vbCrLf & _
"3) ByRef" & vbCrLf & _
"4) Each" & vbCrLf & vbCrLf & _

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

"One of the following is not a Visual Basic keyword" & vbCrLf & _
"1) Function" & vbCrLf & _
"2) Except" & vbCrLf & _
"3) ByRef" & vbCrLf & _
"4) Each" & vbCrLf & vbCrLf & _

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

Answer = InputBox(" -=- Multiple Choice Question -=-" & vbCrLf & _
"To create a constant in your code, " & _
"you can use the Constant keyword" & vbCrLf & _

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

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

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

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

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

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 3. In the Employee # of the form, enter one of the numbers such as 54080 and press Tab
 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
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 3. Process an order