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 sections of code.
How to update an Excel spreadsheet using ADO An example can be found at the bottom of the post. Option Explicit 'Purpose: Updates the contents of an Excel Spreadsheet using ADO 'Inputs: sWorkbookPath The path of the workbook to update the range contents of. ' sRange The range name or range reference to update (eg.
The code will be written in an old version of a Visual Basic IDE (VB6) and a new version (VB.NET). The screenshot will be done just for this example. In the other examples we shall give only the expected outcome. Do the following: Create a new project in your VB IDE (VB6 or VB.NET). Visual Basic Sample Codes Visual Basic 6 is a third-generation event-driven programming language first released by Microsoft in 1991. In VB 6, there is no limit of what applications you.
The two Conditional Statement types are discussed individually below.
The Visual Basic If ... Then Statement
Visual Basic Code Examples For Excel Spreadsheet
The If ... Then statement tests a condition and if it evaluates to True, executes a specific section of code. If the condition evaluates to False, a different section of code is executed.
The syntax of the If ... Then statement is:
IfCondition1Then Code to be executed if Condition1 evaluates to True ElseIfCondition2ThenCode to be executed if Condition2 evaluates to True ElseCode to be executed if none of the previous conditions evaluate to True End If |
In the above If statement, you can add as many ElseIf conditions as you require. Alternatively, the ElseIf and the Else parts of the conditional statement can be omitted 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 green ElseIf ActiveCell.Value < 10 ThenActiveCell.Interior.Color = 49407 ' Color cell interior orange ElseActiveCell.Interior.Color = 255 ' Color cell interior red End If |
Note that, in the above example, the If statement stops once it has satisfied a condition. Therefore, if the ActiveCell value is less than 5, the first condition is satisfied and so the cell is colored green. The If ... Then statement is then exited, without testing any further conditions.
For further information on the VBA If ... Then statement, see the Microsoft Developer Network website.
Excel Visual Basic Programming Examples
The Visual Basic Select Case Statement
The Select Case statement is similar to the If ... Then statement, in that it tests an expression, and executes different sections of code, depending on the value of the expression.
The syntax of the Select Case statement is:
Select CaseExpression CaseValue1CaseValue2 End SelectActions if Expression matches Value2 Case ElseActions if expression does not match any of listed cases |
Visual Basic Code Examples For Excel
In the above code, the Case Else part of the conditional statement is optional.
In the following example, 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 <= 5 End SelectActiveCell.Interior.Color = 65280 ' Color cell interior green Case 6, 7, 8, 9ActiveCell.Interior.Color = 49407 ' Color cell interior orange Case 10ActiveCell.Interior.Color = 65535 ' Color cell interior yellow Case ElseActiveCell.Interior.Color = 255 ' Color cell interior red |
Visual Basic Code Examples For Excel Pdf
The above example illustrates different ways of defining the different Cases in the Select Case statement. These are:
Microsoft Excel Visual Basic Examples
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 code executed, the whole Select Case statement is exited. Therefore, the code will never enter more than one of the listed cases.
For further information on the VBA Select Case statement, see the Microsoft Developer Network website.
Return to the Excel VBA Tutorial Page