VBA Not Function

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Excel VBA Not Function

Logical functions are useful for calculations that require multiple conditions or criteria to test. In our earlier articles, we have seen "VBA IF," “VBA OR,” and “VBA AND” conditions. This article will discuss the VBA NOT with the IF function in excel. But, first, we need to look at the VBA NOT function to understand it.

Table of contents
vba-not

Examples

Example #1

The NOT function is available with VBA too. It works the same as the Excel function. For example, look at the below set of VBA codes.

Code:

Sub NOT_Example1()

Dim k As String

k = Not (45 = 45)

MsgBox k

End Sub
vba not example 1.1

We have declared the variable "k" as a string in the above code.

Dim k As String

Next, we have assigned the value through the NOT function. Does it NOT function to say whether the number 45 equals 45 or not?

k = Not (45 = 45)

Next, we have assigned the value returned by the NOT function to the variable "k" in the message box.

MsgBox k

Run the code and see what the result is.

vba not example 1.2

Example #2 - NOT with IF Function

As we said in one of the earlier articles, "IF with other logical functions are the best pairs in Excel."

Similarly, NOT with IF is useful in many ways. For example, with IF, we can have our results instead of the default results of TRUE or FALSE.

Take the same example code above. Again, we will apply NOT with the IF function.

Code:

Sub NOT_Example2()

Dim k As String

If Not (45 = 45) Then
k = "Test result is TRUE"
Else
k = "Test result is FALSE"
End If

MsgBox k

End Sub
vba not example 2.1

In the above code, we have altered the default results from “Test result is FALSE” and “Test result is TRUE.” If the supplied logical test is true, it will return “Test result is FALSE,” and if the supplied logical test is false, it will return. “Test result is TRUE.”

In the above code, we have a value of 45 = 45, so we will get the answer as follows.

vba not example 2.2

Example #3 - Advanced NOT

The NOT function is utilized best with the IF function. For example, we can use this function to hide all the sheets except one particular sheet.

We have various sheets, as follows in our Excel.

example 3.1

Below is the sample code to hide all sheets except one particular sheet.

Code:

Sub NOT_Example3()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets
If Not (Ws.Name = "Data Sheet") Then
Ws.Visible = xlSheetVeryHideen
End If
Next Ws

End Sub
example 3.2

The above code hides all the worksheets except the worksheet “Data Sheet.”

example 3.3

You can use this VBA code to hide all the sheets except one particular sheet by changing the sheet name to your sheet name.

For how we can also unhide sheets in excel. The below code will unhide all the sheets except the sheet name "Data Sheet."

Code:

Sub NOT_Example4()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets

If Not (Ws.Name = "Data Sheet") Then
Ws.Visible = xlSheetVisible

End If
Next Ws

End Sub
example 3.4

The below code will unhide only the sheet name “Data Sheet.”

Code:

Sub NOT_Example3()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets
If Not (Ws.Name <> "Data Sheet") Then
Ws.Visible = xlSheetVisible
End If
Next Ws

End Sub
example 3.5