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 If ... Then statement tests a condition and if it evaluates to True, carries out a set of actions. If the condition evaluates to False, a different set of actions are carried out.
The format of the If ... Then statement is:
|If Condition1 Then|
Actions if Condition1 evaluates to TrueElseIf Condition2 Then
Actions if Condition2 evaluates to TrueElse
Actions if none of the previous conditions evaluate to TrueEnd If
In the above If statement, the ElseIf and the Else parts of the conditional statement can be left out if desired.
In the example below, an If ... Then statement is 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 greenElseIf ActiveCell.Value < 10 Then
ActiveCell.Interior.Color = 49407 ' Color cell interior orangeElse
ActiveCell.Interior.Color = 255 ' Color cell interior redEnd If
Note that, in the above example, the conditional statement stops once it has satisfied a condition. Therefore, if the ActiveCell value is less than 5, the first condition is satisfies and so the cell is colored green. The If ... Then statement is then exited, without testing any further conditions.
Further information on the VBA If ... Then statement is provided on the Microsoft Developer Network website.
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 Value1End Select
Actions if Expression matches Value1Case Value2
Actions if Expression matches Value2Case Else
Actions if expression does not match any of listed cases
In the above code block, the Case Else part of the conditional statement is optional.
In the example below, the Select Case statement is used to color the current active cell, depending on the value of the cell contents.
|Select Case ActiveCell.Value|
Case Is <= 5End Select
ActiveCell.Interior.Color = 65280 ' Color cell interior greenCase 6, 7, 8, 9
ActiveCell.Interior.Color = 49407 ' Color cell interior orangeCase 10
ActiveCell.Interior.Color = 65535 ' Color cell interior yellowCase Else
ActiveCell.Interior.Color = 255 ' Color cell interior red
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.
Further information on the VBA Select Case statement is provided on the Microsoft Developer Network website.