VBA Option Compare Statement

The Option Compare statement in VBA is used to specify how string comparisons are made in the program. It specifies whether case-sensitive or case-insensitive comparisons should be used when comparing strings in VBA. This statement is especially useful when working with large amounts of data that may contain variations in text such as upper and lowercase letters. the Option Compare statement also allows for more control over the results of string comparisons.

VBA Option Compare Statement

Syntax of Option Compare Statement

The syntax for the Option Compare statement is as follows:

Option Compare {Binary | Text | Database}

The three options for the Option Compare statement are:

• Binary: This is the default option and it performs binary comparisons based on the numeric value of each character. It is case-sensitive, meaning that uppercase letters are considered different from lowercase letters. For example, “Apple” and “apple” would be considered different words using this option.
• Text: This option performs text-based comparisons using the sorting rules defined in the system’s locale. It also considers the uppercase and lowercase letters as equal. So in the example above, “Apple” and “apple” would be considered the same word under this option.
• Database: This option is similar to the ‘Text’ option but it also takes into account the sorting rules used in databases. This option is useful when working with databases and ensuring consistency in data comparisons.

Examples on VBA Option Compare Statement

Now, let’s look at some practical examples of how the Option Compare statement can be used in VBA.

Example 1: Comparing two strings using ‘Binary’ option

Option Compare Binary
'Strings to be compared
Dim str1 As String
Dim str2 As String
str1 = "Apple"
str2 = "apple"
'Using the StrComp function for comparing strings
If StrComp(str1, str2) = 0 Then
  MsgBox "Strings are equal using Binary option."
Else
  MsgBox "Strings are not equal using Binary option."
End If

The above code will display a message box stating that the strings are not equal as it is using the ‘Binary’ option for comparisons.

Example 2: Comparing two strings using ‘Text’ option

Option Compare Text
'Strings to be compared
Dim str1 As String
Dim str2 As String
str1 = "Apple"
str2 = "apple"
'Using the StrComp function for comparing strings
If StrComp(str1, str2) = 0 Then
  MsgBox "Strings are equal using Text option."
Else
  MsgBox "Strings are not equal using Text option."
End If

In this example, the code will display a message box stating that the strings are equal as the ‘Text’ option considers the uppercase and lowercase letters as equal.

Example 3: Using Option Compare in a Case statement

The Option Compare statement can also be used in a Case statement for more control over string comparisons.

Option Compare Text
Dim fruit As String
fruit = "apple"
'Switch statement using Case
Select Case fruit
  Case "Apple"
    MsgBox "A fruit."
  Case "Apple", "Banana"
    MsgBox "A fruit or a vegetable."
  Case Else
    MsgBox "Neither a fruit nor a vegetable."
End Select

In this case, as we are using the ‘Text’ option, the code will display a message box stating that “A fruit or a vegetable” when the fruit is “apple” as it considers the uppercase and lowercase letters as equal.

Example 4: Comparing two strings using ‘Database’ option

Option Compare Database
'Strings to be compared
Dim str1 As String
Dim str2 As String
str1 = "Apple"
str2 = "apple"
'Using the StrComp function for comparing strings
If StrComp(str1, str2) = 0 Then
  MsgBox "Strings are equal using Database option."
Else
  MsgBox "Strings are not equal using Database option."
End If

In this example, the code will display a message box stating that the strings are equal using the ‘Database’ option as it takes into account the sorting rules used in databases.

Example 5: Using Option Compare with other string functions

The Option Compare statement can also be used with other string functions to better control string comparisons.

Option Compare Text
Dim fruit As String
fruit = "apple"
'Using the Like function with wildcard characters
If fruit Like "a*e" Then
  MsgBox "The word starts with 'a' and ends with 'e'."
Else
  MsgBox "The word does not start with 'a' and ends with 'e'."
End If

The code above will display a message box stating that the word starts with ‘a’ and ends with ‘e’ as the ‘Text’ option considers the uppercase and lowercase letters as equal.

Important Notes and Remarks

• The Option Compare statement must appear in a module before any procedures. Otherwise, it will have no effect.
• If the Option Compare statement is not included in the code, then the default comparison method will be used, which is ‘Binary’.
• The Option Compare statement is not supported in VBScript.
• If the Option Compare statement is used, then all string comparisons in the code will be affected. Therefore, it is important to choose the appropriate option based on the requirements of the program.

In Conclusion

The Option Compare statement in VBA is a powerful tool for controlling string comparisons in your program. It allows for more control over how strings are compared, making it easier to work with large amounts of data and ensuring consistency in data comparisons. Understanding its syntax and how it works will greatly enhance your coding experience in VBA.

Thank you for reading this blog post on the Option Compare statement in VBA. I hope it provided you with a clear understanding of its purpose, syntax, and practical examples.

I would love to hear your feedback and views on this topic. Did you find it helpful? Is there anything else you would like to know about the Option Compare statement? Please leave a comment below and share your thoughts. Thank you!