Sometimes it's the little things that drive you crazy. I think I left my
brain at home.

How do you compare three strings? I want to make sure that none of three
variables are the same value. In a formula, I can do it, but the VBA code
seems to limit comparisons of only 2 at a time? Unless I'm missing something
simple. Any help?

Thanks in advance?

If var1 < var2 And var1 < var3 And var2 < var3 Then
...

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jayklmno" wrote in message
...
Sometimes it's the little things that drive you crazy. I think I left my
brain at home.

How do you compare three strings? I want to make sure that none of three
variables are the same value. In a formula, I can do it, but the VBA code
seems to limit comparisons of only 2 at a time? Unless I'm missing
something
simple. Any help?

Thanks in advance?

One way:

Dim bUnique As Boolean
bUnique = (strA < strB) And (strA < strC) And (strB < strC)
if bUnique Then
MsgBox "Strings are unique"
Else
MsgBox "Strings are not unique"
End If

In article ,
jayklmno wrote:

Sometimes it's the little things that drive you crazy. I think I left my
brain at home.

How do you compare three strings? I want to make sure that none of three
variables are the same value. In a formula, I can do it, but the VBA code
seems to limit comparisons of only 2 at a time? Unless I'm missing something
simple. Any help?

Thanks in advance?

If a = b And b = c Then
Debug.Print "OK"
Else
Debug.Print "No Way"
End If

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"jayklmno" wrote:

Sometimes it's the little things that drive you crazy. I think I left my
brain at home.

How do you compare three strings? I want to make sure that none of three
variables are the same value. In a formula, I can do it, but the VBA code
seems to limit comparisons of only 2 at a time? Unless I'm missing something
simple. Any help?

Thanks in advance?

How about something like:

Sub test()

a = 2
b = 1
c = 1

If Not Application.And(a = b, b = c, a = c) Then

MsgBox "different"
Else
MsgBox "same"
End If

End Sub

Good luck.

Ken
Norfolk, Va



On Feb 5, 5:16 pm, jayklmno
wrote:
Sometimes it's the little things that drive you crazy. I think I left my
brain at home.

How do you compare three strings? I want to make sure that none of three
variables are the same value. In a formula, I can do it, but the VBA code
seems to limit comparisons of only 2 at a time? Unless I'm missing something
simple. Any help?

Thanks in advance?

Hello,

I suggest
IF var1 = var2 OR var1 = var3 OR var2 = var3 THEN
...
END IF

Regards,
Bernd

Yet another approach is to use StrComp to compare the strings. StrComp is
faster and more efficient than use the "=" operator when comparing strings.

Function ThreeStringsDifferent(S1 As String, S2 As String, S3 As String, _
Optional ByVal CompareMode As VbCompareMethod) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''
' ThreeStringsDifferent
' This function compares S1, S2, and S3, and returns TRUE if at least one
' string is different from the others. It returns FALSE if all three strings
' are the same. If CompareMode is omitted or is any value other the
' vbBinaryCompare (case-sensitive), vbTextCompare is assumed
(case-insensitive).
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''
If CompareMode < vbBinaryCompare Then
CompareMode = vbTextCompare
End If

ThreeStringsDifferent = CBool((Abs(StrComp(S1, S2, CompareMode)) + _
Abs(StrComp(S2, S3, CompareMode))) + _
Abs(StrComp(S1, S3, CompareMode)))

End Function

You can specify in the CompareMode parameter whether the strings should be
compared ignoring upper and lower case (CompareMode omitted or anything
except vbBinaryCompare) or whether upper and lower case are to be considered
different (CompareMode = vbBinaryCompare).

You could generalize the function above to accept any number of strings.
This will accept up to 28 string values to compare. If they are all the
same, the function returns False. If at least one string differs from the
others, the result is True. Text comparison is determined by CompareMode. If
CompareMode is vbBinaryCompare, upper and lower case are considered
different. If CompareMode is any value other the vbBinaryCompare, the
comparison is case-insensitive (upper case = lower case).

Function StringsDifferent(ByVal CompareMode As VbCompareMethod, _
ParamArray Strings() As Variant) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
' StringsDifferent
' This returns TRUE if there is one string within Strings that is
' different from any other string in Strings. Case-sensitivity
' is determined by the CompareMode parameter. If CompareMode is
' any value other than vbBinaryCompare (case-sensitive),
' vbTextCompare is assumed (case-insensitive). You may supply up to
' 28 string values to test with function.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
Dim Ndx As Long
If CompareMode < vbBinaryCompare Then
CompareMode = vbTextCompare
End If

For Ndx = LBound(Strings) To UBound(Strings) - 1
If CBool(Abs(StrComp(Strings(Ndx), Strings(Ndx + 1), CompareMode))) Then
StringsDifferent = True
Exit Function
End If
Next Ndx
End Function



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"jayklmno" wrote in message
...
Sometimes it's the little things that drive you crazy. I think I left my
brain at home.

How do you compare three strings? I want to make sure that none of three
variables are the same value. In a formula, I can do it, but the VBA code
seems to limit comparisons of only 2 at a time? Unless I'm missing
something
simple. Any help?

Thanks in advance?