Programming Excel with VBA and .NET : Conditional Statements

5/15/2013 7:44:16 PM

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: " & _
            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: " & _
        ElseIf TypeName(ActiveSheet) = "Worksheet" Then
            str = "Worksheet total: " & _
            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]
        ' 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.

Video tutorials
- How To Install Windows 8 On VMware Workstation 9

- How To Install Windows 8

- How To Install Windows Server 2012

- How To Disable Windows 8 Metro UI

- How To Change Account Picture In Windows 8

- How To Unlock Administrator Account in Windows 8

- How To Restart, Log Off And Shutdown Windows 8

- How To Login To Skype Using A Microsoft Account

- How To Enable Aero Glass Effect In Windows 8

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen
programming4us programming4us
Top 10
Free Mobile And Desktop Apps For Accessing Restricted Websites
TOYOTA CAMRY 2; 2.5 : Camry now more comely
KIA SORENTO 2.2CRDi : Fuel-sipping slugger
How To Setup, Password Protect & Encrypt Wireless Internet Connection
Emulate And Run iPad Apps On Windows, Mac OS X & Linux With iPadian
Backup & Restore Game Progress From Any Game With SaveGameProgress
Generate A Facebook Timeline Cover Using A Free App
New App for Women ‘Remix’ Offers Fashion Advice & Style Tips
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone