Excel VBA Tutorial Part 5: VBA Conditional Statements


The main Excel VBA Conditional Statements are the If ... Then statement and the Select Case statement. Both of these evaluate one or more conditions and, depending on the result, execute specific actions.

The two Conditional Statement types are discussed individually below.


The Visual Basic If ... Then Statement

The If ... Then statement tests a condition and if it evaluates to true, carries out a set of actions. Alternative actions can be specified if the condition evaluates to false.

The format of the If ... Then statement is:

The main Excel VBA Conditional Statements are the If ... Then statement and the Select Case statement. Both of these evaluate one or more conditions and, depending on the result, execute specific actions.

The two Conditional Statement types are discussed individually below.


The Visual Basic If ... Then Statement

The If ... Then statement tests a condition and if it evaluates to true, carries out a set of actions. Alternative actions can be specified if the condition evaluates to false.

The format of the If ... Then statement is:

  If Condition1 Then
      Actions if Condition1 evaluates to True
  ElseIf Condition2 Then
      Actions if Condition2 evaluates to True
  Else
      Actions if none of the previous conditions evaluate to True
  End If


In the above if statement, the ElseIf and the Else parts of the conditional statement can be left out if desired.


The example below shows the If ... Then statement being used to color the current active cell, depending on the value of the cell contents.

  If ActiveCell.Value < 5 Then
      ActiveCell.Interior.Color = 65280  ' Color cell interior green
  ElseIf ActiveCell.Value < 10 Then
      ActiveCell.Interior.Color = 49407  ' Color cell interior orange
  Else
      ActiveCell.Interior.Color = 255    ' Color cell interior red
  End If


The Visual Basic Select Case Statement

The Select Case statement is similar to the If ... Then statement, in that it tests an expression, and carries out different actions, depending on the value of the expression.

The format of the Select Case statement is:

  Select Case Expression
    Case Value1
        Actions if Expression matches Value1
    Case Value2 or Expresson2
        Actions if Expression matches Value2
        .
        .
        .
    Case Else
        Actions if expression does not match any of listed cases
  End Select

In the above code block, the Case Else part of the conditional statement is optional.

The example below shows the Select Case statement being used to color the current active cell, depending on the value of the cell contents.

  Select Case ActiveCell.Value
    Case Is <= 5
        ActiveCell.Interior.Color = 65280  ' Color cell interior green
    Case 6, 7, 8, 9
        ActiveCell.Interior.Color = 49407  ' Color cell interior orange
    Case 10
        ActiveCell.Interior.Color = 65535  ' Color cell interior yellow
    Case Else
        ActiveCell.Interior.Color = 255    ' Color cell interior red
  End Select


The above example illustrates different ways of defining the different Cases in the Select Case statement. These are:

Case Is <= 5 This is an example of how you can test if your expression satisfies a condition such as <= 5 by using the keyword Case Is
Case 6, 7, 8, 9 This is an example of how you can test if your expression evaluates to any one of several values, by separating the possible values by commas
Case 10 This is an example of the basic test of whether your expression evaluates to a specific value
Case Else This is an example of the 'Else' condition, which is executed if your expression hasn't matched any of the previous cases

Note that as soon as one case in the Select Case statement is matched, and the corresponding actions executed, the whole Select Case statement is exited. Therefore, you will never get entry into more than one of the listed cases.

Go To Excel VBA Tutorial Part 6 - Loops

Return to the Excel VBA Tutorial Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net