VBA Compare Strings – StrComp

Associated Files Download Links

In VBA, you can compare two strings using the StrComp function. In this tutorial, you will learn how to use this function with different comparison methods.

If you want to learn how to use VBA like operator, click here: VBA Like Operator

If you want to learn how to use comparison operators, click here: VBA Comparison Operators – Not Equal to & More

 

Using the StrComp Function to Compare Two Strings

The StrComp function takes two strings and comparison method as arguments. There are 3 methods of comparison:

  • vbBinaryCompare – compares two strings binary (case-sensitive);
  • vbTextCompare – compares two strings as texts (case-insensitive);
  • vbDatabaseCompare – this method is available only in Microsoft Access and not in VBA.

By default, the function uses the binary method. If we omit this argument, the function will be case-sensitive.

The function returns the following results:

  • 0 – if strings are equal;
  • -1 – if string 1 is less than string 2;
  • 1 – if string 1 is greater than string 2;
  • Null – if one of the strings is empty.

Here is the example code:

In this example, we want to check if strings “Computer” and “Computer” are equal, using the default comparison method (binary). The result of the function is in the intResult variable. As you can see in Image, both strings are equal, therefore the intResult has value 0.

vba compare strings strcomp binary

Image 1. Using the StrComp function with the binary method in VBA

 

Using the StrComp Function with Binary and Textual Method

You will now see the difference between using the binary and textual method. The binary method has a binary number for each character, so the uppercase and lowercase are different characters in this method. On the other side, the textual method considers “S” and “s” as the same letters. If you want to make the function case-insensitive, you need to set the third argument to vbBinaryCompare. Here is the code:

We want to compare strings “Computer” and “CompuTer”, using both methods.

In the variable intResult1, we will get the value with the binary method, while the intResult2 will get the value with the textual method. You can see the values of these two variables:

vba compare strings strcomp

Image 2. Using the StrComp function with binary and textual method

 

The value of intResult1 is 1, which means that two strings are different for the binary method. For the textual method, these two strings are equal, so the value of intResult2 is 0.

You may also like some of this related content...

VBA has many string functions that will allow you to manipulate and work with text
Add Spaces to a String A common way to add spaces to a string with
Extract Numerical Portion of String The following function will extract the numerical portion from a
Reverse a String of Text with VBA The VBA Routine below allows the user to
INSTR Function You can use the Instr Function in VBA to test if a string
Ads