WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA StrComp

VBA StrComp

Excel VBA StrComp Function

VBA StrComp is a built-in function used to compare whether the two string values are same or not. However, results are not defaulted TRUE or FALSE like in worksheet rather it is different.

Before we look at the results, let me show you the syntax of the StrComp function first.

VBA StrComp Formula

  • String 1: String 1 is the first string or value we are comparing.
  • String 2: String 2 is the second string or value we are comparing against String 1.
  • Compare: We can supply three options here.
      • 0 = Binary Compare. This performs case sensitive calculations. For example, “Hello” is not equal to “HELLO” because both the words cases are different. This is the default value if you ignore this parameter. vbBinaryCompare
      • 1 = Text Compare. This option performs the non-case sensitive calculations. For example, “Hello” is equal to the “HELLO” event though both the words cases are different. vbTextCompare
      • 2 = Access Compare. This performs database comparison.

Results of String Comparison (StrComp) Function

As I told when we compare two values in the worksheet, we get the result as either TRUE or FALSE. But with the VBA string comparison function, results are not the same.

  • We get zero (0) when String 1 is equal to String 2.
  • We get one (1) when the String 1 value is greater than the String 2 Value.
  • We get minus one (-1) when String 1 value is less than String 2
  • We get NULL when String 1 or String 2 value is NULL.

VBA StrComp

Examples to use VBA StrComp Function

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

Example #1

Let’s start with a simple example. For example, we will compare two values i.e., “Excel VBA” & “Excel VBA.”

Code:

Sub StrComp_Example1()

  Dim FirstValue As String 'To Store String 1 value
  Dim SecondValue As String 'To Store String 2 value

  Dim Result As String 'To Store Result of the StrComp formula

  FirstValue = "Excel VBA" 'Assign the String 1 value
  SecondValue = "Excel VBA" 'Assign the String 2 value

  Result = StrComp(FirstValue, SecondValue, vbBinaryCompare)
      'Apply StrComp function

  MsgBox Result 'Show the result in message box

End Sub

When I run this code, we will get Zero (0) as the result because both String 1 and String 2 values are the same.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA StrComp Example 1

Example #2

Now I will change the cases of two words.

String 1 = Excel Vba

String 2 = Excel VBA

Code:

Sub StrComp_Example2()

  Dim FirstValue As String 'To Store String 1 value
  Dim SecondValue As String 'To Store String 2 value

  Dim Result As String 'To Store Result of the StrComp formula

  FirstValue = "Excel Vba" 'Assign the String 1 value
  SecondValue = "Excel VBA" 'Assign the String 2 value

  Result = StrComp(FirstValue, SecondValue, vbBinaryCompare)
    'Apply StrComp function

  MsgBox Result 'Show the result in message box

End Sub

When I run this code, we will get 1 because since we supplied the Compare argument as “vbBinaryCompare,”, it will check for case-sensitive characters.

VBA StrComp Example 2

Now I will change the Compare option from “vbBinaryCompare” to “vbTextCompare”

Code:

Sub StrComp_Example3()

  Dim FirstValue As String 'To Store String 1 value
  Dim SecondValue As String 'To Store String 2 value

  Dim Result As String 'To Store Result of the StrComp formula

  FirstValue = "Excel Vba" 'Assign the String 1 value
  SecondValue = "Excel VBA" 'Assign the String 2 value

  Result = StrComp(FirstValue, SecondValue, vbTextCompare)
     'Apply StrComp function

  MsgBox Result 'Show the result in message box

End Sub

With this Compare, we will get zero (0) because vbaTextCompare ignores case sensitive words.

vba string comparison Example 3

Example #3

Case Study of VBA StrComp with IF Condition

Assume you have the data like the below image.

string comparison Example 4

We need to compare String 1 with String 2 and arrive at the Result as “Exact” if both are the same, else the result should be “Not Exact.”

The below code will do the job for us.

Code:

Sub StrComp_Example4()

  Dim Result As String
  Dim I As Integer

  For i = 2 To 6
    Result = StrComp(Cells(i, 1).Value, Cells(i, 2).Value)

    If Result = 0 Then
      Cells(i, 3).Value = "Exact"
    Else
      Cells(i, 3).Value = "Not Exact"
    End If

  Next i

End Sub

When I run the above VBA code in Excel, we will get the below result.

string comparison Example 4-1

If you look at C4 cell String 1 and String 2 are the same, but the characters are case sensitive, so the result is “Not Exact.” To overcome this issue, we need to supply the Compare as vbTextCompare.

Below is the modified code to get the result as “Exact” for the C4 cell.

Code:

Sub StrComp_Example4()

  Dim Result As String
  Dim I As Integer

  For i = 2 To 6
    Result = StrComp(Cells(i, 1).Value, Cells(i, 2).Value, vbTextCompare)

    If Result = 0 Then
      Cells(i, 3).Value = "Exact"
    Else
      Cells(i, 3).Value = "Not Exact"
    End If

  Next i

End Sub

This code will return the below result.

string comparison Example 5

Recommended Articles

This has been a guide to VBA STRCOMP. Here we discuss how to use StrComp to compare two values in VBA along with examples and download an excel template. You may also have a look at other articles related to Excel VBA –

  • VBA InStr
  • String Conversion in VBA
  • End in VBA
  • Call Sub in VBA
  • VBA Const
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA StrComp Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More