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 ORVBA OROr is a logical function in programming languages, and we have an OR function in VBA. The result given by this function is either true or false. It is used for two or many conditions together and provides true result when either of the conditions is returned true.,” and “VBA ANDVBA ANDVBA AND is an inbuilt logical function and a logical operator. Only when all of the conditions specified in this function are met , it returns the output as true. In contrast, if any of the conditions fails, the output is false.” conditions. In this article, we will discuss the “VBA NOT” with IF function in excelIF Function In ExcelIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria. . In order to understand it first, we need to look at the “VBA NOT” function.
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 codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task..
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 the NOT functionNOT FunctionNOT Excel function is a logical function in Excel that is also known as a negation function and it negates the value returned by a function or the value returned by another logical 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.
Run the code and see what the result is.
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.”
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.
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
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.
Below is the sample code to hide all sheets except one particular sheet.
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 hideVBA Code To HideVBA columns need to be hidden for the sheet to convey only the relevant information. To hide a column, simply set the property value to 'TRUE'; to unhide the column, set the property value to 'FALSE'. 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 excelUnhide Sheets In ExcelThere are different methods to Unhide Sheets in Excel as per the need to unhide all, all except one, multiple, or a particular worksheet. You can use Right Click, Excel Shortcut Key, or write a VBA code in Excel. as well. The below code will unhide all the sheets except the sheet name “Data Sheet.”
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.”
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
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 –