Logical Operators for Conditional Statements in VBA

While the previous post explained how to use conditionals to execute specific blocks of code, the conditions were relatively strict, relying on single variables or liberal values. This post provides an overview of how to incorporate logical operators into conditional statements, permitting much needed flexibility with complex testing.

Overview

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

Logical operators are operators inserted into conditional statements (If, ElseIf, Else) to combine various conditions and criteria. Through the use of these operators, developers can create any number of tests within a specific conditional statement. For example, let’s say that you want to run a block of code if two variables are a specific value, such as a bike type and bike color — the AND operator can be used to test this in a single conditional statement.

And Operator

As explained in the overview, the And operator can be used to test if two or more values are true at the same time. Combined with conditional statements, the And operator provides for efficient testing of complex conditionals. Use of the And operator is easy, only requiring the word And to be placed between two tests. In the following example, let’s assume that a university offers Statistics courses to both Business and Mathematics students.

The script will return a message the message “You are taking Statistics for Business Managers” given that the value “Business” is assigned to variable majorGroup, and “Statistics” is assigned to courseGroup. With the And operator, the conditional statement checks if both tests are true before identifying the entire statement as true. Drilling down on the first conditional, the program sees it as “True” And “True”, which returns a “True” statement for the conditional. Drilling down on the second conditional, the program sees it as “False” And “True” (variable majorGroup does not equal “Mathematics”), which returns a “False” statement for the conditional. In short, every test assigned to an And operator must be “True” for the full statement to return “True”.

And vs. Nested Conditional

In the previous post of the series, providing a general overview on using conditional statements, a note was provided explaining that nested conditionals may not be the most efficient or clear means of writing code. Simple nested conditionals can often be converted into more straightforward If or ElseIf statements using the logical operator And. In the following example, we show how to accomplish the Statistics course example using a nested loop, then show a clearer alternative using the And operator. First, here’s the solution with a nested loop:

While this solution accomplishes the task, it is slightly difficult to read. Imagine if the script were running results for 30 different courses within 8 different departments — the parent condition will be out of view for much of the script. While a nested loop is fine for this simple program, they can become burdensome to manage as a program becomes more complex.

Here’s a slightly streamlined version using the And operator:

The If-ElseIf statement accomplishes the same task with two fewer lines of code. Each conditional explicitly states the conditions that are being tested, which add clarity to the script’s intentions.

Or Operator

The Or operator provides for more flexibility in cases where a block of code should be executed when one or more variables is equal to any number of specific values. In the following example, a script that does a credit approval check contains an Or operator, approving credit if a customer has either a credit score greater than or equal to 700, or an income greater than or equal to $75,000.

With the Or operator, the conditional statement checks if any tests within the conditional statement are true before identifying the entire statement as true. Drilling down on the first conditional, the program sees it as “True” Or “False”, which returns “True”, because one of the values in the statement is true. Drilling down on the second conditional (ElseIf), the program sees it as “False” Or “False”, which returns a “False” because not one of the statements are true. A conditional statement containing an Or operator will return “True” whenever one or more of the individual statements is true.

Or vs. ElseIf

In some cases, you may want to test if a single variable value is any of many variables to return an identical block of code. For example, a number of courses may belong to a certain academic department. In the following example, an If-ElseIf statement is used to test if various courses are part of the Business Department. Note that the block of code returned is the same for each conditional test.

While the above code will work, you should see obvious messiness by the fact that 3 lines of code are duplicates. Given that the conditional contains three separate tests that return the same block of code, a single If statement using the Or operator can accomplish the same task with 5 fewer lines of code, and no duplication.

Not Operator

The Not operator provides a simple method for testing if a value is not true. Here’s a simple example:

The Not operator will return a value of “True” for any conditional test that is “False”. In summary, the Not operator dictates to return “True” if a test is not “True”.

The Not operator is most useful when you want to run a certain script if a Boolean variable is “False”. In the following script, the Boolean variable projectCompleted is assigned “False” (let’s say it’s signifying a project at work is not complete). We want the program to tell the user that the project is incomplete. This can be done with a simple If-Else statement (where the Else statement will provide the message), but it’s possible to accomplish this with a simple If statement when using the Not operator.

Even though the variable projectComplete is “False”, the Not operator is effectively telling the conditional to execute the block of code if the variable is not “True”. With that, the block will be executes and the user will see the message “Project is incomplete”.

Not Operator vs. <>

An alternative to the Not logical operator is the <> mathematical operator. In modifying the original Not operator example above, code is as follows:

There’s no real impact on efficiency or clarity between the two approaches. While not a stated rule, some developers choose to use the Not logical operator when using strings, while using the <> mathematical operator when using numbers.

Xor

The Xor operator is similar to the Or operator, but it applies a “or not” treatment to the variable following in. In essence, it has the same affect as variable1 = True Or Not (variable2 = False).

The script evaluates that the variable creditScore is greater than or equal to 700, or that annualIncome is not (Xor) less than or equal to $80,000. This test passes.

Combining Operators

The real power of logical operators lies in the ability to create statements to test complex conditions. Returning to the credit approval scenario, the following code includes a more complex approval conditions. Note that to make the conditional statement easier to read, it includes carriage returns (underscore “_”) to start a new line of code.

This script performs 6 tests to determine if a customer is eligible for credit. The tests are set into 3 groups (2 tests each) within brackets. The brackets tell the program that the tests are grouped together within the logical operator. Excluding the brackets will have unintended consequences. A summary of the logic is as follows:

  1. The customer’s credit score must be at least 700, and their income must be at least $80,000
  2. Or, if the customer does not meet the credit and income requirements, they can obtain credit if they have worked at least 8 years and have a Masters degree.
  3. Or, If the customer has a criminal record or has previously declared bankruptcy, they will not qualify for credit (Xor is used because those variables must be “False” to pass).

Other Notes

  • Although this post briefly touched upon alternative approaches to logical operators, such as nested loops, it’s ultimately up to the preference of the developer. Complex programs can benefit from logical operators in terms of readability and efficiency, but simple programs may be better suited with simple conditional statements.
  • Brackets should be used in complex conditional statements to group tests together. VBA treats brackets in conditional statements similar to brackets in arithmetic.

Leave a Reply