EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA String and Text Functions VBA String Comparison
 

VBA String Comparison

Madhuri Thakur
Article byMadhuri Thakur

VBA String Comparison

Excel VBA String Compare

In our day to day life, we often do comparisons. Comparisons between two people, comparison between two products, comparison between two numbers and for that sake, comparison between two strings as well. While, for comparing two numbers, we have different mathematical functions available such as equality operator, we are in this lecture about to have a look at how to compare strings in under VBA in Excel. The conventional equality operator provides either TRUE or FALSE as a result of a comparison between two numbers or texts. Under VBA, we have STRCOMP as a function that specifies the comparison between two strings. However, the results are not as same as the equality operator (TRUE/FALSE) instead we have three possible results, based on the comparison. We will go through this in detail in the next session of this article. Let’s see the syntax and working of the VBA STRCOMP function.

 

 

Syntax of VBA StrComp function:

Watch our Demo Courses and Videos

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

 VBA String Comparison Syntax

Where,

String1 and String2 are the two string values we wanted to compare with each other.

How to Compare Strings in VBA?

VbCompareMethod is a method of comparison for the two strings provided. There are three types of comparison mentioned below:

  • vbBinaryCompare: Compares two strings binarily and is case sensitive (‘UPPER’ does not equal to ‘upper’). This is the default comparison method if not specified.
  • vbTextCompare: Compares two strings as texts and is not case sensitive (‘Upper’ equals to ‘upper’).
  • vbDatabaseCompare: Compares the text through the database and is only available for Microsoft Access not under VBA.

This function can return three possible results as follows:

  • 0 – If String1 matches with String2.
  • 1 – If String1 does not match with String2 and in binary comparison case String1 is lesser than String2.
  • -1 – If String 1 does not match with String 2 and in binary comparison case, String 1 is greater than String2.

Examples of String Comparison in VBA

Below are the examples of Excel VBA String Comparison:

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

VBA String Comparison – Example #1

Let’s see some examples of the VBA StrComp function. For this, follow the below steps:

Step 1: Open a new Excel file and hit Alt + F11 to navigate towards the Visual Basic Editor. You can navigate to the Developers tab and click on the Visual Basic button to achieve the same result.

Developer Tab

Step 2: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Insert Module

Step 3: Define a new sub-procedure using the Sub-End clause that can hold your macro.

Code:

Sub Strcomp_Ex1()

End Sub

VBA String Comparison Examples 1-3

Step 4: Define two new variables String1 and String2 with data type as a string that can be used to store the string values, we wanted to compare with each other.

Code:

Sub Strcomp_Ex1()

Dim String1 As String
Dim String2 As String

End Sub

VBA String Comparison Examples 1-4

Step 5: Assign text values to the two variables we have created using the assignment operator. I will assign values as ‘welcome to vba!’ and “WELCOME TO VBA!” respectively to the two variables.

Code:

Sub Strcomp_Ex1()

Dim String1 As String
Dim String2 As String
String1 = "welcome to vba!"
String2 = "WELCOME TO VBA!"

End Sub

VBA String Comparison Examples 1-5

Step 6: Now, define a new variable named CompResult as a string. This variable will be used to store the result of the StrComp function which compares whether two of the strings are equal or not.

Code:

Sub Strcomp_Ex1()

Dim String1 As String
Dim String2 As String
String1 = "welcome to vba!"
String2 = "WELCOME TO VBA!"
Dim CompResult As String

End Sub

New variable Examples 1-6

Step 7: Now, use the assignment operator to assign the value of StrComp function to this newly defined variable.

Code:

Sub Strcomp_Ex1()

