VBA Like

What is Excel VBA Like Operator?

The VBA Like operator compares strings to determine if they match a specified pattern. It allows you to perform wildcard-based searches within strings, making it a powerful tool for string manipulation and text matching in VBA. This operator supports wildcard characters such as “?” and “*,” representing single and multiple characters.

Let us look at an example where we’ll use the VBA Like operator to validate email addresses. Here, we have a subroutine named “ValidateEmail” that aims to validate the format of an email address stored in the “email” variable. The condition checks if the “email” variable matches the pattern “@.,” where “” matches zero or more characters.

VBA Like Operator Example 1

This pattern verifies that the email contains an “@” symbol and at least one dot (for the domain extension). If the condition is true, a message box displays “Email is valid!”; otherwise, it shows “Invalid email format.”

VBA Like Operator Example 1-1
Key Takeaways
  • The VBA Like operator is used for pattern-based string matching. It supports wildcards like “?” and “*” representing single and multiple characters.
  • The Like operator is case-insensitive by default. You can use brackets “[ ]” to specify character ranges and sets in patterns.
  • Debug your code if the VBA Like operator is not behaving as expected, and consider using custom comparisons for case sensitivity.
  • The VBA Like operator can be useful for tasks like data validation, text parsing, and filtering data based on patterns.

How To Use VBA Like Operator? 

Using the VBA Like operator involves the following steps:

Step 1: In the Excel workbook, press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

How to Use VBA Like Operator - Step 1

Step 2: Inside the VBA editor, go to Insert > Module to insert a new module where you can write your VBA code.

How to Use VBA Like Operator - Step 2

Step 3: If your code requires variables, use the Dim statement to declare them. Variables are used to hold the values you want to compare.

For example:

Dim firstName As String

Dim lastName As String

Step 4: Within your VBA code, use the VBA Like operator to compare strings. Define the pattern you want to match, incorporating wildcard excel characters such as “?” (matches a single character) and “*” (matches zero or more characters).

For example:

If firstName Like “J*n” Then

Step 5: Typically, you use the VBA Like operator within conditional statements like If…Then…Else to determine whether the comparison results in a match or not.

For example:

If firstName Like “J*n” Then

        MsgBox “First name matches the pattern!”

    Else

        MsgBox “First name does not match the pattern.”

    End If

Step 6: Run your VBA code to execute the comparison using the VBA Like operator. Your code will produce the desired output or take specific actions depending on the condition.


Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Example #1 – With Question Mark

In this example, the “?” wildcard matches any single character, so “a?ple” matches “apple.”

Step 1: In the new module, first, we define a VBA subroutine named “Example1.”

VBA Like Example 1 - Step 1

Step 2: Next, we declare a string variable named “str” using the “Dim” statement.

VBA Like Example 1 - Step 2

Step 3: Here, we assign the string “apple” to the variable “str.”

VBA Like Example 1 - Step 3

Step 4: This line begins an “If” statement in VBA, which is used for conditional execution.

The condition being checked is whether the value of the “str” variable matches the pattern “a?ple.”

“a?ple” is a pattern where “?” represents any single character.

VBA Like Example 1 - Step 4

Step 5: If the condition in Step 4 is true (a match is found), this line displays a message box with the text “Match found!”

VBA Like Example 1 - Step 5

Step 6: If the condition in Step 4 is false (no match is found), this line indicates that the code should execute the following block of code (in this case, the MsgBox in the next step).

VBA Like Example 1 - Step 6

Step 7: If the condition in Step 4 is false, this line exhibits a message box containing the text “No match found!”

VBA Like Example 1 - Step 7

Step 8: Save the VBA macro and click on run. 

When executed, the code will display a message box with the text “Match found!” because the string “apple” matches the pattern “a?ple” where “?” represents any single character.

VBA Like Example 1 - Step 8

Here is the full code:

Sub Example1()

    Dim str As String

    str = “apple”

        If str Like “a?ple” Then

        MsgBox “Match found!”

    Else

        MsgBox “No match found.”

    End If

End Sub

Example #2 – With Asterisk

In this example, the “” wildcard matches zero or more characters. So, “bnana” matches “banana.”

Step 1: In the new module, we define a VBA subroutine named “Example2.”

VBA Like Example 2 - Step 1

Step 2: Subsequently, we utilize the “Dim” statement to declare a string variable called “str.”

VBA Like Example 2 - Step 2

Step 3: Here, we set the variable “str” to contain the string “banana.”

VBA Like Example 2 - Step 3

Step 4: This line initiates an “If” statement, which serves the purpose of executing code conditionally.

It assesses whether the content of the “str” variable conforms to the “bnana” pattern, where “” signifies the potential presence of zero or more characters.

VBA Like Example 2 - Step 4

Step 5: If the condition in Step 4 is true (a match is found), this line displays a message box with the text “Match found!” 

VBA Like Example 2 - Step 5

Step 6: If the condition in Step 4 is false (no match is found), this line indicates that the code should execute the following block of code (in this case, the MsgBox in the next step).

VBA Like Example 2 - Step 6

Step 7: In the event that the condition in Step 4 is false, this line exhibits a message box containing the text “No match found!”

VBA Like Example 2 - Step 7

