Link to home
Start Free TrialLog in
Avatar of lcha
lchaFlag for United States of America

asked on

excel vba program - check values in cell for ALL CAPs or uppercase return Boolean value

Hello,

I am writing a vba program that loops through all the rows in an excel spreadsheet.  For each row, I need it to check a particular cell and highlight the row in a light blue when it finds a cell that contains a value with all capitals or UPPER CASE.  

I need a check to return T or F to tell the program whether or not ALL values in the cell are in CAPs.

Is there a vba function I can use to accomplish this?   Thanks
SOLUTION
Avatar of carsRST
carsRST
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
carsRST,

Please note that your suggestion will not work if there is a module level declaration for Option Compare Text.  That is why I used the StrComp function, because it allows me to specify the text comparison mode.

Patrick
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
cyberkiwi,

>>Patrick, UCase converts it to uppercase and checks that to pre-convert

With respect, please read what I wrote:

"Please note that [carsRST's] suggestion will not work if there is a module level declaration for Option Compare Text [emphasis added]"

Try creating a module, and putting this code in it:


'begin module
Option Explicit
Option Compare Text

Sub Test1()

    MsgBox "word" = UCase("word")

End Sub
' end module


If you run that sub, the MsgBox will say "True": by using Option Compare Text, we have told VBA to ignore case in string comparisons.

Now, add another module, and put in this code:


'begin module
Option Explicit

Sub Test2()

    MsgBox "word" = UCase("word")

End Sub
' end module


When you run *that* code, the MsgBox will say "False", as unless we put Option Compare Text (or Option Compare Database for Access) in the module declarations, VBA is case-sensitive in string comparisons.


Not knowing whether or not Option Compare Text might be used, I maintain that the safest couse is to use StrComp, which allows you to specify the string comparison mode regardless of what is specified in the module declarations.

Patrick
Avatar of lcha

ASKER

thanks everyone and apology for the delay on this.  I ended up using matthewpatricks solution.  thanks!