EDUCBA

Home VBA VBA Resources VBA String and Text Functions VBA StrComp

VBA StrComp

Madhuri Thakur
Article byMadhuri Thakur

VBA StrComp

VBA StrComp Function

In our work, we normally do comparisons every time. These comparisons can be on numbers texts or any sort of data. For numbers, we know already how do we do a comparison. There are lots of mathematical methods to do so. But how do we compare two strings, what result will we get. STRCOMP in VBA is a string comparison function. It compares two strings with each other and gives us the result. However, the result of the comparison is not true or false. There are three ways to compare two strings in STRCOMP function. Before we dip into that let us see the syntax for this function.

 

 

Syntax of StrComp in Excel VBA

The syntax for the VBA StrComp function in excel is as follows:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of StrComp

String 1 is the string which will be compared against string 2. Comparison methods are optional for this function. Now let us go to the comparison methods in this function. There are three types of comparison methods in this function and they are as follows:

  1. VbBinaryCompare: This is by default comparison method if we do not choose a comparison method for our function. This comparison method is case sensitive, which means if the string 1 and string 2 are same but string 1 is in lower case and string 2 is in upper case both will not be similar. The binary code for “a” and “A” are different. Similarly for other characters. Those codes are called ASCII Codes.
  2. VbTextCompare: In this comparison method the comparison is not case sensitive so if string 1 and string 2 is equal but not in the same case, then this comparison methods will be used.
  3. Access Compare: This method is used in database comparisons.

Now when we use this function what result will we get? We will not get true or false by using this comparison function. Instead, we can have any of the following results:

  1. 0 if the strings are equal to each other.
  2. 1 if the strings do not match with each other.
  3. -1 if the first string is smaller than string 2.
  4. NULL if there is no value for both string 1 and string 2.

We will go through some various examples and see how these string comparisons are done in VBA.

How to Use VBA StrComp Function in Excel?

We will learn how to use a VBA StrComp function with example in excel.

You can download this VBA StrComp Excel Template here – VBA StrComp Excel Template

Example #1 – VBA StrComp

Let us first use the Binary comparison method for our example. We will take two strings inputs from the user, one in lower case while the other will be in upper case and compare them.

Step 1: From the developer’s tab and then from Visual Basic to get into VB Editor.

VBA StrComp Devloper's Tab

Step 2: Insert a module from the insert tab to start writing codes in VBA.

VBA StrComp - Module

Step 3: Double click on the module from the project tab and declare a sub-function as follows.

Code:

Sub Sample()

End Sub

VBA StrComp Example 1.1

Step 4: We need to define three variables as strings, two will hold our input while another will store the result.

Code:

Sub Sample()

Dim A, B, C As String

End Sub

VBA StrComp Example 1.2

Step 5: In Variable A and B take input from the user for two strings as follows.

Code:

Sub Sample()

Dim A, B, C As String
A = InputBox("Enter a String", "In Lowercase")
B = InputBox("Enter a String", "In Uppercase")

End Sub

VBA StrComp Example 1.3

Step 6: Compare both strings in variable A and B and store the value in C using the STRCOMP function and use a comparison method as a binary comparison.

Code:

Sub Sample()

Dim A, B, C As String
A = InputBox("Enter a String", "In Lowercase")
B = InputBox("Enter a String", "In Uppercase")
C = StrComp(A, B, vbBinaryCompare)

End Sub

VBA StrComp Example 1.4

Step 7: Display the output stored in Variable C using the Msgbox function.

Code:

Sub Sample()

Dim A, B, C As String
A = InputBox("Enter a String", "In Lowercase")
B = InputBox("Enter a String", "In Uppercase")
C = StrComp(A, B, vbBinaryCompare)
MsgBox C

End Sub

Example 1.5

Step 8: Now press F5 to execute the code and provide two input strings as follows,

Example 1.6

Example 1.7

Step 9: See the final result as follows.

Example 1.8

We get 1 as a result because in Binary comparison both the strings are not equal as one of the string is in upper case while other one is in lower case.

Example #2 – VBA StrComp

Now let us use another comparison method which is VbTextCompare and see what results will we get for the same inputs we provided above.

Step 1: In the module, we inserted earlier, double click on it from the project tab and declare a sub-function to start working on the second example as follows.

Code:

Sub Sample1()

End Sub

VBA StrComp Example 2.1

Step 2: We need to define three variables as strings, two will hold our input while another will store the result.

Code:

Sub Sample1()

Dim A, B, C As String

End Sub

VBA StrComp Example 2.2

Step 3: In Variable A and B take input from the user for two strings as follows.

Code:

Sub Sample1()

Dim A, B, C As String
A = InputBox("Enter a String", "In Lowercase")
B = InputBox("Enter a String", "In Uppercase")

End Sub

VBA Sub Exit Example 2.2

Step 4: Compare both strings in variable A and B and store the value in C using the STRCOMP function and use the comparison method as text comparison.

Code:

Sub Sample1()

Dim A, B, C As String
A = InputBox("Enter a String", "In Lowercase")
B = InputBox("Enter a String", "In Uppercase")
C = StrComp(A, B, vbTextCompare)

End Sub

Example 2.4

Step 5: Display the output stored in Variable C using the Msgbox function.

Code:

Sub Sample1()

Dim A, B, C As String
A = InputBox("Enter a String", "In Lowercase")
B = InputBox("Enter a String", "In Uppercase")
C = StrComp(A, B, vbTextCompare)
MsgBox C

End Sub

Example 2.5

Step 6: Now press F5 and provide two input strings as follows.

Example 2.6

Example 2.7

Step 7: See the final result as follows.

Example 2.8

We get 0 as a result as text comparison is not case sensitive which means the strings are equal.

Example #3 – VBA StrComp

Now I have some data in Sheet 1 as follows, I want to find out if both the data in column A and column B are similar to each other or not. Have a look at the data below.

VBA StrComp Example 3.1

Step 1: Let us work in the third example for our data as follows.

Code: 

Sub Sample2()

End Sub

VBA StrComp Example 3.2

Step 2: Activate worksheet 1 so that we can use its properties.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate

End Sub

Example 3.3

Step 3: Declare two variables one as an integer which will be used for loop and one as a string which will use to store the result of the comparison.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate
Dim A As Integer
Dim B As String

End Sub

Example 3.4

Step 4: Write the following code to compare and use the for loop.

Code:

Sub Sample2()

Worksheets("Sheet1").Activate
Dim A As Integer
Dim B As String
For A = 2 To 5
B = StrComp(Cells(A, 1).Value, Cells(A, 2).Value, vbBinaryCompare)

If B = 0 Then
Cells(A, 3).Value = "Equal"
Else
Cells(A, 3).Value = "NOT Equal"
End If

Next A

End Sub

Example 3.5

Step 5: Run the above code and see the result in Sheet 1 as follows.

VBA StreComp

None of the strings were equal in the comparison.

Things to Remember

  • This is a comparison function.
  • It returns 0,1,-1 or NULL as a result not true or false.
  • If we do not provide any of the comparison methods then by default the comparison method is VbBinaryCompare.
  • Comparison Method is an optional argument for this function.

Recommended Articles

This is a guide to VBA StrComp. Here we discuss how to use Excel VBA StrComp function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA InStr
  2. VBA Integer
  3. VBA Select Cell
  4. VBA Transpose