VBA NOT

What is VBA Not?

In Visual Basic for Applications (VBA), the Not keyword is used as a logical operator to perform a negation operation. It is a keyword that can be used to reverse the logical value of an expression.

For further clarity, see the example below.

Here, a subroutine is created to check if the two values are greater or not. The “Not” function is used to check if the values are NOT greater or NOT lesser. This will return True and False, respectively in the Immediate tab.

How to Use Excel VBA Not Function?

Now you know how to use VBA Not function for your purposes. Follow down to try out some more interesting examples.

Examples

Let us see exciting ways to use the VBA Not function in different ways.

Example #1

See how you can use the VBA Not function to compare and contrast different cases. It can not only be used for VBA Boolean expressions but also for checking conditions.

• Step 1: Begin declaring a subroutine.

• Step 2: Declare and initialize multiple variables as shown.

You can use colons to declare multiple variables in a single line.

• Step 3: Check if a is less than b.

Print the result in the Immediate window using the “Debug.Print” function.

• Step 4: Similarly, compare b and c. Keep in mind that ‘c’ is declared as Null. It will return a Null value irrespective of the function used. Print the result similarly.

• Step 5: Check if d is less than a. Use the Not function to check if d is not greater than a.

Print the result in the Immediate tab.

• Step 6: Use the Not function with any given variable (not the Null variable).

It will perform bitwise comparison instead of negating the value. Print the bitwise comparison value in the immediate tab.

Code:

Sub NOT_Function_For_Many_Cases()
a = 10: b = 12: c = Null: d = 14
chek = Not (a > b)
Debug.Print chek
chek = Not (b > c)
Debug.Print chek
chek = Not (d > a)
Debug.Print chek
chek = Not b
Debug.Print chek
End Sub

• Step 7: Press the Run button. It prints the responses in the Immediate tab, as shown below.

Example #2

In this example, you write a subroutine that accepts a Boolean input from the user (True or False). You can get the inverse of the Boolean function using the VBA Not Function and check if it’s true/false based on the user’s input. It can be a fun game/activity.

• Step 1: You can start by first declaring a subroutine to get the inverse of the boolean value given the input.

• Step 2: Define a boolean variable to accept from the user.

• Step 3: Get the input as True/False from the user using the VBA InputBox function.

You can define the title of the InputBox by declaring it after the initial statement, as shown above.

• Step 4: Convert the input from the user as a Boolean variable using the CBool function.

If the condition is “True,” the If statement is executed, and the output is printed in a message box.

• Step 5: If the above statement is “False,” then the Else block is defined to be executed in that case.

End the If-Else condition in VBA block for this condition.

• Step 6: Declare another If-Else statement block to check if the condition is “True” after inverting the boolean condition using the Not function. For example, Not True will return the result as “False”.

The result is then printed in a Message Box function.

• Step 7: Define an Else block in case the If statement is false. After using the Not function, if the given condition is not True, print a message box in VBA stating that the condition is False.

Code:

Sub ToggleConditionExample()
Dim condition As Boolean
condition = InputBox(“Enter True or False”, “Is it True/False”)
If CBool(condition) Then
MsgBox “Condition is True”, vbInformation
Else
MsgBox “Condition is False”, vbInformation
End If
If CBool(Not condition) Then
MsgBox “Condition is True after toggling”, vbInformation
Else
MsgBox “Condition is False after toggling”, vbInformation
End If
End Sub

• Step 8: Run the above subroutine to view the output. Type “true.”

After clicking ok, you get

Example #3

Suppose you want to create a UserForm to check if a number is greater than three or not. You can customize your VBA UserForm to do so.

• Step 1: Insert a new UserForm by clicking the “UserForm” in the dropdown of the “Insert” option.

• Step 2: Create a blank UserForm and customize it in the “Properties” section.

It is how it currently looks.

• Step 3: Add a text box to the UserForm and customize its properties.

Customize the font size by clicking the box with 3 dots “…” next to the “font” property.

Customize the font looks as you please.

• Step 4: Create a command button and customize its properties.

Now the UserForm looks like this.

• Step 5: Double-click on the command button in order to write your code.

This will create a new private subroutine, which will execute if you click the button defined in the earlier.

• Step 6: Define a boolean variable.

• Step 7: Initialize the condition by checking if the given text box value is greater than 3.

This will either return as True or False. The text box is the text box defined earlier and its value can be used using the VBA Value function.

• Step 8: Check if the condition is false using the VBA Not function.

Using an If-Else condition block, use the VBA Not function in combination with the If statement to check if it is “False”.

Print a MsgBox function showing that the If block has been executed.

• Step 9: If the condition is not “False” declare an Else statement to be executed otherwise.

End the If-Else statement block.

• Step 10: Close the VBA UserForm using the Unload keyword.

This closes the UserForm after the If-Else statement block is executed in the button click subroutine.

Code:

Private Sub Btn_NOT_Click()
Dim condition As Boolean
condition = (TextBox1.Value > 3)
If Not condition Then
MsgBox “Condition is False”
Else
MsgBox “Condition is True”
End If
Unload UserForm1
End Sub

• Step 11: Run the UserForm and see the results.

Otherwise,

Important Things to Note

• Use Not with Boolean values to negate their logical state.
• Apply Not in If or While statements to check the opposite condition.
• Do not use Not directly with non-Boolean VBA data types. It will perform a bitwise operation.
• Use parentheses for clarity when combining Not with other logical operators to ensure correct evaluation.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA NOT. Here we learn how to use the NOT function in Excel VBA code, along with step-by-step examples & points to remember. You can learn more from the following articles –

• VBA Not Equal
• VBA Unprotect Sheet
• VBA Switch Case