If Condition1 Then
    Statement1
ElseIf Condition2 Then
    Statement2
ElseIf Conditionk Then
    Statementk
End If

The program will first examine Condition1. If Condition1 is true, the program will execute Statment1 and stop examining conditions. If Condition1 is false, the program will examine Condition2 and act accordingly. Whenever a condition is false, the program will continue examining the conditions until it finds one that is true. Once a true condition has been found and its statement executed, the program will terminate the conditional examination at End If. Here is an example:

Sub Exercise()
    Dim MemberAge As Byte

    MemberAge = 32

    If MemberAge <= 18 Then
        MsgBox ("Membership: " & "Teen")
    ElseIf MemberAge < 55 Then
        MsgBox ("Membership: " & "Adult")
    End If
End Sub

This would produce:

1. Start Microsoft Excel and, on the Ribbon, click Developer
2. In the Code section, click Visual Basic
3. To add a new form, on the Standard toolbar, click the Insert UserForm button
4. Design the form as follows:
    

   Control Name Caption/Text Other Properties Label Number of CDs:   TextBox txtQuantity 0 TextAlign: 3 - frmTextAlignRight CommandButton cmdEvaluate Evaluate   Frame   Based on the Specified Quantity   Label   Each CD will cost:   TextBox txtUnitPrice 0.00 TextAlign: 3 - frmTextAlignRight Label   And the total price is:   TextBox txtTotalPrice 0.00 TextAlign: 3 - frmTextAlignRight

There is still a possibility that none of the stated conditions be true. In this case, you should provide a "catch all" condition. This is done with a last Else section. The Else section must be the last in the list of conditions and would act if none of the primary conditions is true. The formula to use would be:

If Condition1 Then
    Statement1
ElseIf Condition2 Then
    Statement2
ElseIf Conditionk Then
    Statementk
Else
    CatchAllStatement
End If

Here is an example:

Sub Exercise()
    Dim MemberAge As Byte

    MemberAge = 65

    If MemberAge <= 18 Then
        MsgBox ("Membership: " & "Teen")
    ElseIf MemberAge < 55 Then
        MsgBox ("Membership: " & "Adult")
    Else
        MsgBox ("Membership: " & "Senior")
    End If
End Sub

This would produce:

1. Double-click the Evaluate button and implement its Click event as follows:
    

   Private Sub cmdEvaluate_Click() Dim Quantity As Integer Dim UnitPrice As Currency Dim TotalPrice As Currency Quantity = CInt(txtQuantity.Text) ' The price of one CD will depend on the number ordered ' The more the customer orders, the lower value each If Quantity < 20 Then UnitPrice = 20 ElseIf Quantity < 50 Then UnitPrice = 15 ElseIf Quantity < 100 Then UnitPrice = 12 ElseIf Quantity < 500 Then UnitPrice = 8 Else UnitPrice = 5 End If TotalPrice = Quantity * UnitPrice txtUnitPrice.Text = CStr(UnitPrice) txtTotalPrice.Text = CStr(TotalPrice) End Sub

2. Press F5 to test the form
3. Perform the calculations with different quantities. For example, in the top text box, type 1250 and click Evaluate
    
   
4. After testing various quantities, close the form

As introduced in previous lessons, we know that a function is used to perform a specific assignment and produce a result. Here is an example:

Private Function SetMembershipLevel$()
    Dim MemberAge%

    MemberAge% = InputBox("Enter the Member's Age")

    SetMembershipLevel$ = ""
End Function

When performing its assignment, a function can encounter different situations, some of which would need to be checked for truthfulness or negation. This means that conditional statements can assist a procedure with its assignment.

1. Start another workbook
2. In cell B2, type Bethesda Car Rental
3. In cell B3, type Order Processing
4. In cell B4, type Processed by:
5. In cell B5, type Processed for:
6. In cell B6, type Car Selected
7. In cell B7, type Tag #:
8. Enlarge column B so that Processed by: can fit in the allocated width
9. Right-align cell B7

A function is meant to return a value. Sometimes, it will perform some tasks whose results would lead to different results. A function can return only one value (we saw that, by passing arguments by reference, you can make a procedure return more than one value) but you can make it render a result depending on a particular behavior. If a function is requesting an answer from the user, since the user can provide different answers, you can treat each result differently. Consider the following function:

Private Function SetMembershipLevel$()
    Dim MemberAge%

    MemberAge% = InputBox("Enter the Member's Age")

    If MemberAge% < 18 Then
        SetMembershipLevel$ = "Teen"
    ElseIf MemberAge% < 55 Then
        SetMembershipLevel$ = "Adult"
    End If
End Function

Sub Exercise()
    Dim Membership$

    MsgBox ("Membership: " & Membership$)
End Sub

At first glance, this function looks fine. The user is asked to provide a number. If the user enters a number less than 18 (excluded), the function returns Teen. Here is an example of running the program:

If the user provides a number between 18 (included) and 55, the function returns the Adult. Here is another example of running the program:

What if there is an answer that does not fit those we are expecting? The values that we have returned in the function conform only to the conditional statements and not to the function. Remember that in If Condidion Statement, the Statement executes only if the Condition is true. Here is what will happen. If the user enters a number higher than 55 (excluded), the function will not execute any of the returned statements. This means that the execution will reach the End Function line without encountering a return value. This also indicates to the compiler that you wrote a function that is supposed to return a value, but by the end of the method, it didn't return a value. Here is another example of running the program:

To solve this problem, you have various alternatives. If the function uses an If...Then condition, you can create an Else section that embraces any value other than those validated previously. Here is an example:

Private Function SetMembershipLevel$()
    Dim MemberAge%

    MemberAge% = InputBox("Enter the Member's Age")

    If MemberAge% < 18 Then
        SetMembershipLevel$ = "Teen"
    ElseIf MemberAge% < 55 Then
        SetMembershipLevel$ = "Adult"
    Else
        SetMembershipLevel$ = "Senior"
    End If
End Function

Sub Exercise()
    Dim Membership$

    Membership$ = SetMembershipLevel$()
    MsgBox ("Membership: " & Membership$)
End Sub

This time, the Else condition would execute if no value applies to the If or ElseIf conditions and the compiler would not produce a warning. Here is another example of running the program:

An alternative is to provide a last return value just before the End Function line. In this case, if the execution reaches the end of the function, it would still return something but you would know what it returns. This would be done as follows:

Private Function SetMembershipLevel$()
    Dim MemberAge%

    MemberAge% = InputBox("Enter the Member's Age")

    If MemberAge% < 18 Then
        SetMembershipLevel$ = "Teen"
    ElseIf MemberAge% < 55 Then
        SetMembershipLevel$ = "Adult"
    End If

    SetMembershipLevel$ = "Senior"
End Function

If the function uses an If condition, both implementations would produce the same result.

1. On the Ribbon, click Developer
2. In the Code section, click Record Macro
3. Set the Macro Name to LocateEmployee
4. In the Shortcut Key text box, type E to get Ctrl + Shift + E
    
   
5. Click OK
6. On the Ribbon, click Stop Recording
7. In the Code section of the Ribbon, click Macros
8. In the Macro dialog box, make sure LocateEmployee is selected and click Edit
9. Change the code as follows:
    

   Private Function GetEmployeeName(ByVal EmplNbr As Long) As String Dim Name As String If EmplNbr = 22804 Then Name = "Helene Mukoko" ElseIf EmplNbr = 92746 Then Name = "Raymond Kouma" ElseIf EmplNbr = 54080 Then Name = "Henry Larson" ElseIf EmplNbr = 86285 Then Name = "Gertrude Monay" Else Name = "" End If GetEmployeeName = Name End Function Public Sub LocateEmployee() ' ' Macro Name: LocateEmployee ' This macro is used to find the name of an employee ' based on the employee number ' ' 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 = "" EmployeeNumber = 0 Else EmployeeNumber = CLng(Range("C4")) End If EmployeeName = GetEmployeeName(EmployeeNumber) Range("D4").FormulaR1C1 = EmployeeName End Sub

10. Return to Microsoft Excel
11. In cell C4, type 54080 and press Enter
12. Press Ctrl + Shift + E to see the result