IF NOT in 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 IF NOT” function. Before introducing VBA IF NOT function let me show you about VBA NOT function first.
What is NOT Function in VBA?
“NOT” is one of the logical functions we have with excel & VBA. All the logical functions require logical test to be conducted and return TRUE if the logical test is correct, if the logical test is not correct then it will return FALSE as the result.
But “VBA NOT” is completely opposite of the other logical function. I would say this is the inverse function of logical functions.
“VBA NOT” function returns “FALSE” if the logical test is correct and if the logical test is not correct it will return “TRUE”. Now, look at the syntax of “VBA NOT” function.
This is very simple, we need to provide the logical test. NOT function evaluates the test and returns the result.
Examples of NOT & IF Function in VBA?
Below are the examples of using IF and NOT function in excel VBA.
Take a look at the below code for an example.
Sub NOT_Example() Dim k As String k = Not (100 = 100) MsgBox k End Sub
In the above code, I have declared the variable as String.
Dim k As String
Then for this variable, I have assigned the NOT function with the logical test as 100 = 100.
k = Not (100 = 100)
Then I have written the code to show the result in the VBA message box. MsgBox k
Now I will execute the code and see the result.
We got the result as “FALSE”.
Now look back at the logical test. We have provided the logical test as 100 = 100 which is generally TRUE since we had given the NOT function we got the result as FALSE. As I told, in the beginning, it gives inverse results compared to other logical functions. Since 100 is equal to 100 it has returned the result as FALSE.
Now, look at one more example with different numbers.
Sub NOT_Example() Dim k As String k = Not (85 = 148) MsgBox k End Sub
Code is the same only thing I have changed here is I have changed the logical test from 100 = 100 to 85 = 148.
Now I will run the code and see what the result is.
This time we got the result as TRUE. Now examine the logical test.
k = Not (85 = 148)
We all know 85 is not equal to the number 148. Since it is not equal NOT function has returned the result as TRUE.
NOT with IF Condition:
In excel or VBA, any logical conditions are not complete without the combination IF condition. Using IF condition we can do much more things beyond default TRUE or FALSE. For example, in the above examples we got default results of FALSE & TRUE, instead of that we can modify the result in our own words.
Look at the below code.
Sub NOT_Example2() Dim Number1 As String Dim Number2 As String Number1 = 100 Number2 = 100 If Not (Number1 = Number2) Then MsgBox "Number 1 is not equal to Number 2" Else MsgBox "Number 1 is equal to Number 2" End If End Sub
I have declared two variables.
Dim Number1 As String & Dim Number2 As String
For these two variables, I have assigned the numbers as 100 & 100 respectively.
Number1 = 100 & Number2 = 100
Then for NOT function, I have attached IF condition to alter the default TRUE or FALSE. If the result of the NOT function is TRUE then my result will be as follows.
MsgBox “Number 1 is not equal to Number 2”
If the NOT function result is FALSE then my result is as follows.
MsgBox “Number 1 is equal to Number 2”
Now I will run the code and see what happens.
We got the result as “Number 1 is equal to Number 2”, so NOT function has returned the FALSE result to IF condition so IF condition returned this result.
Like this, we can use IF condition to do the inverse test.
This has been a guide to VBA IF NOT. Here we discuss how to use IF & NOT function in excel VBA along with examples and downloadable excel templates. Below are some useful articles related to VBA –
- VBA Replace String Examples
- Use of VBA CLng
- Excel VBA VARTYPE
- VBA If Else Statement
- VBA AND Statement
- VBA OR Statement
- VBA IF OR Statement