VBA Boolean

What is Excel VBA Boolean Operator?

Boolean is a data type available in VBA that accepts only Boolean values when assigned to a variable. There are only two Boolean values; they are TRUE and FALSE.

The Boolean data type is typically used in logical operations to perform tasks based on the TRUE or FALSE result of the logical test. When we assign a Boolean data type to any variable, it occupies 2 bytes of the memory. Boolean values are represented by numerical values besides TRUE or FALSE. TRUE is represented by 1. FALSE is represented by 0.

For example, look at the following code.

Sub Boolean_Intro()
Dim Exam_Score As Integer
Dim Total_Score As Integer
Dim Result As Boolean
Exam_Score = 50
Total_Score = 60
Result = Exam_Score >= (Total_Score * 0.5)
MsgBox Result
End Sub

We have defined Integer data-type variables to store the exam and total scores. Similarly, the Result variable is also defined with the VBA Boolean data type. If the exam score is greater than or equal to 50% of the total score, then we will see a TRUE value, else we will see FALSE.

Since the exam score’s value is greater than 50% of the total score, we get the Boolean result as TRUE.

Working with Boolean Data Type in VBA Programming Language

To understand the functionality of the VBA Boolean variable data type, follow the steps below.

Step 1: Start a subroutine procedure in the Visual Basic Editor window.

Note: To open the Visual Basic editor window, press the excel shortcut key, ALT + F11, in opened Excel workbook.

Step 2: Define a variable by giving a name to it. To define a variable, use the DIM keyword.

Step 3: Once the variable is defined, we must assign a data type. Hence, use the keyword “As” and assign the data type “Boolean.”

Once the data type VBA Boolean is assigned to the defined variable, we can see it in blue.

Step 4: Now, we can assign only Boolean values, TRUE or FALSE, to the Boolean data type variable.

But to understand its functionality, let us assign a different value to the defined variable.

Step 5: Now, let’s try to execute the code and see what happens.

Step 6: We have received an error stating “Run-time error ‘13’: “Type mismatch.”

It is because of the mismatch of the values we assigned to the defined variable where it accepts only Boolean values, i.e., TRUE or FALSE.

Step 7: We can apply some logical tests instead of assigning a value to the Boolean data type variable. Logical tests return only Boolean values, i.e., either TRUE or FALSE.

Let us assign the logical operation as shown below.

Step 8: Let’s show the “MyVar” variable value in a message box.

Step 9: Execute the code by pressing the F5 shortcut key.  We get the Boolean result per the logical test applied to the defined variable.

The applied logical test 45 > 50 returns a FALSE Boolean result because 45 is not greater than 50. This way, we can work around with the VBA Boolean data type.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Boolean Data Type Cannot Hold Other than TRUE or FALSE

VBA Boolean is a logical data type that can contain only two values: TRUE (1) and FALSE (0). We can use the Boolean data type to store only TRUE or FALSE values, and anything outside of these two values will result in an error Type Mismatch in VBA.

To demonstrate the error associated with the VBA Boolean data type, let us look at the following VBA code.

Sub Boolean_Only_TRUE_FALSE()
Dim K As Boolean
K = “Hi There!”
MsgBox K
End Sub

• We have defined a variable “k” and assigned the data type Boolean in VBA. In the following line, we have assigned a value to the defined variable “Hi There!” which is not a Boolean value in Excel VBA.
• In the last line of the code, we are trying to show the value assigned to the variable “k” in a message box.

Let us execute the code; we see the following error message.

We have received an error message Run-time error 13 Type mismatch. It is due to the value assigned to the defined variable not being a Boolean value.

Now, look at the following code.

Sub Boolean_Only_TRUE_FALSE()
Dim K As Boolean
K = 1
MsgBox K
End Sub

Instead of a text value now, we have assigned a numerical value, i.e., 1. Let’s execute the code now and see what we get in return.

Even though we assigned a numerical value, we got the Boolean value result, i.e., TRUE.