Dim String1 As String
Dim String2 As String
String1 = "welcome to vba!"
String2 = "WELCOME TO VBA!"
Dim CompResult As String
CompResult = StrComp(

End Sub

VBA String Comparison Examples 1-7

Step 8: Provide the arguments this function statement requires. Use String1 as the first argument String2 variable as the second argument and use vbTextCompare as the comparison argument to this statement.

Code:

Sub Strcomp_Ex1()

Dim String1 As String
Dim String2 As String
String1 = "welcome to vba!"
String2 = "WELCOME TO VBA!"
Dim CompResult As String
CompResult = StrComp(String1, String2, vbTextCompare)

End Sub

VBA String Comparison Examples 1-8

Step 9: Use VBA MsgBox property to display the output of the StrComp statement which is stored under CompResult variable.

Code:

Sub Strcomp_Ex1()

Dim String1 As String
Dim String2 As String
String1 = "welcome to vba!"
String2 = "WELCOME TO VBA!"
Dim CompResult As String
CompResult = StrComp(String1, String2, vbTextCompare)
MsgBox CompResult

End Sub

VBA String Comparison Examples 1-9

Step 10: Run this code by hitting F5 or Run button placed at the uppermost ribbon of Visual Basic Editor.

VBA String Comparison Examples 1-10

Zero here in message box means that two texts we have stored under String1 and String2 are matching with each other though there is a case difference. This is because we have specified vbTextCompare as a comparison argument. This argument checks whether the texts are matching with each other or not irrespective of checking the cases.

VBA String Comparison – Example #2

Now, follow the below steps to compare strings in VBA.

Step 1: Define sub-procedure which can hold your macro.

Code:

Sub strcomp_Ex2()

End Sub

Sub-Procedure Examples 2-1

Step 2: Define a variable Result as String so that we can assign a value of StrComp function to it.

Code:

Sub strcomp_Ex2()

Dim Result As String

End Sub

VBA String Comparison Examples 2-2

Step 3: Now use the Assignment operator to assign the value of StrComp to the variable named Result.

Code:

Sub strcomp_Ex2()

Dim Result As String
Result = StrComp(

End Sub

Assignment operator Examples 2-3

Step 4: Now, we need to specify the arguments for this function. Use “india” as a String1, “INDIA” as String2 arguments.

Code:

Sub strcomp_Ex2()

Dim Result As String
Result = StrComp("india", "INDIA"

End Sub

Argument Examples 2-4

Step 5: For comparison of two strings, use vbBinaryCompare as an argument. It is OK if you put is as blank because it is an optional argument and has a default value as vbBinaryCompare itself. Due to this comparison operation, the system checks the binary codes of two strings given as input (which apparently will be different for two strings provided).

Code:

Sub strcomp_Ex2()

Dim Result As String
Result = StrComp("india", "INDIA",

End Sub

Binary Code Examples 2-5

Step 6: Use VBA MsgBox Property to display the Result in the Message box.

Code:

Sub strcomp_Ex2()

Dim Result As String
Result = StrComp("india", "INDIA", vbBinaryCompare)
MsgBox Result

End Sub

VBA String Comparison Examples 2-6

Step 7: Run this code by hitting F5 or Run button that is placed at the upper ribbon in Visual Basic Editor.

VBA String Comparison Examples 2-7

As soon as you run this code, you’ll see an output as “1” in the message box which means these two texts are not equal. Rightly so because of the use of vbBinaryCompare property. This is how we can compare two strings in VBA using the StrComp function. Let’s close this article with some points to be remembered:

Things to Remember

  • Comparison argument is optional and can be skipped as it has a default value as vbBinaryCompare which checks the two strings with their binary codes. It means “UPPER” and “upper” are both different.
  • If no string value provided, this function returns a Null value
  • We can use this function to compare the email addresses of different clients.
  • If the String1 is greater than String2. This happens in the case of Binary Comparison; the output will be -1 in that case.

Recommended Articles

This is a guide to the VBA String Comparison. Here we discuss how to Compare Strings in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA SendKeys
  2. VBA Name Worksheet
  3. VBA CInt
  4. VBA SubString

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

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

EDUCBA

Download VBA String Comparison - Excel Template

EDUCBA
Watch our Demo Courses and Videos

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW