EDUCBA

Home VBA VBA Resources VBA Logical Functions VBA Not
 

VBA Not

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

VBA Not

Excel VBA Not

VBA Not is a logical function. NOT is one of the logical functions among others such as VBA IF, VBA OR and VBA AND. All these functions work in the same logical concept but all have different applications. Where VBA Not works mainly on Boolean. Which means we will get the output in the form of TRUE and FALSE. And VBA Not is the opposite of the input we feed. Suppose, we want to compare 2 parameters such as temperature. The temperature of places A and B are 30˚C and we use VBA Not to compare the answer then we will definitely get FALSE, as VBA Not means No or No Equal. Opposite to this, if the temperature of place A is 30˚C and temperature of the place B is 35˚C then using VBA Not here will give us the answer as TRUE as both the values are not equal.

 

 

How to Use Not Function in Excel VBA?

Below are the different examples to use Not function in Excel VBA.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VBA Not Excel Template here – VBA Not Excel Template

Example #1

In this example, we will compare 2 numbers and see what type of output we would get. To execute this, follow the below steps:

Step 1: Open a Module from the Insert menu tab as shown below.

VBA Not Example 1-1

Step 2: Now write the subprocedure in the name of VBA Not or in any other name as per your choice as shown below.

Code:

Sub VBA_Not()

End Sub

VBA Not Example 1-2

Step 3: For numbers, we used to use Integer but as here we will be using NOT with numbers, so we will use String data type.

Code:

Sub VBA_Not()

Dim A As String

End Sub

VBA Not Example 1-3

Step 4: Select the numbers which we want to compare. Here we will be comparing 10 and 20 and see if Number 20 is greater than 10 or not using Not function as shown below.

Code:

Sub VBA_Not()

Dim A As String
A = Not (10 < 20)

End Sub

VBA Not Example 1-4

Step 5: Now to print the output, we will use the message box as shown below.

Code:

Sub VBA_Not()

Dim A As String
A = Not (10 < 20)
MsgBox A

End Sub

VBA Not Example 1-5

Step 6: Run the code by pressing the F5 key or by clicking on the Play button. We will get the message as FALSE.

Output of Example 1-6

We all know that number 20 is greater than 10. But VBA Not is the logical function that always gives the negative answer or opposite answer from the value we feed. So, as per VBA Not, number 20 is NOT greater than 10.

Example #2

There is another way to implement VBA Not. This time we will use If-End If loop to execute VBA Not. For this, follow the below steps:

Step 1: Write the subprocedure of VBA Not as shown below.

Code:

Sub VBA_Not2()

End Sub

VBA Not Example 2-1

Step 2: Again we will use the variable as String here as well.

Code:

Sub VBA_Not2()

Dim A As String

End Sub

Declare Variable Example 2-2

Step 3: Here we will compare whether 2 numbers are equal or not. Let’s compare number 10 with 10 and see what type of answer we will get. For this, open If loop and in condition write if NOT 10 is equal to 10 as shown below.

Code:

Sub VBA_Not2()

Dim A As String
If Not (10 = 10) Then

End Sub

VBA Not Example 2-3

Step 4: If the above condition is satisfied then we will get the answer as TRUE.

Code:

Sub VBA_Not2()

Dim A As String
If Not (10 = 10) Then
A = "TRUE"

End Sub

VBA Not Example 2-4

Step 5: Else give us the answer as FALSE. After that close the loop by End-If.

Code:

Sub VBA_Not2()

Dim A As String
If Not (10 = 10) Then
A = "TRUE"
Else
A = "FALSE"
End If

End Sub

End-If Example 2-5

Step 6: To see the output, we will use a message box with variable A.

Code:

Sub VBA_Not2()

Dim A As String
If Not (10 = 10) Then
A = "TRUE"
Else
A = "FALSE"
End If
MsgBox A

End Sub

Use message box Example 2-6

Step 7: Run the code by pressing the F5 key or by clicking on the Play button. We will get the output message as FALSE.

Output of Example 2-7

Which means although number 10 is equal to 10 but since we used NOT so we are getting the opposite answer here.

Example #3

VBA Not can be used to compare the marks as well. Below we have 2 subjects rows under that a student got 61 in Subject1 and 2 in Subject2. And we will get the result in cell B3.

VBA Not Example 3-1

Follow the below steps:

Step 1: Write the subprocedure of VBA Not as shown below.

Code:

Sub VBA_Not3()

End Sub

VBA Not Example 3-2

Step 2: Define 2 variable for Subject1 and Subject2 using Integer and one of result using String data type as shown below.

Code:

Sub VBA_Not3()

Dim Subject1 As Integer
Dim Subject2 As Integer
Dim result As String

End Sub

VBA Not Example 3-3

Step 3: Now select the range cell which has the number for respective subjects as shown below.

Code:

Sub VBA_Not3()

Dim Subject1 As Integer
Dim Subject2 As Integer
Dim result As String
Subject1 = Range("B1").Value
Subject2 = Range("B2").Value

End Sub

VBA Not Example 3-4

Step 4: Now use If loop with VBA Not as marks in Subject1 is greater than equal to 70 and for subject2 is greater than 30 in If condition, then result is “Pass”.

Code:

Sub VBA_Not3()

Dim Subject1 As Integer
Dim Subject2 As Integer
Dim result As String
Subject1 = Range("B1").Value
Subject2 = Range("B2").Value

If Not (Subject1 >= 70 And Subject2 > 30) Then
result = "Pass"

End Sub

VBA Not Example 3-5

Step 5: Else the student is Fail.

Code:

Sub VBA_Not3()

Dim Subject1 As Integer
Dim Subject2 As Integer
Dim result As String
Subject1 = Range("B1").Value
Subject2 = Range("B2").Value

If Not (Subject1 >= 70 And Subject2 > 30) Then
result = "Pass"

Else
result = "Fail"
End If

End Sub

VBA Not Example 3-6

Step 6: At last, we will select cell B3 to get the result.

Code:

Sub VBA_Not3()

Dim Subject1 As Integer
Dim Subject2 As Integer
Dim result As String
Subject1 = Range("B1").Value
Subject2 = Range("B2").Value

If Not (Subject1 >= 70 And Subject2 > 30) Then
result = "Pass"

Else
result = "Fail"
End If

Range("B3").Value = result

End Sub

VBA Not Example 3-7

Step 7: Run the code by pressing the F5 key or by clicking on the Play button. We will get the result as PASS.

Output of Example 3-8

This means technically student is failed but we have used VBA Not in if the condition so the result is shown as Pass.

Pros of Excel VBA Not

  • It is rarely used but when we need the opposite or negative answer then we can use it here.
  • It is useful in comparing the negative outputs which may lead to giving a positive result.

Things to Remember

  • VBA Not gives the opposite answer to the value we feed. It can be positive or negative, but the answer will be the opposite of that value we give as input.
  • Always use VBA Not where we need to change the output opposite to the actual output.
  • Remember to save the file as macro enable excel format so that this will retain the code for future use as well.
  • Using VBA Not with If-Else loop will be more advisable.

Recommended Articles

This is a guide to VBA Not. Here we discuss how to use Not Function in Excel VBA along with practical examples and downloadable excel template. You may also look at the following articles to learn more –

  1. VBA IF Not
  2. VBA Variable Types
  3. VBA ScreenUpdating
  4. VBA Web Scraping