Text Comparison: Option Compare Text

Posted: July 12, 2013 by kulshresthazone in Excel, Outlook, Powerpoint, VBA
Tags: ,

If we compare text in VBA Code then “B” is not equal to “b” since there ASCII codes for both these characters are different. However we can force Excel code to compare text on the basis of  Text not on their Binary values.
Make use of  Option Compare Text on the very First line of the Code module. It will force VBA to compare Text in Text mode not in Binary mode. By Default Text comparison is done in Binary mode.

If we don’t use Option Compare Text then
“B”<>”b”
If we make use of Option Compare Text then
“B”=”b”

Happy Coding…

Advertisements
Comments
  1. snb says:

    The disadvantage of this method is it’s generality: it aplies to all the code in the codemodule.

    I’d prefer to use a specific method like:

    msgbox replace(lcase(“aabbccAABBCC”),”b”,”~”)

    to replace all b’s and B’s.in one go.

  2. for 26 different letters you need to do it 26 times in you code…

  3. I never use Option Compare Text.

    If casing is important, then just do varA=varB
    If casing should be ignorer do Upcase(varA)=Upcase(varB) or LCase(varA)=LCase(varB)

  4. Btw. Professional Excel Development by Bovey et.al.:
    “The Option Compare Text statement should be avoided for the same reason Option Base 1 should be avoided. It makes procedures behave differently when placed in modules with statement versus modules without it…”

    This is also a solution
    StrComp(“B”,”b”,vbTextCompare)
    StrComp(“B”,”b”,vbBinaryCompare)

  5. snb says:

    [quote]for 26 different letters you need to do it 26 times in you code[/quote]

    You can’t be serious; please read the VBEditor’s helpfiles, lemma lcase

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s