VBA Express
Solved: Read string variable while ignoring case sensitiveity [Archive] - VBA Express Forum

PDA

View Full Version : Solved: Read string variable while ignoring case sensitiveity



daniel_d_n_r
09-23-2007, 03:52 AM
Hi all just wondering how this is done.

for example

If i want to get the last 5 letters from PineApPle and match it with 'apple' or 'APPLE' OR 'aPPlE'.

Is there a way?

cheers

qff
09-23-2007, 04:04 AM
Hi

you can use the strcomp function with vbBinaryCompare to see if the letters are the same regardless of case or vbTextCompare for a case match.

Example
Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "APPLE"
str2 = "aPPlE"
If StrComp(str1, str2, vbBinaryCompare) Then
MsgBox ("match found")
End If

End Sub

regards
qff

rory
09-23-2007, 05:19 AM
That's the wrong way round - vbTextCompare ignores case, vbBinaryCompare is case sensitive. Additionally, StrComp returns 0 if there is a match, so the code should be:
Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "APPLE"
str2 = "appLe"
If StrComp(str1, str2, vbTextCompare) = 0 Then
MsgBox ("match found")
End If
End Sub

qff
09-23-2007, 05:37 AM
Rory

thanks for pointing out my error.

I should know better than to post with a hangover

regards
qff

rory
09-23-2007, 06:27 AM
:)

daniel_d_n_r
09-23-2007, 02:39 PM
Thanks guys
That looks wierd , but works, I would have thought if a string comparison equals zero (False) it would not return a match.?

cheers

xld
09-23-2007, 03:02 PM
I would use



Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "Pineapple"
str2 = "Apple"
If str1 Like "*" & str Then
MsgBox ("match found")
End If

rory
09-23-2007, 03:27 PM
It makes a bit more sense when you think that StrComp can tell you whether string 1 is less than, equal to, or greater than string 2 (returning -1, 0, or 1)
I was only commenting on qff's code though - for what you want, xld's is more specific.

johnske
09-23-2007, 03:42 PM
Hi all just wondering how this is done.

for example

If i want to get the last 5 letters from PineApPle and match it with 'apple' or 'APPLE' OR 'aPPlE'.

Is there a way?

cheersto cater for all the case variations you've given, use Option Compare Text, e.g.


Option Explicit

Option Compare Text

Sub mymatch()
If ActiveCell.Value Like "*apple*" Then MsgBox "match found"
End Sub

daniel_d_n_r
09-24-2007, 04:08 AM
It strange that VBA doesn't have a function like UCASE or LCASE to ignore case sensitivity.

thanks all for the helpful assistance

cheers

xld
09-24-2007, 04:28 AM
It does if you want that



Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "Pineapple"
str2 = "Apple"
If LCase(str1) Like "*" & LCase(str) Then
MsgBox ("match found")
End If

johnske
09-24-2007, 04:40 AM
It does if you want that



Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "Pineapple"
str2 = "Apple"
If LCase(str1) Like "*" & LCase(str) Then
MsgBox ("match found")
End If
Exactly, but that is not ignoring case sensitivity, it's forcing changes to case to enable a comparison to be made. Option Compare Text is really the only way to do what you want i.e. to ignore case altogether...

xld
09-24-2007, 04:43 AM
Is the result different?

johnske
09-24-2007, 04:47 AM
No Bob, but the thread title is... Read string variable while ignoring case sensitiveity

xld
09-24-2007, 04:53 AM
But then he said ... It strange that VBA doesn't have a function like UCASE or LCASE to ignore case sensitivity... so he clearly wasn't being as literal as you have interpreted it.

johnske
09-24-2007, 05:12 AM
Bob, my post #12 wasn't in any way some sort of criticism of your reply, it was targeted at the OP's stated misconception (Post #10) that UCASE or LCASE somehow ignores case...