Table Of Contents
VBA Like Operator
Like is an operator in VBA. A comparison operator compares a given string as an argument in a set of strings and matches the pattern. If the pattern matches, the result obtained is "True," and if the pattern does not match, then the result obtained is "False." It is an inbuilt operator in VBA.
LIKE operator is the most underused operator despite its wonderful usage. We have not seen many people who use this operator to a full extent in their coding. One may be the one who does not use this operator quite often. The VBA LIKE operator allows us to match the pattern of the string against the full string. We can compare two strings against the pattern using the VBA LIKE operator. We can check whether the string contains a substring in VBA or whether the string contains any specific format. If the pattern matches the string, then the VBA LIKE operator returns TRUE or else FALSE.
While matching strings, we need to use wildcard characters to the pattern we specify. Below are the wildcards we use in VBA LIKE operator
- Question Mark (?): One may use it to match any one character from the string. For example, if we have a string "CAT," and the pattern is "C?T," then VBA LIKE operator returns TRUE. On the other hand, if the string is "CATCH and the patterns are "C?T," then VBA LIKE operator returns FALSE.
- Asterisk (*): This matches zero or more characters. For example, if the string is โGood,โ and the pattern is โG**d,โ VBA LIKE operator returns TRUE.
- Brackets (): This matches any single character specified in the brackets.
- : This matches any single character in the range Char-Char.
- : This matches any single character not in the list.
- : This matches any single character not in the range Char-Char.
Examples of VBA LIKE Operator
Let us see some of the examples of VBA LIKE operators now.
Example #1 - With Question Mark
Code:
Sub QuestionMark_Example1() Dim k As String k = "Good" If k Like "Go?d" Then MsgBox "Yes" Else MsgBox "No" End If End Sub
In the above code, we have supplied the string as โGood,โ and the pattern is โGo?d.โ Since the question mark can match a single character, it will show the result as โYes.โ
Now, we will change the string to "Good Morning."
Code:
Sub QuestionMark_Example1() Dim k As String k = "Good Morning" If k Like "Go?d" Then MsgBox "Yes" Else MsgBox "No" End If End Sub
In this case, it will show โNoโ because we have added one more word to the string, i.e., Morning. To match any number of characters, we need to use the asterisk.
Example #2 - With Asterisk
Code:
Sub QuestionMark_Example2() Dim k As String k = "Good Morning" If k Like "*Good*" Then MsgBox "Yes" Else MsgBox "No" End If End Sub
In the above example, we have added two asterisks before and after the character "*Good*." It will match the word "Good" in the string "Good Morning" and return "Yes."
Example #3 - With Brackets
Code:
Sub QuestionMark_Example3() Dim k As String k = "Good Morning" If k Like "**" Then MsgBox "Yes" Else MsgBox "No" End If End Sub
The above code matches the single letter mentioned in the bracket "M" and returns the result as "Yes."
Example #4 - With Brackets & Alphabets
Code:
Sub QuestionMark_Example4() Dim k As String k = "Good Morning" If k Like "**" Then MsgBox "Yes" Else MsgBox "No" End If End Sub
In the above, we have mentioned the characters to match from A to D.
It will return "No" because there are no characters from A to D in the string "Good Morning."
Now, we will change the pattern to .
Code:
Sub QuestionMark_Example4() Dim k As String k = "Good Morning" If k Like "**" Then MsgBox "Yes" Else MsgBox "No" End If End Sub
It will return "Yes" because from A to H, we have a character "G" in the string "Good Morning."
Like this, we can use the VBA "LIKE" operator to match any string from the pattern with wildcard characters.