Step 8: Save the macro and run the code. When executed, the code will display a message box with the text “Match found!” because the string “banana” matches the pattern “bnana” where “” represents zero or more characters, allowing any characters to appear after “b.”

VBA Like Example 2 - Step 8

Here is the full code:

Sub Example2()

    Dim str As String

    str = “banana”

        If str Like “b*nana” Then

        MsgBox “Match found!”

    Else

        MsgBox “No match found.”

    End If

End Sub

Example #3 – With Brackets []

In this example, “[1-5]” matches any single digit from 1 to 5, so “[1-5]2345” matches “12345.”

Step 1: In the new module, first, we define a VBA subroutine named “Example3.”

VBA Like Example 3 - Step 1

Step 2: We establish a string variable denoted as “str.”

VBA Like Example 3 - Step 2

Step 3: We allocate the string “12345” to the variable labeled as “str.”

VBA Like Example 3 - Step 3

Step 4: This line initiates an “If” statement featuring a specific condition.

This condition assesses if the content of “str” aligns with the “[1-5]2345” pattern, where “[1-5]” signifies any individual digit ranging from 1 to 5.

VBA Like Example 3 - Step 4

Step 5: When the condition in Step 4 holds true, this line exhibits a message box containing the text “Match found!”

VBA Like Example 3 - Step 5

Step 6: If the condition in Step 4 is false, this line indicates that the code should execute the following block of code (in this case, the MsgBox in VBA in the next step).

VBA Like Example 3 - Step 6

Step 7: This line displays a message box with the text “No match found!” if the condition in Step 4 is false.

VBA Like Example 3 - Step 7

Step 8: Save the macro and click on run.

When executed, the code will display a message box with the text “Match found!” because the string “12345” matches the pattern “[1-5]2345” where “[1-5]” represents any single digit from 1 to 5.

VBA Like Example 3 - Step 8

Here is the full code:

Sub Example3()

    Dim str As String

    str = “12345”

        If str Like “[1-5]2345” Then

        MsgBox “Match found!”

    Else

        MsgBox “No match found.”

    End If

End Sub

Example #4 – With Brackets & Alphabets [A-Z]

In this example, “[A-Z]*” matches any string that starts with an uppercase letter. “Hello123” matches this pattern.

Step 1: In the new module, first, we define a VBA subroutine named “Example4.”

VBA Like Example 4 - Step 1

Step 2: We define a string variable named “str.”

VBA Like Example 4 - Step 2

Step 3: Here, we allocate the string “Hello123” to the variable named “str.”

VBA Like Example 4 - Step 3

Step 4: This line initiates an “If” statement incorporating a specific condition.

The condition evaluates if the content of “str” corresponds to the “[A-Z]” pattern, where “[A-Z]” signifies any uppercase letter, and “” denotes the potential presence of zero or more characters following the uppercase letter.

VBA Like Example 4 - Step 4

Step 5: If the condition in Step 4 is true, this line displays a message box with the text “Match found!”

VBA Like Example 4 - Step 5

Step 6: If the condition in Step 4 is false, this line indicates that the code should execute the following block of code (in this case, the MsgBox in the next step).

VBA Like Example 4 - Step 6

Step 7: This line displays a message box with the text “No match found!” if the condition in Step 4 is false.

VBA Like Example 4 - Step 7

Step 8: Save the macro and click on Run.

When executed, the code will display a message box with the text “Match found!” because the string “Hello123” matches the pattern “[A-Z]” where “[A-Z]” represents an uppercase letter, and “” matches zero or more characters after the uppercase letter.

VBA Like Example 4 - Step 8

Here is the full code:

Sub Example4()

    Dim str As String

    str = “Hello123”

        If str Like “[A-Z]*” Then

        MsgBox “Match found!”

    Else

        MsgBox “No match found.”

    End If

End Sub

Important Things To Note

  1. By default, the VBA Like operator is case-insensitive in VBA. To make it case-sensitive, you can use a custom comparison function.
  2. If you want to match wildcard characters like “?” or “*”, you can use the tilde “” as an escape character. For example, “a?ple” would match “a?ple.”
  3. You can use multiple wildcards in a single pattern. For instance, “a*p?e” would match “apple.”

Frequently Asked Questions (FAQs)

Is the like operator case sensitive in VBA?

By default, the VBA Like operator is not case-sensitive. It treats uppercase and lowercase letters as the same. If you need a case-sensitive comparison, you’ll need to implement custom logic in your code.

What is the use of the like operator in VBA?

The VBA Like operator is used to compare strings against patterns. It is commonly used for wildcard-based searches and pattern matching within strings, making it a powerful tool for tasks like text parsing and data validation.

Why is the like operator not working in VBA?

If the VBA Like operator is not working as expected, you should check for potential issues such as incorrect pattern syntax, missing wildcards, or case sensitivity. Debugging your code and verifying the input data can help identify and resolve the problem.

What is the difference between like and InStr in VBA?

VBA Like Operator: It compares a string against a pattern using wildcards. It is useful for pattern-based matching.

InStr Function: It searches for one string within another and returns the position of the first occurrence. It is used for finding substrings within a larger string but does not support wildcards like the Like operator.

This has been a guide to VBA Like. Here we explain the how to use VBA Like Operator along with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *