VBA StrComp Function

One of the many useful functions in VBA is the StrComp function, which is used to compare strings. In this blog post, we will discuss the purpose, syntax, arguments, example, remarks, and important notes of the StrComp function.

VBA StrComp Function – Purpose, Syntax and Arguments

Description

The StrComp function in VBA is a string comparison function that is used to compare two strings and returns a value indicating whether the strings are the same or different. It compares two strings based on the specified comparison method and returns an integer value, which can be used in various conditional statements.

Syntax

StrComp(string1, string2, [compare])

• string1: This is a required argument and refers to the first string that will be compared.
• string2: This is also a required argument and refers to the second string that will be compared.
• compare: This is an optional argument that specifies the type of comparison to be performed. It can have the following values:
  • 0 (Default) – Performs a binary comparison.
  • 1 – Performs a case-insensitive comparison.
  • -1 – Performs a case-sensitive comparison.
  • 2 – Performs a database comparison.

Arguments

• string1: This argument is mandatory and must be enclosed in quotation marks if it is a string literal. It can also be a variable or a cell reference that contains a string.
• string2: This argument is also mandatory and must be enclosed in quotation marks if it is a string literal. It can also be a variable or a cell reference that contains a string.
• compare: This argument is optional, and if omitted, the default value of 0 will be used. It determines the type of comparison to be performed, as mentioned in the syntax section above.

Example

Let’s take a look at a practical example to understand how the StrComp function works. Consider the following VBA code:

Sub CompareStrings()
    Dim str1 As String
    Dim str2 As String
    Dim result As Integer
    
    str1 = "VBA"
    str2 = "vba"
    result = StrComp(str1, str2, 1)
    
    If result = 0 Then
        MsgBox "Strings are identical."
    ElseIf result = 1 Then
        MsgBox "Strings are different."
    End If
End Sub

In this example, we have declared two string variables, str1 and str2, and assigned them the values “VBA” and “vba” respectively. We then use the StrComp function to compare these two strings using a case-insensitive comparison method (value of 1 for the ‘compare’ argument). The result is stored in the result variable. We then use an ‘If’ statement to display a message box depending on the output of the StrComp function.
The output of this code will be a message box saying “Strings are identical.” This is because, in a case-insensitive comparison, the strings “VBA” and “vba” are considered identical as they only differ in case.

Remarks

• The StrComp function is case-sensitive by default. This means that it will return a different result for strings that only differ in case, as shown in the example above.
• If the ‘compare’ argument is set to a value other than 0, 1, -1, or 2, the function will return an error.
• The StrComp function can be used in a variety of situations, such as comparing user input to a predefined list, identifying duplicates in a data set, or validating data in a form.

Important Notes

• The StrComp function can only compare two strings at a time. To compare multiple strings, you will need to use loops or other logic to compare each string individually.
• The StrComp function can also be used to compare numbers by first converting them to strings.
• If the two strings being compared are not of the same length, the function will return a value based on the length of the shorter string.

To sum it up, the StrComp function is a very useful tool in VBA that allows users to compare strings and perform specific actions based on the result of the comparison. It is a great function to use when dealing with user input, data validation, or data manipulation tasks.
In conclusion, we have discussed the purpose, syntax, arguments, example, remarks, and important notes of the StrComp function in VBA. Learning and mastering this function will greatly enhance your skills in using VBA to automate tasks and create customized solutions.

Understanding VBA StrComp Function with Examples

Example 1: Simple Comparison

The StrComp function in VBA is used to compare two strings and return an integer value based on the result of the comparison. This function has three mandatory parameters: the first string to be compared, the second string to be compared, and an optional parameter for the comparison method. In this first example, we will see a simple comparison between two strings and understand how the function works.

Dim str1 As String
Dim str2 As String
str1 = "apple"
str2 = "orange"
Dim result As Integer
result = StrComp(str1, str2)
MsgBox "The result of the comparison is " & result

1. First, we declare two string variables named str1 and str2.
2. Then, we assign the values “apple” to str1, and “orange” to str2.
3. We declare another variable named result to store the result of the comparison.
4. The StrComp function is used to compare str1 and str2, and the result is stored in the result variable.
5. Lastly, a message box is used to display the result of the comparison.

