VBA IF Not

VBA IF Not

In any programming language, we have logical operators AND OR and NOT. Every operator has a specific function to do. AND combines two or more statements and return values true if every one of the statements is true where is in OR operator if any one of the statements is true the value is true. The NOT operator is a different thing. NOT operator negates the given statement. We use these logical operators with IF statements in our day to day data analysis. If we use IF NOT statement in VBA consider this as an inverse function.

We have discussed above that we use the logical operators with if statements. In this article, we will use NOT operator with the if statement. I said earlier that IF NOT statement in VBA is also considered as an inverse function. Why is that because if the condition is true it returns false and if the condition is false it returns true. Have a look below,

IF A>B equals to IF NOT B>A

Both the if statements above are identical how? In the first statement if A is greater than B then the next statement is executed and in the next, if not statement means if B is not greater than A which in itself means A is greater than B.

The most simple way to understand IF NOT statement should be as follows:

If True Then
If NOT false Then

Or we can say that

If False then
IF NOT True then

Both the statements in Comparison 1 and Comparison 2 are identical to each other.

Let’s use IF NOT function in few examples which will make it more clearly for us.

How to Use Excel VBA IF Not?

We will learn how to use a VBA IF Not with few examples in excel.

Example #1 – VBA IF Not

Follow the below steps to use IF NOT in Excel VBA.

For example, I have two values in sheet 1 in cell A1 and B1. Have a look at them below,

What I want to do is compare these two values which one is greater using IF NOT statement in VBA.

Step 1: Go to the developer’s tab and then click on Visual Basic to open the VB Editor.

Step 2: Insert a module from the insert tab in the VB Editor. Double click on the module we just inserted to open another window where we are going to write our code.

Step 3: Every VBA code starts with a sub-function as below,

Code:

Sub Sample()

End Sub

Step 4: Declare two variables as integers which will store our values from cell A1 and B1.

Code:

Sub Sample()

Dim A, B As Integer

End Sub

Step 5: To assign values to these variables we need to activate the worksheet first by the following code.

Code:

Sub Sample()

Dim A, B As Integer
Worksheets("Sheet1").Activate

End Sub

Step 6: Now we will assign these variables the values of A1 and B1.

Code:

Sub Sample()

Dim A, B As Integer
Worksheets("Sheet1").Activate
A = Range("A1")
B = Range("B1")

End Sub

Step 7: Let us compare both the variables using IF NOT statement by the following code,

Code:

Sub Sample()

Dim A, B As Integer
Worksheets("Sheet1").Activate
A = Range("A1")
B = Range("B1")
If Not A > B Then
MsgBox "B is greater than A"
Else
MsgBox "A is greater than B"
End If

End Sub

Step 8: Run the above code from the run button in VBA or we can press the F5 button to do the same. We will get the following result.

Step 9: Let us inverse the values of A and B and again run the code to see the following result.

In the first execution, A was greater than B but we compared IF NOT A>B, Initially, the condition was true so it displayed the result for False statement i.e. A is greater than B and vice versa for execution second.

Example #2 – VBA IF Not

In the first example we compared integers, let us compare strings in this example with IF NOT statement in VBA. In the same sheet1, we have two strings in cell A3 and B3 as follows,

Let us compare both the strings using IF NOT Statement.

Step 1: To open VB Editor first click on Developer’s Tab and then click on Visual Basic.

Step 2: In the same module, we inserted above double click on it to start writing the second code.

Step 3: Declare a sub-function below the code we wrote first.

Code:

Sub Sample1()

End Sub

Step 4: Declare two variables as a string that will store our values from cell A3 and B3.

Code:

Sub Sample1()

Dim A, B As String

End Sub

Step 5: To assign values to these variables we need to activate the worksheet first by the following code to use its properties.

Code:

Sub Sample1()

Dim A, B As String
Worksheets("Sheet1").Activate

End Sub

Step 6: Now we will assign these variables the values of A3 and B3.

Code:

Sub Sample1()

Dim A, B As String
Worksheets("Sheet1").Activate
A = Range("A3")
B = Range("B3")

End Sub

Step 7: Let us compare both the variables using IF NOT statement by the starting the if statement as follows,

Code:

Sub Sample1()

Dim A, B As String
Worksheets("Sheet1").Activate
A = Range("A3")
B = Range("B3")
If Not A = B Then

End Sub

Step 8: If A = B condition is true then the above statement will negate it and return the value as false.

Code:

Sub Sample1()

Dim A, B As String
Worksheets("Sheet1").Activate
A = Range("A3")
B = Range("B3")
If Not A = B Then
MsgBox "Both the strings are not same"

End Sub

Step 9: If both the strings are same i.e. if the result is returned as true display the following message,

Code:

Sub Sample1()

Dim A, B As String
Worksheets("Sheet1").Activate
A = Range("A3")
B = Range("B3")
If Not A = B Then
MsgBox "Both the strings are not same"
Else
MsgBox "Both the Strings are same"
End If

End Sub

Step 10: Now let us run the above code by pressing the F5 button or from the run button given. Once we run the code we get the following result.

Step 11: Now let us make both the stings in A3 and B3 cell same to see the different result when we run the same code.

In the first execution A was not similar to B but we compared IF NOT A=B, Initially the condition was true so it displayed the result for false statement i.e. both the strings are not same and when both of the strings were same we get the different message as both the strings are same.

Things to Remember

• IF NOT is a comparison statement.
• IF NOT negates the value of the condition i.e. if a condition is true it returns false and vice versa.
• IF NOT statement is basically an inverse function.

Recommended Articles

This has been a guide to VBA If Not. Here we have discussed how to use Excel VBA If Not along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Active Cell
2. VBA RGB
3. VBA Transpose
4. VBA Not