What is Excel VBA StrComp Function?
The Excel VBA StrComp function is used for comparing two strings in VBA. It stands for “String Compare” and is a valuable tool for assessing the relationship between two strings, helping you determine if they are equal, which is greater, or if they have any other specific relationship.
Let us look at an example. Here, we utilize the Excel VBA StrComp function to perform a case-insensitive comparison between two strings, “apple pie” and “apple.” We ensure that case differences are ignored during comparison by applying the “vbTextCompare” option within the VBA StrComp function. The resulting value is stored in the result. To interpret this value, we employ an If statement.
The output of the above code will be a message box displaying: “The strings are not equal.”
It is because although “apple” appears in both strings, the VBA StrComp function, with the vbTextCompare option, performs a case-insensitive comparison and considers “apple pie” and “apple” to be different due to the extra characters in “apple pie.”
Table of Contents
Key Takeaways
- Excel VBA StrComp function compares two strings and determines their relationship.
- It returns an integer result: 0 for equal strings, 1 for the first string being greater, and -1 for the second string being greater.
- StrComp supports optional parameters for different comparison methods, including case-insensitive and wildcard comparisons.
- Be cautious when using StrComp in If statements to avoid unexpected results, and always specify the comparison condition explicitly. StrComp is not suitable for comparing non-string data types; other operators should be used for that purpose.
Results of String Comparison (StrComp) Function
The VBA StrComp function returns an integer value based on the comparing two strings. The result can have three possible values:
- 0: Indicates that the two strings are equal.
- 1: Shows that the first string is greater than the second string.
- -1: Indicates that the first string is less than the second string.
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 – Basic Comparison
In this example, the VBA StrComp function compares “apple” and “banana” and displays a message box indicating which string is greater.
Step 1: In the Excel workbook, press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
Step 2: Inside the VBA editor, go to Insert > Module to insert a new module where you can write your VBA code.
Step 3: In the new module, we define a VBA subroutine named BasicComparison using the Sub keyword.
Step 4: Inside the subroutine, we declare a variable result as an integer using the Dim statement. This variable will store the result of the VBA StrComp function.
Step 5: We use the result = StrComp(“apple”, “banana”) line to compare the strings “apple” and “banana” using the VBA StrComp function. The result of this comparison (0, 1, or -1) is assigned to the result variable.
Step 6: We use conditional statements (If, ElseIf, and Else) to check the value of the result:
If the result is 0, a message box indicates that the strings are equal.
If the result is 1, a message box indicates that the first string is greater.
If the result is -1, a message box indicates that the second string is greater.
Step 7: Save the macro and run the code. When you run the code, a message box will pop up saying, “The second string is greater” because, in alphabetical order, “apple” comes before “banana,” which makes the first string smaller, and VBA StrComp gives it a value of -1.
Here is the full code:
Sub BasicComparison()
Dim result As Integer
result = StrComp(“apple”, “banana”)
If result = 0 Then
MsgBox “The strings are equal.”
ElseIf result = 1 Then
MsgBox “The first string is greater.”
Else
MsgBox “The second string is greater.”
End If
End Sub
Example #2 – Case-Insensitive Comparison
This example performs a case-insensitive comparison by using vbTextCompare as the optional parameter.
Step 1: We define a VBA subroutine named CaseInsensitiveComparison in the new module.
Step 2: We declare a variable result as an integer inside the subroutine.
Step 3: We use the VBA StrComp function with the vbTextCompare option to compare the strings “Hello” and “hello” case-insensitively. The result is stored in the result variable.
Step 4: If the result is 0, a message box indicates that the strings are equal in a case-insensitive comparison.
If not, a message box indicates that the strings are not equal.
Step 5: Save the macro and click on run.
Once you run the code, the message box will show “The strings are equal (case-insensitive)” because the VBA StrComp function, with the vbTextCompare option, ignores the letter case, making “Hello” and “hello” equivalent, resulting in a StrComp value of 0.
Here is the full code:
Sub CaseInsensitiveComparison()
Dim result As Integer
result = StrComp(“Hello”, “hello”, vbTextCompare)
If result = 0 Then
MsgBox “The strings are equal (case-insensitive).”
Else
MsgBox “The strings are not equal.”
End If
End Sub
Example #3 – Wildcard Comparison
This example uses excel wildcard (*) in the comparison and demonstrates how VBA StrComp can handle wildcard comparisons.
Step 1: We define a VBA subroutine named WildcardComparison in the new module.
Step 2: We declare a variable “result” as an integer inside the subroutine..
Step 3: We use the VBA StrComp function with the vbTextCompare option to compare the string “applesauce” with “apple*” using a wildcard (*). The result is stored in the result variable.
Step 4: If the result is 0, a message box indicates that the strings match using the wildcard (*).
If the result is not 0, a message box indicates that the strings do not match.
Step 5: Once you run the code, the message box will display “The strings match using wildcard ()” because the VBA StrComp function, with the vbTextCompare option, treats “applesauce” as matching “apple” due to the wildcard (*), resulting in a VBA StrComp value of 0.
Here is the full code:
Sub WildcardComparison()
Dim result As Integer
result = StrComp(“applesauce”, “apple*”, vbTextCompare)
If result = 0 Then
MsgBox “The strings match using wildcard (*).”
Else
MsgBox “The strings do not match.”
End If
End Sub
Important Things To Note
- The VBA StrComp function can take an optional third parameter, which allows you to specify the comparison method. Commonly used options include vbBinaryCompare (default) for binary comparison and vbTextCompare for case-insensitive comparison.
- VBA StrComp Wildcard refers to the usage of wildcards in string comparisons with the VBA StrComp function. Wildcards are special characters like asterisk (*) or question mark (?) that allow for pattern matching.
- VBA StrComp in If Statement relates to using the VBA StrComp function within an If statement. You can employ VBA StrComp to compare strings and then conditionally execute code based on the result.
- When you mention VBA StrComp exact match in the context of VBA StrComp, it typically means performing a strict comparison where the two strings must match entirely, character by character, without considering case differences or any wildcards.
- When VBA StrComp is used in an If statement without comparing the result to a specific value, it can lead to a VBA StrComp always True condition because StrComp returns a non-zero result, even when the strings are not equal.
Frequently Asked Questions (FAQs)
If VBA StrComp is not working as expected, consider the following:
• Check if you are using the correct syntax.
• Ensure that you are comparing strings and not other data types.
• Verify that you are using the appropriate comparison option (e.g., vbBinaryCompare or vbTextCompare) to match your requirements.
• Double-check if you are comparing null values or empty strings, as they might produce unexpected results.
The VBA StrComp function has two optional parameters:
• String1: The first string to compare.
• String2: The second string to compare.
• Compare: (Optional) Specifies the type of comparison. It can be vbBinaryCompare (default) for binary comparison or vbTextCompare for case-insensitive comparison.
VBA StrComp and == are two different ways to compare strings in VBA:
• VBA StrComp is a function that returns an integer based on the comparison result, allowing you to determine the relationship between two strings.
• == is an operator used for simple equality comparison. It returns True if the two strings are equal and False if they are not.
Use VBA StrComp when you need more detailed information about the comparison result or when you want to perform case-insensitive or wildcard comparisons. Use == for straightforward equality checks.
No, VBA StrComp is specifically designed for comparing strings. It may not produce meaningful results when used to compare non-string data types, such as numbers. To compare numbers, you should use other comparison operators like =, <, >, etc., which are suitable for numeric comparisons.
Recommended Articles
This has been a guide to VBA StrComp. Here we explain the results of string comparison (StrComp) function along with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply