What is Option Compare Text in VBA?

 

Ans : ➯ When we working on text data and we need to compare text values.

➯ If  you haven't used Option Compare Text the text will be considered as different.

For Ex : 

Sub Test1()

If Sheet1.Range("A1").Value = Sheet1.Range("B1").Value Then

 MsgBox "Two texts are the same" 

Else 

 MsgBox "Two texts are different" 

End If

End Sub

➯ In above Test1() Procedure we haven't used Option Compare Text , here data can be same but it can be in Upper case and Lower case so it will be considered as No Match/different.

➯ By Default VBA consider Uppercase text and Lowercase text  as a different.

➯ If we want to make text as Case In Sensitive we need to use "Option Compare Text" or "LCase" or "UCase" etc.

A) Option Compare Text :

➯ Open VBA Editor (Alt + F11)  Then Insert Module ( Alt + I + M ) Then Add Below VBA Code :

Option Compare Text

Sub Test2()

If Sheet1.Range("A1").Value = Sheet1.Range("B1").Value Then

 MsgBox "Two texts are the same" 

Else 

 MsgBox "Two texts are different" 

End If

End Sub

 
➯ Now instead of "Option Compare Text" we can use "LCase" or "UCase"

B) LCase :

For Ex :

Sub Test3()

If LCase(Sheet1.Range("A1").Value) = LCase(Sheet1.Range("B1").Value) Then

 MsgBox "Two texts are the same" 

Else 

 MsgBox "Two texts are different" 

End If

End Sub

Here LCase / UCase works same.

➯ That's It.😎😎😎