VBA Boolean data type can only be either TRUE or FALSE – expressed either as “1” (TRUE) or “0” (FALSE).
Boolean values are commonly used with IF statements to verify whether a given condition is met or not, allowing you to build complex logical comparisons.
A blank canvas in the VBA editor is waiting for us, so let’s get started.
Declaring Boolean Variables
First things first, let’s start with declaring Boolean variables so that you can use them in your VBA code.
There are two ways to declare a Boolean variable:
- on the module level, meaning the variable can only be used within a given module
- on the global level, meaning the variable can be used across the entire VBA project
Use the following Dim statement to create a Boolean value on the module level:
Dim BooleanValue As Boolean
On the other hand, copy this public statement to create a global Boolean variable:
Public BooleanValue As Boolean
Example #1: Using Boolean Variables
Let’s move from theory to practice.
The examples below demonstrate how to use the Boolean data type in VBA.
Start with creating a new module (Insert > Module). Once there, copy the following code into the VBA editor and click “Run Sub/UserForm” or press F5 to execute it:
Sub Boolean_Vba() Dim BooleanValue As Boolean BooleanValue = 50 > 115 MsgBox BooleanValue End Sub
This simple Sub procedure compares whether 50 is greater than 115 and returns a message box with the corresponding Boolean value based on this simple logical comparison.
Let’s take a closer look at the VBA code to help you adjust accordingly:
- Dim BooleanValue As Boolean – This line of code creates a new variable named “BooleanValue” and converts it to the Boolean data type.
- BooleanValue = 50 > 115 – This determines the actual Boolean value of the variable based on the specified logical comparison. If 50 is greater than 115, the value is set to TRUE. If not, the value is set to FALSE.
- MsgBox BooleanValue – This VBA command creates a message box containing the output.
Predictably, VBA returns FALSE since 50 is not greater than 115.
Related Article: How to Change The Position And Size Of Excel Charts In VBA
Example #2: Boolean Variables & IF Statements
That was a simple example, so let’s ramp up the difficulty level and break down how you can use the VBA Boolean data type with IF statements.
Imagine you’re overseeing a web development project where the devs are getting paid based on their hourly rate. Your fictitious company allocated 54 hours to the project.
Armed with this data (and the knowledge of how to apply Boolean values), you can put together a simple IF statement to check if the project runs over budget at any stage of the development process:
Sub Boolean_Vba() Dim HoursTracked As Integer Dim HoursPlanned As Integer Dim Result As Boolean HoursTracked = 13 HoursPlanned = 54 If HoursTracked > HoursPlanned Then Result = True Else Result = False End If MsgBox "Project cost overrun: " & Result End Sub
The screenshot below breaks down the building blocks of this Sub procedure in greater detail:
As a result, VBA will return this message box to help you spot any project cost overrun.
In VBA, Boolean operators make it possible for you to work with combinations of Boolean variables, providing you a lot more flexibility.
The four most common Boolean operators in VBA are AND, OR, NOT, and XOR. In this section, we will show you how to use each of them.
The AND Operator
The AND operator connects two or more Boolean expressions and returns TRUE only if all of the conditions are met.
In the example below, the variable “Result” equals TRUE only if both the first condition (50 > 155) and the second condition (50 < 55) are TRUE.
Sub Boolean_Vba() Dim Result As Boolean Result = 50 > 155 And 50 < 55 // Use the AND operator to check if both of the conditions are met MsgBox Result End Sub
The OR Operator
The OR operator returns TRUE if at least one of the conditions is met.
In the same example, the variable “Result” equals TRUE if either the first or second condition is TRUE.
Sub Boolean_Vba() Dim Result As Boolean Result = 50 > 155 Or 50 < 55 // Use the OR operator to check if any of the conditions are met MsgBox Result End Sub
The NOT Operator
The NOT operator turns TRUE into FALSE, and vice versa.
In this example, since 50 is not greater than 155, the VBA editor should return FALSE. However, since the NOT operator is applied, it takes truth to falsity. Another variation is the not-equal-to operator (<>) that works in a similar way.
Sub Boolean_Vba() Dim Result As Boolean Result = Not 50 > 155 // Use the NOT operator to reverse the TRUE and FALSE values MsgBox Result End Sub
The XOR Operator
The XOR operator returns TRUE only if two conditions are not TRUE or FALSE at the same time.
In the example below, the “Result” variable is TRUE because the first condition (50 > 155) is FALSE while the second condition (50 < 55) is TRUE.
Sub Boolean_Vba() Dim Result As Boolean Result = 50 > 155 Xor 50 < 55 // Check if BOTH or NEITHER of the conditions are met MsgBox Result End Sub
1. The default Boolean value is FALSE.
Sub Boolean_Vba() Dim Result As Boolean MsgBox Result End Sub
2. By default, every nonzero number is TRUE.
Sub Boolean_Vba() Dim Result As Boolean Result = 15 MsgBox Result End Sub
3. Zero is equal to FALSE.
Sub Boolean_Vba() Result = 0 MsgBox Result End Sub
4. Boolean values can’t be anything but TRUE or FALSE. If you try to turn a Boolean variable into any other data type, this breaks the code and triggers an error.
Sub Boolean_Vba() Dim Result As Boolean Result = “Bird is the word” MsgBox Result End Sub
Result: a type-mismatch error – as shown on the screenshot below.