It looks strange, but the fact is that in VBA Boolean, one is represented as TRUE and 0 as FALSE. Hence, even though we assigned a numerical value of 1 to the defined variable, we still got TRUE.

Similarly, now look at the following code.

Sub Boolean_Only_TRUE_FALSE()
Dim K As Boolean
K = 0
MsgBox K
End Sub

This time we have assigned a numerical value of 0 instead of 1. Let’s execute the code and see what we get in return.

This time we have FALSE because the numerical value 0 is represented as FALSE in Boolean language.

All Numbers are TRUE, and Zero is FALSE in VBA Boolean

Another interesting fact about the Boolean data type is that all the numbers other than 0 are represented as TRUE. Until now, we have seen that one is represented as TRUE and 0 is represented as FALSE.

However, not only one but all numbers other than 0 are considered TRUE, and only 0 is considered FALSE.

For example, look at the following code.

Sub Boolean_Only_TRUE_FALSE()
Dim K As Boolean
K = 100
MsgBox K
End Sub

Here, we have assigned a numerical value of 100, and in return, we get the following result upon executing the code.

Zero is the only number that will be treated as FALSE in the VBA Boolean data type.

VBA Boolean Operator with IF Condition

The VBA Boolean operator is widely used with the IF condition for logical tests in excel to get a desired outcome based on the result value.

For example, look at the following data in the Excel spreadsheet.

We have target and actual values in cells A2 and B2, respectively. In cell C2, we must arrive at the result based on the following logical test.

Logical Test = If the actual is greater than the target value the result should be “Achieved.” If not, then it should be “Not Achieved.”

The following code will do the logical test based on the values in cells A2 and B2 and arrive at the result in cell C2.

Part by Part Code Explanation:

Part 1: We have defined two variables to hold the target and actual value.

Part 2: Defined another variable to hold the logical test result and its variable data type Boolean.

Part 3: Assigned the target and actual values in cells A2 and B2 to the respective variables.

Part 4: Applied the result of the logical test, checking if the actual value is greater than or equal to the target value.

Part 5: Using the obtained Boolean logical result, we have applied the IF condition to arrive at the result value in cell C2. If the Boolean variable “Boolean_Result” is TRUE, then it will insert “Achieved” in result cell C2, and if the Boolean variable “Boolean_Result” is FALSE, then it will insert “Not Achieved” in result cell C2.

Let’s execute the code and see the result in cell C2.

Since the value in cell B2 (actual value) is greater than in cell A2 (target value), the logical test returned a TRUE result. Hence, the IF condition displayed the result in cell C2 as “Achieved.”

VBA Boolean to Integer: We can convert the Boolean to Integer value using the VBA CINT function. For example, look at the following code.

Sub Boolean_Int()
Dim A As Boolean
A = 0
MsgBox A
End Sub

For the variable “A,” we have assigned a number 0, representing FALSE in Boolean language. Next, we display the value in a message box.

Since 0 equals FALSE in Boolean, we get FALSE in the message box.

Similarly, look at the following code.

Sub Boolean_Int()
Dim A As Boolean
A = 0
MsgBox Cint(A)
End Sub

We have used the VBA CINT function to convert the VBA Boolean value to an integer value. Now we will see the following result in a message box.

Since we have used the data type conversion function VBA CINT, the VBA Boolean value has been converted to an integer value. Hence, it shows zero instead of the Boolean result FALSE.

Similarly, we can convert the Boolean to String using the VBA CStr.

Important Things to Note

• VBA Boolean returns a type mismatch error if the value assigned to the VBA Boolean variable is other than TRUE or FALSE.
• The VBA Boolean data type can be used only if you are sure that the output of a test is either TRUE or FALSE.
• Once the Boolean value is converted to an integer, VBA treats the value as an integer and not as a Boolean.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Boolean, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Boolean. Here we explain how to use Boolean data type in Excel VBA with examples and downloadable template. You may learn more from the following articles –

• VBA Do Loop
• VBA ReDim
• VBA Not Equal