How to Compare Strings in VBA (With Examples)


You can use the following methods in VBA to compare strings:

Method 1: Case-Sensitive String Comparison

Sub CompareStrings()
    Dim i As Integer

    For i = 2 To 10
        Range("C" & i) = StrComp(Range("A" & i), Range("B" & i)) = 0
    Next i
End Sub

This macro will perform a case-sensitive string comparison between the strings in the corresponding cells in the ranges A2:A10 and B2:B10 and return TRUE or FALSE in the range C2:C10 to indicate whether or not the strings are equal.

Method 2: Case-Insensitive String Comparison

Sub CompareStrings()
    Dim i As Integer

    For i = 2 To 10
        Range("C" & i) = StrComp(Range("A" & i), Range("B" & i), vbTextCompare) = 0
    Next i
End Sub

This macro will perform a case-insensitive string comparison between the strings in the corresponding cells in the ranges A2:A10 and B2:B10.

The following examples show how to use each method in practice with the following lists of strings in Excel:

Example 1: Case-Sensitive String Comparison in VBA

We can create the following macro to perform a case-sensitive string comparison between each corresponding string in columns A and B:

Sub CompareStrings()
    Dim i As Integer

    For i = 2 To 10
        Range("C" & i) = StrComp(Range("A" & i), Range("B" & i)) = 0
    Next i
End Sub

When we run this macro, we receive the following output:

case-sensitive string comparison in VBA

Column C returns TRUE if the strings are equal and have the same case.

Otherwise, column C returns FALSE.

Example 2: Case-Insensitive String Comparison in VBA

We can create the following macro to perform a case-insensitive string comparison between each corresponding string in columns A and B:

Sub CompareStrings()
    Dim i As Integer

    For i = 2 To 10
        Range("C" & i) = StrComp(Range("A" & i), Range("B" & i), vbTextCompare) = 0
    Next i
End Sub

When we run this macro, we receive the following output:

case-insensitive string comparison in VBA

Column C returns TRUE if the strings are equal, regardless of the case.

Column C only returns FALSE if the strings are not equal. 

Note: You can find the complete documentation for the StrComp function in VBA here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Replace Characters in String
VBA: How to Remove Special Characters from String
VBA: How to Convert String to Integer

Leave a Reply

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