excel vba option compare binary

VBA Macros

Make VBA String Comparisons Case In-sensitive [Quick Tip]

Last updated on December 1, 2011

Chandoo

Today, while answering a reader's electronic mail, I wrote this VBA lawmaking,

If Target.Value = "yep" So 'do something End If

Only I realized that my code would run only the Target cell has "yes" in information technology. It wont run if the target cell has "Yes", or "Yes" or "YeS".

This is because by default, all VBA comparisons are binary . That ways, "yes" ≠ "Yes".

One quick work-effectually for this problem is to use UCASE to convert target.value to Capital and then compare, like this,

If UCASE(Target.Value) = "Yes" And then 'practise something Finish If

But this seemed painful, especially, if I had to practise similar comparison at multiple places in my lawmaking, I had to use UCASE() everywhere.

If only at that place is an option to tell VBA how to compare?!?

Well, there is an option.

Use Option Compare Text

If you write Option Compare Text at the superlative of your module, all the VBA comparisons with in that module will employ Text comparison instead of Binary comparing. Thus, "yes" volition exist equal to "Yeah".

Do you lot use Option Compare?

There are iii settings for Option Compare.

Selection Compare Binary: This is the default setting. Compares everything at binary level.
Option Compare Text: Used for situations like this.
Option Compare Database: Tin can be used simply with MS Access VBA. Uses Database Table settings to decide how to compare.

This is the beginning time I have used Option Compare Text. Just information technology seems like an elegant way to tell Excel VBA how to compare. I will be using it more often.

What about you lot? Do you use Option Compare? What are your favorite tips & tricks? Please share with usa using comments.

More on Excel VBA

VBA (or Macros) is how you lot tin tell Excel to automate parts of your work. It is a powerful programming linguistic communication built right in to Excel (and other MS Office applications) to help yous do more. If you are new to VBA, why don't you get thru our Free crash course?

Introduction to VBA & Excel Macros
Understanding Variables, Conditions & Loops in VBA
Using Cells, Ranges & Other Objects in your Macros
Putting information technology all together – Your First VBA Application using Excel
My Acme 10 Tips for Mastering VBA & Excel Macros

Also, go thru our VBA (Macros) article collection for more tips, tutorials & ideas.

Share this tip with your colleagues

Get Costless Excel + Power BI Tips

Uncomplicated, fun and useful emails, once per week.

Larn & be crawly.

6 Comments
Inquire a question or say something...
Tagged nether if then argument, Larn Excel, macros, option compare text, quick tip, ucase(), VBA
Category: VBA Macros

Welcome to Chandoo.org

Thank you and then much for visiting. My aim is to make you lot awesome in Excel & Ability BI. I do this past sharing videos, tips, examples and downloads on this website. At that place are more than one,000 pages with all things Excel, Ability BI, Dashboards & VBA here. Go alee and spend few minutes to be Crawly.

Read my story • Gratuitous Excel tips book

Excel School fabricated me great at work.

     5/v

Related Tips

half dozen Responses to "Make VBA String Comparisons Instance In-sensitive [Quick Tip]"

Rick Rothstein (MVP - Excel) says:

Another way to examination if Target.Value equal a string constant without regard to letter casing is to utilise the StrCmp function...

If StrComp("yes", Target.Value, vbTextCompare) = 0 Then
' Do something
End If
- Fares Al-Dhabbi says:
  
  That's a cool way to compare. i only converted my values to strings and used the above code to compare. worked nicely
  
  Thanks!
Tim says:

In case that option just needs to exist used for a single comparison, you could utilize

If InStr(1, "yes", Target.Value, vbTextCompare) Then
'practise something
End If

every bit well.
Luke M says:

Nice tip, cheers! I never even thought to think there might exist an easier mode.
Cyril Z. says:

Regarding Chronology of VB in full general, the Option Compare pragma appears at the very beginning of VB, way before classes and objects arrive (with VB6 - around 2000).

Today StrComp() and InStr() function offers a more than local style to compare, fully object, thus more than consequent with object programming (even if VB is still interpreted).

My only question hither is : "what if you want to binary compare locally with re-entering functions or concurrency (with events) ?". This volition lead to a real nightmare and probably a big nasty mess to debug.

By the way, congrats for you Millions/calendar month visits 🙂
Bhavik says:

This is nice article.
I used these examples to aid my understanding. Even Instr is like to Find but it can be example sensitive and also case insensitive.
Hope the examples below assist.

Public Sub CaseSensitive2()

If InStr(1, "Look in this string", "look", vbBinaryCompare) = 0 So
MsgBox "woops, no match"
Else
MsgBox "at least one match"
Stop If

Terminate Sub

Public Sub CaseSensitive()

If InStr("Wait in this string", "wait") = 0 And so
MsgBox "woops, no friction match"
Else
MsgBox "at least 1 lucifer"
Terminate If

Cease Sub
Public Sub NotCaseSensitive()
'doing alot of example insensitive searching and whatnot, you can put Choice Compare Text
If InStr(1, "Wait in this string", "await", vbTextCompare) = 0 So
MsgBox "woops, no friction match"
Else
MsgBox "at to the lowest degree one match"
End If

Stop Sub