VBA Boolean

VBA Boolean Operation

In Excel when we compare two cell contents or numbers by applying equal sign between them, we get output in TRUE or FALSE format. Which means values which we are comparing may be equal or may not be equal. In a similar manner, we have Boolean in VBA Excel. Boolean also gives the output in the form of TRUE or FALSE. Boolean is a form of data type which can only contain either TRUE or FALSE. When we give some input to Boolean and if the test becomes TRUE then we get an answer as TRUE or we get FALSE.

How to Use Boolean in VBA Excel?

Let’s see the examples of Boolean in Excel VBA.

Example #1 – VBA Boolean

Let’s see a very simple example where we will see how to apply Boolean while comparing some.

Step 1: For that go to the VBA window and click on the Insert menu tab. From the list select a Module as shown below.

Step 2: Now in the opened module, write the sub category of VBA Boolean. We can choose to write any name of subprocedure here.

Code:

Sub VBABoolean1()

End Sub

Step 3: Now define a Dim with any name, let’ say an A and assign the variable A as Boolean as shown below.

Code:

Sub VBABoolean1()

Dim A As Boolean

End Sub

Step 4: Let’s consider two numbers, 1 and 2. And now we will test with the help of Boolean whether 2 is greater than 1 or not. So in the next line, write a mathematical expression of 1<2 under defined variable A.

Code:

Sub VBABoolean1()

Dim A As Boolean
A = 1 < 2

End Sub

Step 5: Now assign a message box to variable A to see what outcome will appear when we run the code.

Code:

Sub VBABoolean1()

Dim A As Boolean
A = 1 < 2

MsgBox A
End Sub

Step 6: For running the code, click on the Play button which is below the menu bar as shown below. As we can see, we got the output as TRUE which means 1 is less than 2.

Step 7: If we change the sign as 1 is greater than 2 as shown below. What would we get?

Code:

Sub VBABoolean1()

Dim A As Boolean
A = 1 > 2

MsgBox A
End Sub

Step 8: To test this, again run the code. We will see, Boolean has given FALSE as 1 cannot be greater than 2.

Example #2 – VBA Boolean

In this example, we will test if Boolean works for text or not. To apply this, we need a module.

Step 1: Open a new Module and give it a subcategory in the name of VBA Boolean or any name as per your choice.

Sub VBABoolean2()

End Sub

Step 2: Define a variable A and assign a Boolean function to it.

Code:

Sub VBABoolean2()

Dim A As Boolean

End Sub

Step 3: Now assign a text to defined variable A. Let’s say that text is VBA Boolean. And it should be under inverted commas.

Code:

Sub VBABoolean2()

Dim A As Boolean
A = "VBA  Boolean"

End Sub

Step 4: At last, give that variable A in a message box to see the output as shown below.

Code:

Sub VBABoolean2()

Dim A As Boolean
A = "VBA Boolean"
MsgBox A

End Sub

Step 5: Once done, run the code. We will get an error message as “Run-time error 12 – Type Mismatch” which means that Boolean doesn’t support input as Text.

Example #3 – VBA Boolean

In this example, we will see, if Boolean works for a number without any comparison.

Step 1: Open a new module and give it a subcategory of VBA Boolean as shown below.

Code:

Sub VBABoolean3()

End Sub

Step 2: Now define a Dim A variable as Boolean as shown below.

Code:

Sub VBABoolean3()

Dim A As Boolean

End Sub

Step 3: As discussed above, we will give the variable A a number. Let’s consider that number is 10.

Code:

Sub VBABoolean3()

Dim A As Boolean
A = 10

End Sub

Step 4: After that, select the function msgbox and assign it to variable A. This will help us print the value with the help of Boolean.

Code:

Sub VBABoolean3()

Dim A As Boolean
A = 10
MsgBox A

End Sub

Step 5: Now run the code. We will get the message with the message as TRUE.

Step 6: Now let’s change that value to 0 from 10.

Code:

Sub VBABoolean3()

Dim A As Boolean
A = 0
MsgBox A

End Sub

Step 7: Now run the code again. We will see the message box has returned the output as FALSE. In Boolean, any value greater than 0 will always give the returned answer as TRUE whereas the 0 will return the value as FALSE.

Example #4 – VBA Boolean

In this example, we will see how Greater Than-Equal to (>=) or Less Than-Equal to (<=) works in Boolean. This will be done with the help for If-End If loop.

Step 1: Now, open a new module and write the subcategory of VBA Boolean as shown below.

Code:

Sub VBABoolean4()

End Sub

Step 2: Now define 2 variable with any name as per your choice. Here, we have selected A and B as Integer. Which means both will store numeric values.

Code:

Sub VBABoolean4()

Dim A As Integer
Dim B As Integer

End Sub

Step 3: Now assign any values to variable A and B. Here we have chosen number 1 and 2 for variable A and B as shown below.

Code:

Sub VBABoolean4()

Dim A As Integer
Dim B As Integer

A = 1
B = 2

End Sub

Step 4: As stated above, we will use the If-Else loop. Now open the If-End If loop where we will write the criteria.

Code:

Sub VBABoolean4()

Dim A As Integer
Dim B As Integer

A = 1
B = 2

If

End If

End Sub

Step 5: Now write the code, If A is less than equal to B then show us the message as TRUE, else show us the message as FALSE.

Code:

Sub VBABoolean4()

Dim A As Integer
Dim B As Integer

A = 1
B = 2

If A <= B Then
    MsgBox True
Else
    MsgBox False

End If

End Sub

Step 6: Now compile the above code step-by-step and then run if no error found. We will see, the message box has the message as TRUE which means value stored in variable A (which is 1) is less than the value stored in variable B (which is 2).

Pros of VBA Boolean

• It is quite useful when we are want to implement the process flow following TRUE and FALSE for each iteration.
• Conditional comparison with the help of any kind of loop can easily be implemented.

Cons of VBA Boolean

• Only numbers can be used in Boolean. It will show the error if used for text as seen in example-2.

Things to Remember

• Using Boolean with any loop will give users a better output. And comparison can be done in various ways.
• Boolean is a kind of cell comparison formula used in excel, but it only compares the numerical or mathematical values.
• Always save the file in macro enable format to retain the written code to be used in the future.

Recommended Articles

This is a guide to VBA Boolean. Here we discuss how to use Boolean in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA IsNumeric
2. VBA Get Cell Value
3. VBA XML
4. VBA UCASE