WallStreetMojo

WallStreetMojo

WallStreetMojo

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

VBA String Comparison

Excel VBA String Comparison

To compare two strings in VBA we have a built-in function i.e. “StrComp”. This we can read it as “String Comparison”, this function is available only with VBA and is not available as a worksheet function. It compares any two strings and returns the results as “Zero (0)” if both the strings are matching and if both supplied strings are not matching then we would get “One (1)” as the result.

In VBA or excel, we face plenty of different scenarios. One such scenario is “comparing two string values.” In a regular worksheet, we can do these multiple ways, but in VBA, how do you do this?

VBA String Comparison

Below is the syntax of the “StrComp” function.

VBA String Comparison Syntax

First, two arguments are quite simple,

  • for String 1, we need to supply what the first value we are comparing and
  • for String 2, we need to supply the second value we are comparing.
  • [Compare] this is the optional argument of StrComp function. This is helpful when we want to compare case sensitive comparison. For example, in this argument, “Excel” is not equal to “EXCEL” because both these words are case sensitive.

We can supply three values here.

  • Zero (0) for “Binary Compare,” i.e., “Excel,” is not equal to “EXCEL.” For case sensitive comparison, we can supply 0.
  • One (1) for “Text Compare,” i.e., “Excel,” is equal to “EXCEL.” This is a non-case sensitive comparison.
  • Two (2) this only for database comparison.

The results of the “StrComp” function do not default TRUE or FALSE but vary. Below are the different results of the “StrComp” function.

  • We will get “0” as the result if the supplied strings are matching.
  • We will get “1” if the supplied strings are not matching, and in case of numerical matching, we will get 1 if String 1 is greater than string 2.
  • We will get “-1” if the string 1 number is less than the string 2 number.

How to Perform String Comparison in VBA?

You can download this VBA String Comparison Excel Template here – VBA String Comparison Excel Template

Example #1

We will match “Bangalore” against the string “BANGALORE.”

First, declare two VBA variables as the string to store two string values.

Code:

Sub String_Comparison_Example1()

  Dim Value1 As String
  Dim Value2 As String

End Sub

VBA String Comparison Example 1

For these two variables, store two string values.

Code:

Sub String_Comparison_Example1()

  Dim Value1 As String
  Dim Value2 As String
  Value1 = "Bangalore"
  Value2 = "BANGALORE"

End Sub

Example 1.1

Now declare one more variable to store the result of the “StrComp” function.

Code:

Sub String_Comparison_Example1()

  Dim Value1 As String
  Dim Value2 As String
  Value1 = "Bangalore"
  Value2 = "BANGALORE"
  Dim FinalResult As String

End Sub

VBA String Comparison Example 1.2

For this variable, open the “StrComp” function.

Code:

Sub String_Comparison_Example1()

  Dim Value1 As String
  Dim Value2 As String
  Value1 = "Bangalore"
  Value2 = "BANGALORE"
  Dim FinalResult As String
  FinalResult = StrComp(

End Sub

VBA String Comparison Example 1.3

For “String1” & “String2” we have already assigned values through variables, so enter variable names, respectively.

Code:

Sub String_Comparison_Example1()

  Dim Value1 As String
  Dim Value2 As String
  Value1 = "Bangalore"
  Value2 = "BANGALORE"
  Dim FinalResult As String
  FinalResult = StrComp(Value1, Value2,

End Sub

VBA String Comparison Example 1.4

The last part of the function is “Compare” for this choice “vbTextCompare.”

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub String_Comparison_Example1()

  Dim Value1 As String
  Dim Value2 As String
  Value1 = "Bangalore"
  Value2 = "BANGALORE"
  Dim FinalResult As String
  FinalResult = StrComp(Value1, Value2, vbTextCompare)

End Sub

 Example 1.5

Now show the “Final Result” variable in the message box in VBA.

Code:

Sub String_Comparison_Example1()

  Dim Value1 As String
  Dim Value2 As String
  Value1 = "Bangalore"
  Value2 = "BANGALORE"
  Dim FinalResult As String
  FinalResult = StrComp(Value1, Value2, vbTextCompare)
  MsgBox FinalResult

End Sub

VBA String Comparison Example 1.6

Ok, let’s run the code and see the result.

Output:

 Example 1.7

Since both the strings “Bangalore” and “BANGALORE” are the same, we got the result as 0, i.e., matching. Both the values are case sensitive since we have supplied the argument as “vbTextCompare” it has ignored case sensitive match and matched only values, so both the values are the same, and the result is 0, i.e., TRUE.

Code:

Sub String_Comparison_Example1()

  Dim Value1 As String
  Dim Value2 As String
  Value1 = "Bangalore"
  Value2 = "BANGALORE"
  Dim FinalResult As String
  FinalResult = StrComp(Value1, Value2, vbTextCompare)
  MsgBox FinalResult

End Sub

VBA String Comparison Example 1.8

Example #2

For the same code, we will change the compare method from “vbTextCompare” to “vbBinaryCompare.”

Code:

Sub String_Comparison_Example2()

  Dim Value1 As String
  Dim Value2 As String
  Value1 = "Bangalore"
  Value2 = "BANGALORE"
  Dim FinalResult As String
  FinalResult = StrComp(Value1, Value2, vbBinaryCompare)
  MsgBox FinalResult

End Sub

 Example 2

Now run the code and see the result.

Output:

VBA String Comparison Example 2.1

Even though both the strings are the same, we got the result as 1, i.e., Not Matching because we have applied the compare method as “vbBinaryCompare,” which compares two values as case sensitive.

Example #3

Now we will see how to compare numerical values. For the same code, we will assign different values.

Code:

Sub String_Comparison_Example3()

  Dim Value1 As String
  Dim Value2 As String
  Value1 = 500
  Value2 = 500
  Dim FinalResult As String
  FinalResult = StrComp(Value1, Value2, vbBinaryCompare)
  MsgBox FinalResult

End Sub

VBA String Comparison Example 3

Both the values are 500, and we will get 0 as a result because both the values are matched.

Output:

Example 3.1

Now I will change the Value1 number from 500 to 100.

Code:

Sub String_Comparison_Example3()

  Dim Value1 As String
  Dim Value2 As String
  Value1 = 1000
  Value2 = 500
  Dim FinalResult As String
  FinalResult = StrComp(Value1, Value2, vbBinaryCompare)
  MsgBox FinalResult

End Sub

VBA String Comparison Example 3.2

Run the code and see the result.

Output:

 Example 3.3

We know Value1 & Value2 aren’t the same, but the result is -1 instead of 1 because for numerical comparison when the String 1 value is greater than String 2, we will get this -1.

Code:

Sub String_Comparison_Example3()

  Dim Value1 As String
  Dim Value2 As String
  Value1 = 1000
  Value2 = 500
  Dim FinalResult As String
  FinalResult = StrComp(Value1, Value2, vbBinaryCompare)
  MsgBox FinalResult

End Sub

VBA String Comparison Example 3.4

Now I will reverse the values.

Code:

Sub String_Comparison_Example3()

  Dim Value1 As String
  Dim Value2 As String
  Value1 = 500
  Value2 = 1000
  Dim FinalResult As String
  FinalResult = StrComp(Value1, Value2, vbBinaryCompare)
  MsgBox FinalResult

End Sub

 Example 3.5

Run the code and see the result.

Output:

VBA String Comparison Example 3.6

This is not special. If not match, we will get 1 only.

Things to Remember here

  • [Compare] argument of “StrComp” is optional, but in case of case sensitive match, we can utilize this, and the option is “vbBinaryCompare.”
  • The result of numerical values is slightly different in case String 1 is greater than string 2, and the result will be -1.
  • Results are 0 if matched and 1 if not matched.

 Recommended Articles

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

  • Guide to VBA String Functions
  • VBA Split String into Array
  • VBA SubString Methods
  • VBA Text
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ 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 String Comparison Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More