Explanation: In this example, the StrComp function compares the strings “apple” and “orange” alphabetically and returns a value based on that comparison. Since “a” comes before “o” in the alphabet, the result returned is -1. If the first string had come after the second string, the result would have been 1. If both strings were equal, the result would have been 0. This example shows the basic usage of the StrComp function.

Example 2: Using the Comparison Method

The third parameter of the StrComp function is optional and is used to specify the type of comparison to be performed. There are three comparison methods available in VBA: binary, textual, and database. In this example, we will use the database comparison method to compare two strings.

Dim str1 As String
Dim str2 As String
str1 = "apple"
str2 = "Apple"
Dim result As Integer
result = StrComp(str1, str2, vbDatabaseCompare)
MsgBox "The result of the comparison is " & result

1. In this example, we have assigned the value “apple” to both str1 and str2, but the case of the letters is different in each string.
2. The StrComp function is used to compare the strings using the vbDatabaseCompare method.
3. The result of the comparison is displayed in a message box.

Explanation: The database comparison method is used to perform a comparison that is not case-sensitive, but takes into consideration accented characters. In this example, the result of the comparison is 0, which means that both strings are equal as per the database comparison method. Had we used the binary or textual comparison method, the result would have been different.

Example 3: Ignoring Case when Comparing

In the previous example, we used the database comparison method to perform a case-insensitive comparison. However, we can also achieve the same result by using the vbTextCompare method. In this example, we will see how to ignore case when comparing two strings.

Dim str1 As String
Dim str2 As String
str1 = "apple"
str2 = "APPLE"
Dim result As Integer
result = StrComp(str1, str2, vbTextCompare)
MsgBox "The result of the comparison is " & result

1. In this example, we have assigned the value “apple” and “APPLE” to str1 and str2 respectively.
2. The StrComp function is used with the vbTextCompare method to perform a case-insensitive comparison.
3. The result of the comparison is displayed in a message box.

Explanation: Since both strings have the same value, irrespective of the case of the letters, the result of the comparison is 0. The vbTextCompare method is useful when we want to compare strings without considering the case of the letters.

Example 4: Advanced Comparison with Binary Method

The vbBinaryCompare method is the default comparison method used by the StrComp function. This method performs a binary or case-sensitive comparison between two strings. In this example, we will see how to perform an advanced comparison using the vbBinaryCompare method.

Dim str1 As String
Dim str2 As String
str1 = "apple"
str2 = "APPLE"
Dim result As Integer
result = StrComp(str1, str2, vbBinaryCompare)
MsgBox "The result of the comparison is " & result

1. In this example, we are comparing the strings “apple” and “APPLE” again, but this time using the vbBinaryCompare method.
2. The result of the comparison is displayed in a message box.

Explanation: Unlike the previous example where we got a result of 0, in this example, the result of the comparison is -1 because “a” and “A” have different ASCII codes, and the vbBinaryCompare method takes the case of the letters into consideration.

Example 5: Wildcard Character Comparison

The StrComp function can also handle wildcard characters when performing a comparison between two strings. Wildcard characters are used to represent any character or group of characters in a string. In this example, we will see how to use wildcard characters in the StrComp function.

Dim str1 As String
Dim str2 As String
str1 = "abc123def"
str2 = "abc***ef"
Dim result As Integer
result = StrComp(str1, str2, vbTextCompare)
MsgBox "The result of the comparison is " & result

1. In this example, we have assigned two strings to str1 and str2 where the second string contains wildcard characters (*).
2. The StrComp function is used with the vbTextCompare method to perform the comparison.
3. The result of the comparison is displayed in a message box.

Explanation: Since the wildcard characters represent any characters, the result of the comparison is 0, indicating that both strings are equal. The vbTextCompare method is used to ignore case when comparing.

Conclusion

The StrComp function in VBA is a useful tool for comparing strings and obtaining a result based on that comparison. It has various comparison methods that can be used to suit our needs. Understanding and mastering this function is essential for any VBA programmer, as it can be used in various scenarios to make our code more efficient and effective.