Comparison operators are used to compare expressions. They may return True, False or Null. Below is a table showing are the basic comparison operators in VBA.written in the order of preference when evaluated: Operator | Explanation (True if) |
---|
= | Equality | A < B | A less than B | A < = B | A less than or equal to B | A > B | A greater than B | > | Inequality | < | Less than | > | Greater than | < = | Less | A > = B | A greater than or equal to B | A > = BA | Greater than or equal to B | A < > B | | Like and IsA not equal to B |
|
Info |
---|
The operators Like and Is are described further down, as they have some special functionalities. |
To learn when the compartion operators return Null, read the offical documentation on the comparison operators. Here, you will also find more detailed information regarding the actual comparisons done when using the operators. This includes outcome of comparing different variant types, such as a string and a double. Let's look at some exampels: Code Block |
---|
| Dim MyResult, Var1, Var2 ' Variables are declared as type "Variant"
Dim str1 As String
Dim num1 As Integer
MyResult = (10 < 5) ' Returns False.
MyResult = (1 <> 2) ' Returns True.
MyResult = ("5" > "4") ' Returns True.
MyResult = (4 = 4) ' Returns FalseTrue.
MyResult = (15 <>= 2)5 ' Returns True. Notice that we don' Returns Truet have to include the brackets as in the line above.
MyResult = ("5" > "4") = 2 ' Returns TrueFalse.
MyResult = ("hi" > "hello") ' Returns True, since ("i" > "e").
MyResult = ("hi" > "Hi") ' Returns True, since the ASCII values for lowercase letters are higher than for uppercase letters.
Var1 = 5: Var2 = Empty
MyResult = (Var1 > Var2) ' Returns True, since "Empty" is evaluated as 0.
Var1 = 5: Var2 = Empty
MyResult = (Var1 > Var2) ' Returns False, since "Empty" is evaluated as 0.
Var1 = 0: Var2 = Empty
MyResult = (Var1 = Var2) ' Returns True, since "Empty" is evaluated as 0.
Var1 = "1": Var2 = 4
MyResult = (Var1 > Var2) ' Returns True, since a "Variant of type string" is always greater than a "Variant of type numeric".
str1 = "1": num1 = 4
MyResult = (str1 > num1) ' Returns False, since the "string" may be converted to a number, thus using a numerical comparison.
str1 = "a": num1 = 4
MyResult = (str1 > num1) ' Returns an error of type "Type missmatch", this because the "string" can not be converted to a number.
|
As we can see, you may compare almost anything in VBA. Knowing what the outcome of each comparison is, will give you a great advantage when coding.
In addition to the operators above, there are two more comparison operators, Like and Is. They have some special functionalities, and were therefore not described alongside the others. The Is-operator is used to combine two reference variables. If both objects refer to the same object, the comparison returns True; if they do not, it returns False. An example is shown below: Code Block |
---|
| Dim A As ThisWorkbook ' A is declared as a valid object in order for "Is" to work.
Dim B, C, myResult
Set C = A ' C refers to object A.
Set B = A ' B also refers to object A.
myResult = C Is B ' Returns True, since both variabels refer to the same object. |
The Like-operator is used to compare strings. Its syntax is "result = string Like pattern", and it returns True if the string matches the pattern. If not, it returns False, and if either is Null, it returns Null. Characters in pattern | Matches in string |
---|
? | Any single character | * | Zero or more characters. | # | Any single digit (0-9). | [ charlist ] | Any single character in charlist. | [ !charlist ] | Any single character not in charlist. |
Examples showing the use of Like: Code Block |
---|
| Dim myResult
myResult = "I" Like "[A-Z]" ' Returns True, since "I" is between "A" and "Z"
myResult = "i" Like "[A-Z]" ' Returns False, since lowercase characters are places elsewhere in the ASCII-system
myResult = "a2a" Like "a#a" ' Returns True, since the pattern requires any single digit between the a's.
myResult = "a5" Like "a #" ' Returns False, since the pattern has a space between "a" and "#", which is not in the string
myResult = "ab" Like "a*b" ' Returns True, since the * requires zero or more characters.
myResult = "a*b" Like "a[*]b" ' Returns True, since the pattern requires a single "*" between "a" and "b".
myResult = "a**b" Like "a[*]b" ' Returns False.
myResult = "aBBBa" Like "a*a" ' Returns True. |
|