Programming Excel with VBA and .NET : Conditional Statements

One of the fundamental elements of programming is making decisions based on inputs. Visual Basic provides the If statement for making either/or decisions and the Select statement for making multiple-choice decisions. These two statements form the core of any logic your program uses to adjust to different conditions, and for that reason they are called conditional statements .

The If statement has several different forms:

• A very simple one-line form:

      If IsArray(Selection) Then MsgBox "Multiple cells selected."
  

  
  

• A block form that can contain multiple lines and alternative actions:

      Dim str As String
      If IsArray(Selection) Then
          str = "Grand total: " & _
            WorksheetFunction.Sum(Selection)
      Else
          str = "Please select more than one cell"
      End If
      MsgBox str
  

  
  

• A block form with multiple conditions and alternate actions:

      If IsArray(Selection) Then
          str = "Grand total: " & _
            WorksheetFunction.Sum(Selection)
      ElseIf TypeName(ActiveSheet) = "Worksheet" Then
          str = "Worksheet total: " & _
            WorksheetFunction.Sum(ActiveSheet.UsedRange)
      Else
          str = "Please select a worksheet"
      End If
      MsgBox str
  

  
  

You can have multiple ElseIf statements within an If block as shown by the following general form:

    If condition Then
        ' Do something
    [ElseIf condition Then
        ' Do something else]
    [ElseIf condition Then
        ' Can repeat ElseIf]
    [Else
        ' Do something else]
    End If

For more complex logic, you can include If statements within an enclosing If statement, or you can use the Select Case statement. The following Select Case statement compares the current time against a list of literal times to determine which message to display:

    Dim str As String
    Select Case Time
        Case Is > #10:00:00 PM#
            str = "Bed time!"
        Case Is > #7:00:00 PM#
            str = "Time to relax."
        Case Is > #6:00:00 PM#
            str = "Dinner time!"
        Case Is > #5:00:00 PM#
            str = "Drive time."
        Case Is > #1:00:00 PM#
            str = "Work time."
        Case Is > #12:00:00 PM#
            str = "Lunch time!"
        Case Is > #8:00:00 AM#
            str = "Work time."
        Case Is > #7:00:00 AM#
            str = "Breakfast time!"
        Case Else
            str = "Too early!"
    End Select
    MsgBox str


					  

Select statements are evaluated from the top down. Select exits after the first match, so only one of the messages is set.

Visual Basic provides one more conditional statement, though it is not commonly used. The Switch statement is similar to Select, but rather than executing statements, Switch returns a value based on different conditions. The following code is equivalent to the preceding example, except it uses Switch rather than Select:

    Dim str As String
    str = Switch(Time > #10:00:00 PM#, "Bed time!", _
      Time > #7:00:00 PM#, "Time to relax.", _
      Time > #6:00:00 PM#, "Dinner time!", _
      Time > #5:00:00 PM#, "Drive time.", _
      Time > #1:00:00 PM#, "Work time.", _
      Time > #12:00:00 PM#, "Lunch time!", _
      Time > #8:00:00 AM#, "Work time.", _
      Time > #7:00:00 AM#, "Breakfast time!", _
      Time >= #12:00:00 AM#, "Too early!")
    MsgBox str

Perhaps the reason Switch isn't used more often is because it results in long statements that must be broken over multiple lines to be readable in the Code window.