How to☝️ Use the Boolean Data Type in Excel VBA

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.

Boolean Operators 

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

Result: FALSE

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

Result: TRUE

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

Result: TRUE

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

Result: TRUE

Usage Notes

1. The default Boolean value is FALSE.

Sub Boolean_Vba()
Dim Result As Boolean
MsgBox Result
End Sub

Result: FALSE

2. By default, every nonzero number is TRUE.

Sub Boolean_Vba()
Dim Result As Boolean
Result = 15
MsgBox Result
End Sub

Result: TRUE

3. Zero is equal to FALSE.

Sub Boolean_Vba()
Result = 0
MsgBox Result
End Sub

Result: TRUE

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.