Using Conditional Statements in VBA

Previous posts on the Getting Started with VBA Series have focused on how to both extract information from cells on the spreadsheet, and create information in VBA with variables. While these may meet the needs of some simple programs, conditional statements open the door to creating responsive programs.

Overview

This post within the Getting Started with VBA series begins the focus on Logic in VBA.

Before discussing conditional statements in detail, it’s important to understand control flow. With all programming languages, control flow dictates the order in which statements of code are executed. In its default form, control flow goes from the top of a script, to the bottom, also executing all called subroutines and functions in top-to-bottom order.

There are a few ways to override standard control flow. In this post, you will learn about conditional statements, which are a fairly easy method of selectively executing specific blocks of code. In the next few posts, you will also learn about case-and-switch statements and loops. GoTo statements are another way to modify control flow, but they are somewhat controversial in the programming community and will not be discussed as part of this series.

If Statement

The foundation of a conditional statement is the If statement. The If statement sets up the initial test to determine if a certain block of code should be run. In short, the If statement is “True”, such as “x = x”, then a block of code within the if statement will run. Here’s an example of a simple If statement testing one variable.

In the above script, the message “Condition is True” will appear in a message box given that variable testVar has been assigned the value “3” and is equal to the literal value “3”. If testVar was assigned “5”, the condition test would be “False”, and the block of code within the If statement would not be executed. Here’s a workflow diagram showing control flow opportunities.

Workflow diagram for a simple conditional If Statement.
Workflow diagram for a simple If Statement.

Now let’s say that you want a different script to run depending on if the condition is “True”, or if the condition is “False”. Your first inclination may be to simply include the script below the End If statement, but this would actually cause the script to run both if the condition is “False” (skips over the code block within the If statement), and when it is “True” (will still execute the script after the If statement). This is where an If-Then-Else statement is useful, By including an Else statement, you can specify a block of code to run only when a condition is “False”.

In the above script, the message “Condition is False” will appear, given that the variable testVar has now been assigned “5”, and the condition is still testing if testVar is equal to “3”. A return value of False now causes the script to execute the Else statement, as opposed to leaving the conditional completely.

Workflow diagram for a simple conditional If-Then-Else Statement.
Workflow diagram for a simple If-Then-Else Statement.

ElseIf Statement

In some circumstances, you may want to test if a variable is equal to one of many values. This can be done through an ElseIf statement. As the name suggests, an ElseIf statement is a combination of an Else and an If statement. The key is that a conditional statement can contain as many ElseIf statements as needed, and the program will exit the conditional once a single If, ElseIf, or Else statement is executed.

Here’s a sample of a basic conditional statement containing an ElseIf statement.

The above example contains an If statement, two ElseIf statements, and one Else statement. If the initial If statement returns False, each ElseIf statement will be tested in sequence until a single ElseIf statement returns “True”. Upon running the block of code, the program will exit the conditional statement and will not test any of the other conditions. If none of the ElseIf statements return “True, the conditional statement will execute the Else statement before exiting the conditional.

One thing to note is that VBA does not require a final Else statement to execute this type of conditional. Many other programming languages require the final Else statement, but VBA does not. Regardless, it’s generally a good practice to include this as it can help control for inadvertent changes or user input errors.

Workflow diagram showing a conditional If-Elseif-Else statements
Workflow diagram showing If-Elseif-Else statements

Nested If Statements

If statements can be nested within each other. For example, a program needs to run a block of code if two variables equal a certain value. You can nest an If statement within another If statement.

On execution, this script will execute the highlighted line, showing “testVar is 3 and nestedVar is 11” in a message box. As you can see, the nested condition has been indented for ease of readability.

Workflow diagram showing a nested If statement.
Workflow diagram showing a nested If statement.

A Note About Nested If Statements

Nested If statements can be a leading cause of spaghetti code. The above example can be much more efficiently coded by using logical operators, which are discussed in the next post of this series on logical operators. While nested conditionals may be required for complex programs, simple scripts can generally be condensed. The above example was used for simple demonstration purposes.

Use of Mathematical Operators

Up to this point, the conditional examples have used the “Equal To”, or “=” operator for testing. There are a number of other basic mathematical operators that can be used for testing. These include:

< Less than

> Greater than

<= Less than or Equal To

>= Greater than or equal to

<> Not equal to

Just to note, in VBA, the “Equal To” mathematical operator is the same character as the “Assignment” operator used to assign a value to a variable. Keep in mind, however, that VBA reads the “=” sign in context and determines its proper usage. In many other programming languages, the “Equals To” operator is actually identified by two equals signs: “==”

Comparing Strings

String variables can be compared with the equal to (=) operator, as follows:

The only other operator that will work with strings is the not equal to (<>) operator. For any greater than or less than comparison, you must use the StrComp function, as follows:

The StrComp function above will return “0”, making the first condition true. As explained in the previous post of this series on String and Numeric Functions, the StrComp function will return the following values when comparing strings:

  • If str1 is equal to str2, function will return 0.
  • If str1 is greater than str2, function will return 1.
  • If str1 is less than str2, function will return -1.

Also, note that if a string is a string literal (directly typed in script; not a variable) it must be surrounded by quotes. This is not the case for numeric literals.

Comparing Two Variables

All previous examples have focused on comparing variables with numeric and string literals. In other words, the conditional statement contains one variable, and one value directly typed into the script.

You find that in many instances, you will want to compare two variables.

Unlike many other programming languages, VBA will not compare data types in conditional statements. That is, if one variable is declared as an integer, and a second variable is declared as a double, “True will be the returned result as long as values are identical. In fact, a number declared as a string can be tested against an integer, and Boolean “True can be compared against string “True. Regardless, this can be considered messy coding and should be avoided.

Boolean Values in Conditionals

As previously explained in the VBA variables overview, a Boolean variable can contain the value “True” or “False”. Given that conditional statements are driven by Boolean values returned after comparing variables, a Boolean variable will allow you to skip the step of comparing variables.

For example, a new VBA developer may assume that this is how to test a Boolean in a conditional statement:

This will work, but it adds an unnecessary test in the initial If statement. The script tests the Boolean variable testVar against the literal value “True”, to return the Boolean “True”. The following is a more efficient approach:

Both scripts will return a message box “Condition is True”. Given that the variable testVar is already set to Boolean “True”, no test is required and the program can execute the conditional as soon as it reads the variable value.

If testVar was assigned the Boolean “False”, the Else statement would execute.

Other Notes

  • It’s generally a good practice to indent (tab) within conditional statements.
  • Common mistakes made include forgetting to include “Then” after an If or ElseIf statement, or forgetting to place End If at the end of the conditional statement.
  • Code within a conditional statement is often refereed to as a “block” of code.
  • It’s also worth nothing that the integer “0” (zero) carries the value of “False”. For example, a countdown script can return “True” for all integers greater than 0, then return “False” when the countdown reaches 0. This is another trick to avert explicit conditional tests. This approach may seem very specific, but this type of script is encountered quite frequently and will be applied in our future topic of Loops.

Leave a Reply