 |
| FREE Excel STUFF |
Search |
| PRODUCTS |
| Development |
| Contact |
Excel Online |
|
Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee |
Current Special! Complete Excel
Excel
Training Course
for Excel 97 - Excel 2003, only $145.00.
$59.95 Instant
Buy/Download,
30 Day Money Back Guarantee & Free
Excel Help for LIFE!
Got any Excel Questions? Free Excel Help
Stop Case Sensitivity in Excel VBA Macro Code
By default, Excel VBA code is case sensitive and uses Binary comparisons. This means that "Cat" and "cat" are not seen as being the same. There are many times however, where you like Excel VBA to not use Binary comparisons and have "Cat" = "cat". This can be done in at least 2 ways.
UCASE Function
We can use the Ucase function to ensure all text we compare will be in upper case. Take the example macro below which will show a message box if it encounters any cell in A1:A10 of the active sheet that contains any case variation of the word "CAT".
Sub CompareText()
Dim rCell As Range
For Each rCell In Range("A1:A10")
If UCase(rCell) = "CAT" Then
MsgBox rCell.Address & " has " & rCell & " in it"
End If
Next rCell
End Sub
Option Compare Text
The other method we can use will make ALL procedures in a specified Module non-case sensitive. We do this by placing the words Option Compare Text at the very top of the Module we wish to make non-case sensitive. For example, any Procedures placed within the same Module as the Procedure below will no longer be case sensitive. To make ALL procedures within the Module case sensitive again we would replace Option Compare Text with Option Compare Binary
Option Compare Text
Sub OptionCompareText()
Dim rCell As Range
For Each rCell In Range("A1:A10")
If rCell = "CAT" Then
MsgBox rCell.Address & " has " & rCell & " in it"
End If
Next rCell
End Sub
Excel Dashboard Reports & Excel Dashboard Charts 50% Off
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
