VBA STRCOMP Function

VBA StrComp Function

Overview of VBA StrComp Function:

VBA StrComp function is categorized as a Text/String function in VBA. It is a built-in function in MS Office Excel. It returns an integer value representing the result of string comparision. It has two required parameters and one optional parameter. If string1 & string2 arguments are null, it returns Null. This function could be used as a VBA function and can’t be used as a Excel Worksheet function.
The StrComp function can be used in either procedure or function in a VBA editor window in Excel. We can use this VBA StrComp function any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the StrComp function, where we can use this StrComp function and real-time examples in VBA.

Table of Contents:

Syntax of VBA StrComp Function

The syntax of the VBA StrComp function is

StrComp(String1, String2, [Compare])

Note: This StrComp function returns a value.

Parameters or Arguments

This function has two mandatory parameter and one optional parameters for the StrComp Function.
Where
String1: The String1 is a mandatory argument. It represents a first string expression to be compared.
String2: The String2 is a mandatory argument. It represents a second string expression to be compared.
Compare: The Compare is an optional parameter. It represents the type of comparison to perform. Here are the list of following comparison values.

VBA Compare Value Explanation
vbUseCompareOption -1 Performs a comparision using the ‘option compare’
vbBinaryCompare 0 Performs a Binary comparison
vbTextCompare 1 Performs a Textual comparison
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.

Where we can apply or use the VBA StrComp Function?

We can use this VBA StrComp function in MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

Example 1: Example on VBA StrComp Function – vbBinaryCompare

Here is a simple example of the VBA StrComp function.This below example macro uses the StrComp function. And returns output as ‘0’.

'Example on VBA StrComp Function : vbBinaryCompare
Sub VBA_StrComp_Function_Ex1()

    Dim sString1 As String, sString2 As String
    Dim iOutput As Integer
    
    sString1 = "VBAF1.COM": sString2 = "VBAF1.COM"
    
    iOutput = StrComp(sString1, sString2, vbBinaryCompare)
    
    MsgBox "Binary Compare :" & iOutput, vbInformation, "VBA StrComp Function"

End Sub

Output:Here is the screen shot of first example output.
VBA StrComp Function

Example 2: Example on VBA StrComp Function – vbTextCompare

Here is another example of the VBA StrComp function. This below example macro uses the StrComp function. And returns output as ‘1’.

'Example on VBA StrComp Function : vbTextCompare
Sub VBA_StrComp_Function_Ex2()

    Dim sString1 As String, sString2 As String
    Dim iOutput As Integer
    
    sString1 = "VBAF1.COM": sString2 = "VBAF1"
    
    iOutput = StrComp(sString1, sString2, vbTextCompare)
    
    MsgBox "Text Compare :" & iOutput, vbInformation, "VBA StrComp Function"

End Sub

Output:Here is the screen shot of second example output.
VBA StrComp Function

Example 3: Example on VBA StrComp Function – vbUseCompareOption

Here is a simple example of the VBA StrComp function. This below example macro uses the StrComp function. And returns output as ‘-1’.

'Example on VBA StrComp Function : vbUseCompareOption
Sub VBA_StrComp_Function_Ex3()

    Dim sString1 As String, sString2 As String
    Dim iOutput As Integer
    
    sString1 = "VBAF1": sString2 = "VBAF1.COM"
    
    iOutput = StrComp(sString1, sString2, vbUseCompareOption)
    
    MsgBox "Use Compare Option :" & iOutput, vbInformation, "VBA StrComp Function"

End Sub

Output:Here is the screen shot of third example output.
VBA StrComp Function

Instructions to use Macro Codes

Here are the step by step instructions to use the VBA StrComp function in Excel VBA.

  • Open an Excel workbook.
  • Press Alt+F11 to open VBA Editor window.
  • Go to Insert menu and click on module from the available options.
  • Copy above specified macros to the VBA editor.
  • Select any procedure from above codes and click on ‘Run’ command or use Keyboard shortcut ‘F5’.

Complete List of VBA Functions:

Please click on below link to go back to see all List of VBA Functions.
List of VBA Functions

Leave a Comment

Your email address will not be published. Required fields are marked *