WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Not Function

VBA Not Function

Excel VBA Not Function

Logical functions are useful for the calculations which require multiple conditions or criteria 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 excel. In order to understand it first, we need to look at the “VBA NOT” function.

vba-not

Examples

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

Example #1

NOT function is available with VBA too, and it works exactly the same as the excel 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

vba not example 1.1

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

Dim k As String

Next, we have assigned the value through the 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.

vba not example 1.2

Example #2 – NOT with IF Function

As I told in one of the earlier articles, “IF with other logical functions are the best pairs in excel.”

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Similarly, NOT with IF is useful in many ways. With IF, we can have our own results instead of the 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

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

vba not example 2.2

Example #3 – Advanced NOT

NOT function is best utilized with the IF function. We can use this function to hide all the sheets except the 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 the one particular sheet by changing the sheet name to your sheet name.

Like how we can also unhide sheets in excel 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

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

Recommended Articles

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

  • VBA If Else
  • IF NOT in VBA
  • For Each Loop in Excel VBA
  • GoTo in VBA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Not Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More