Not Equal is an operator in VBA which can also be termed as a negation operator, it is a logical function so the output returned by this function is either true or false, we know that equal operator is “=” this but not equal is “<>” in VBA so whatever the value we get from the equal operator we will get exact opposite value using Not Equal operator.
“Not Equal” Operator in VBA
Usually, we do a logical test “whether something is equal to other things or not.” In some cases, we need to do the “inequality” test as well. The inequality test is nothing but not an equal test. Generally, we say if something is equal to other thing or not, if it is equal, performs some kind of task if not different task. Similarly, using the Inequality test also we can do some kind of operation. “NOT EQUAL” in VBA is represented by the combination of greater than and less than symbols. If both these operators combined, then it becomes not equal symbol i.e., “<>.”
How Not Equal to Works in Excel VBA?
VBA Not equal works exactly opposite to the logic of equal to operator. Equal to operator returns TRUE if the supplied test is satisfied is not, it will return FALSE. For example, if you say 10 = 10, it will return TRUE or else FALSE.
On the other side, “Not Equal” works in the opposite direction. If the supplied logical test in excel is not equal, then only it will return TRUE or else FALSE.
For example, if you say 10 <> 10, it will return FALSE because 10 is equal to 10. In order to get a TRUE result, one value should not be equal to the other value.
Examples of Not Equal to in Excel VBA
Below are the examples of not equal to the operator in Excel VBA.
Example #1
Now we will see how to use VBA Not Equal (<>) sign practically. Look at the below piece of code.
Code:
Sub NotEqual_Example1() Dim k As String k = 100 <> 100 MsgBox k End Sub
Here we are testing whether the number 100 is not equal to the number 100. For sure, we know the number 100 is equal to 100, so the result will be FALSE.
4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
Now I will change the equation.
Code:
Sub NotEqual_Example1() Dim k As String k = 100 <> 99 MsgBox k End Sub
Now the test is whether the number 100 is not equal to 99. So the result will be TRUE.
Example #2
Now we will see how to use this not equal operator in real-time examples. For the demonstration, I have created some data.
We have two values, “Value 1” and “Value 2”.
Now my requirement is if Value 1 is not equal to Value 2, then I need the result as “Different,” or else I need the result as “Same.”
Step 1: Define a variable as an Integer.
Code:
Sub NotEqual_Example2() Dim k As Integer End Sub
Step 2: Open FOR NEXT LOOP from 2 to 9.
Code:
Sub NotEqual_Example2() Dim k As Integer For k = 2 To 9 Next k End Sub
Step 3: Inside the loop, we need to test whether value 1 is not equal to value 2. Since we need our own results, we need to use IF Condition.
Code:
Sub NotEqual_Example2() Dim k As Integer For k = 2 To 9 If Cells(k, 1) <> Cells(k, 2) Then Cells(k, 3).Value = "Different" Else Cells(k, 3).Value = "Same" End If Next k End Sub
Suppose condition tests whether the value 1 is not equal to value 2 or not. If not equal, it will return “Different.” If equal, it will return “Same.”
You can copy and paste the below VBA code.
Copy this code to your module and run using the F5 key or manually. This will return a result like this.
Hide and Unhide Sheets with Not Equal Sign
The various ways of using not equal sing are enormous. We can use this sign to fulfill our needs.
#1 – Hide All Sheets except One Sheet
We have seen this kind of situation many times. We needed to hide all the sheets except the particular sheet.
For example, if you want to hide all sheets except the sheet name “Customer Data,” then you can use this code.
Code:
Sub Hide_All() Dim Ws As Worksheet For Each Ws In ActiveWorkbook.Worksheets If Ws.Name <> "Customer Data" Then Ws.Visible = xlSheetVeryHidden End If Next Ws End Sub
#2 – Unhide All Sheets except One Sheet
Similarly, we can also unhide all sheets except the one sheet. Use the below code to do this.
Code:
Sub Unhide_All() Dim Ws As Worksheet For Each Ws In ActiveWorkbook.Worksheets If Ws.Name <> "Customer Data" Then Ws.Visible = xlSheetVisible End If Next Ws End Sub
You can download this VBA Not Equal to Excel template here – VBA Not Equal Operator Template.
Recommended Articles
This has been a guide to VBA, Not Equal. Here we look at how to use the Logical Operator “Not Equal to” in VBA along with practical examples and downloadable excel sheets. Below are some useful VBA articles related –
- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion