VBA STRCOMP Function

VBA StrComp Function in Excel

VBA StrComp function in Excel is categorized as a Text/String function in VBA. It is a built-in function in MS Office Excel. Returns an integer value representing the result of string comparison. 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.

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 Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers

Leave a Reply