lcha
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
>>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
ASKER
thanks everyone and apology for the delay on this. I ended up using matthewpatricks solution. thanks!
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