Conditional statements allow us to make decisions in our macros. Without them, we would be severely hampered in our ability to write macros that accurately carry out our intended tasks.
Thanks for your interest
This content is part of a paid plan.
Introduction to Conditional Statements
Inevitably, we encounter choices in our macros. If we’re at the end of a paragraph, do this, but if not, do that. We need a way to these handle different cases.
Enter conditional statements.
They give us the ability to check an aforementioned condition and run different steps depending on whether it is True or False. Often the conditions come in the form of testing one value against another, but more complicated ones exist such as—is document RangeA contained inside RangeB.
There are a several types of conditional statements most of which are variations or extensions of If statements. If you have a long list of conditions, check out the Select statement instead.
What’s the basic format?
If we only want to run certain steps when a given condition is True and do nothing else otherwise, use a regular If statement.
This is the most basic branching logic.
SomeCondition is a test that results in either a True or False value (called Boolean after early theory by George Boole). The If statement only runs the enclosed steps if the condition is True. Nothing happens if SomeCondition is False. Sometimes that’s exactly what we want.
If-Else variation
If we also have steps to do if the condition is False, we can add an Else part.
Only one section of steps is done.
Types of conditions
There are many of ways to set up a conditional statement. The most common ones include the regular comparison operators we learned as children: less than <, greater than >, equal to =, and variations.
Unfortunately, an equality condition in VBA looks the same as assigning a value (ThisValue would be changed to ThatValue), so VBA must infer what we intend by context (some other languages use a double equals == to be clearer).
Shorthand for the inequalities exist, <= or >= respectively, if we also want to allow the variables to be equal to give a True result.
Finally, the not equal condition has its own symbol <> which literally means less than or greater than.
Text equality
All of these work for numbers, but programming languages like VBA usually try to include some equivalents for other data types. For example, this condition checks whether two text variables (usually called strings) are exactly the same.
A similar expression allows us to check when strings do not match.
These statements mimic equality tests for numbers which is convenient. However, things get more complicated if we want to be more specific (such as alphabetizing a list of names) since the other comparisons don’t apply to text in the same way, but that is outside the scope of this article.
Other condition examples
Some more complex conditions exist. Just to give a flavor of possibilities, here are a few examples.
Does the current paragraph have a Normal paragraph style? Styles are objects in VBA, but we can conveniently refer to and compare as strings them just by their text name.
Is the document range SomeRange contained inside the given range AnotherRange. This is a standard range method, but it would depend on the relative positions of the Start and End positions of each range.
Is the current Selection a “normal” or regular selection Type. We’re usually considering spanned text, but it can be more complicated.
And many more.
Special Nothing object comparison
Objects can be declared as variables in a macro, but they can’t be used until they’re assigned to relevant document (or whatever) data. They only exist as a variable possibility of sorts. Any object not yet assigned has a special value of “Nothing” (which is slightly different than a “Null” value if you’re familiar with it from other languages; there is even an “Empty” value for undefined generic variable types; arghhh).
When evaluating valid objects, we use a special notation. The comparison operator “Is” checks whether two variables represent the same object, but we can also use it to determine whether an object is Nothing.
This type of check is important since we need a way to validate objects, or our macros or functions may crash and stop running in the middle of a task. A common example is validating range variables before taking any range-related action.
Does not work with value variables
The Is operator comparison does not work with value types like numbers or Boolean variables because the variable holds the actual value of the stored data not just a reference to it.
SomeInteger is not an object. It is a number value. A full explanation is more complicated, but that is the gist of the topic.
Nesting If statements
If we need more complicated conditions, a first attempt might be to nest them by putting a second If statement inside the first.
But this is a clumsy way to include multiple conditions unless you have a particular need. See below for a couple examples.
Nested If statements with a required condition
This structure comes up occasionally when you definitely need one condition checked before checking a second condition. An example might be validating whether a range exists before trying to use it:
In this example, if MyRange is not assigned to anything yet (has a value of Nothing), then VBA would crash when we try to access its InRange method.
This is necessary because VBA will not “short-circuit” (end early as soon as it is sure of the result) a condition evaluation like some languages do. It always evaluates the entire expression before deciding which branch to take. Other languages are more robust when evaluating such statements. Even regular Visual Basic has other logical operators (AndAlso and OrElse) to help with this issue.
Nested If statements with extra commands
Other uses for nested conditions might occur if you have other steps that are always run with condition one.
While a nested If statement gets the job done, using compound conditions (see next) is another common method to handle more complicated conditions.
Compound conditions
We can create more complicated conditions using compound statements usually with the logical operators Not, And, and/or Or. These are called “Boolean operators” because they allow us to “operate on” various Boolean conditions.
Negation with Not
First, a special symbol exists for not equal <> which literally means less than or greater than.
We can “negate” any condition by placing a Not in front of it.
Negating the condition literally switches True to a False value and vice versa. This particular statement would evaluate whether SomeRange is not contained in AnotherRange.
Or
Or statements are True if either or both conditions are True. They result in False only if both conditions are False.
For example, if we let x = 1, y = 2, and z = 3 then
This would evaluate to
This evaluates to an overall True value because one of the values is True.
And
And statements require both conditions to be True to get a True value. It results in a False value if either or both conditions are False.
With the same variables and values from above, we let x = 1, y = 2, and z = 3 then
This would evaluate to
This evaluates to an overall False value because one of the values is False.
Xor
The above are by far the most important Boolean operators, but one other in VBA is Xor which stands for “exclusive or”. Xor is True only if one of the conditions is True. It results in False if both conditions are False or both conditions are True. That is, it excludes the True Xor True case.
With the same variables and values from above, we let x = 1, y = 2, and z = 3 then
This would evaluate to
This evaluates to an overall True value because only one of the values is True.
I’ve only used Xor twice in many years of programming in several languages, but an example in VBA might be testing whether double quotes are only on one side of a given range. Using Xor would make the logic a little simpler.
Example with an If statement
It can get a little more complicated when all of the above get mixed into an If statement.
Of course, there are an infinite variety of conditions and combinations. Always make sure your Boolean expressions evaluate the way you intend.
Order of operations
Much like regular multiplication and addition. Logical operators have a natural order they are evaluated if no parentheses are involved. Specifically, logical expressions will be evaluated in order by: Parentheses and comparisons (<, >, =, <>, <=, >=, or Is) are applied or evaluated as necessary. Then the other logical operators are applied to the True-False values in the order: Not, And, and finally Or.
Much like regular mathematical calculations, we can take advantage of this natural order to simplify some logical expressions. It’s not required though, and I tend to err on the side of including parentheses if there is any question of clarity.
Grouping compound conditions
Depending on your circumstances, you can use parentheses to group conditional statements much like in regular mathematics. To shorten the example, let x1, x2, y2, and y2 be numbers.
Much like mathematics, the conditions inside the parentheses are evaluated as a group before combining the result with the outside conditions and/or logical operators.
Order of operations examples
Technically using the order of operations mentioned above, this particular expression could be simplified because Or is evaluated after the And automatically because And precedes Or.
I dislike unnecessary parentheses in mathematics, but this “simpler” expression is just harder to read in my opinion.
If we instead had the following expression with parentheses around the first pair of conditions, we would not be able to remove the parentheses.
But I tend to err on the side of including parentheses in logical expressions just to be extra clear since few of us do them enough to easily read the exact order (other than parentheses first).
ElseIf statements
Sometimes more than two branches are necessary, but regular If-Else statements only handle two—the main True condition and all steps corresponding to the False condition after the Else keyword.
As mentioned above, we could nest If statements, and I’ve done that on occasion when it’s clearer or necessary, but it’s often easier to use If-ElseIf statements. At some point, you’ll likely need a decision tree with several possible branches.
If you need more than a few branches, it’s probably better to use a Select statement since they’re much easier to read with a longer list of conditions.
Basic syntax
An If-ElseIf is essentially a chain of If statements with another keyword ElseIf joining them together.
If the first condition falls through (is False), then check the next one, and repeat until no more ElseIfs are present.
We can chain more ElseIfs together in the middle section, but if it gets too long, you should probably consider Select statement since they’re designed for more lengthy decision branches.
Order matters
ElseIf takes the place of separate If statements connecting them, so only one set of steps are run. That is, once a branch is selected based on the first condition with a True value, run those steps. All steps in other branches will be ignored regardless of whether any following conditions would also be True. If you need two sets of steps run if both conditions are True, you’ll need to use separate If statements.
If all branch conditions are False, do the Else steps if they’re included just like a regular If statement.
IIf function
Yep, you read that right—an IIf statement (really a function). It’s almost a shorthand version of an If-Else statement if you only need to return a value or an object.
As implied, it tests the condition in the first argument and then returns the second argument if the condition is True but the third argument if the condition is False.
The second and third conditions could be a value or an object such as a range. We cannot include a bunch of steps as in a regular If statement and variations. Still, IIf givens us a useful and concise replacement for an If-Else statement if that’s all you need.
It always evaluates both results even though only one of them is used, so both must be error free.
