VBA Not

Not Function in Excel VBA

Logical functions are useful for the calculations which require multiple conditions or criteria’s to test. In our earlier articles, we have seen “VBA IF”, “VBA OR”, and “VBA AND” conditions. In this article, we will discuss the “VBA NOT” with IF function. In order to understand it first, we need to look at the “VBA NOT” function. Read the below section to understand the Excel VBA Not Function.

Examples of VBA Not Function in Excel

Below are some of the examples to understand the working of this function.

Example #1 – NOT Function in VBA

NOT function is available with VBA too and it works exactly the same as the excel NOT function. For example, look at the below set of VBA code.

Code:

Sub NOT_Example1()

Dim k As String

k = Not (45 = 45)

MsgBox k

End Sub

In the above code, we have declared the variable “k” as a string.

Dim k As String

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

k = Not (45 = 45)

Next, we have assigned the value return by the NOT function to the variable “k” in the message box.

MsgBox k

Run the code and see what the result is.

Example #2 – VBA NOT with IF Function

As I told 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. With IF we can have our own results instead of default results of TRUE or FALSE.

Take the same example code from above and we will apply NOT with 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

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 value as 45 = 45 so we will get the answer as follows

Example #3 – Advanced NOT in VBA

NOT function is best utilized with IF function in excel. We can use this function to hide all the sheets except the one particular sheet.

We have various sheets as follows in our excel.

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

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

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

Like how we can also unhide sheets as well. 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

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

Recommended Articles

This has been a guide to VBA Not. Here we discuss the working of VBA Not Function and also how to use VBA Not with If Function along with practical examples and downloadable excel template. Below are some useful articles related to VBA –

• IF NOT in VBA
• VBA Resize | Examples
• CDate in Excel VBA
• How to Enable Solver in Excel & VBA?
• VBA ScreenUpdating
• For Each Loop in Excel VBA
• GoTo in Excel VBA
• Protect Sheet in Excel VBA
• Use Integer Data Type in VBA