Hi -

I'm looking for a Boolean VBA function, MyFun, that takes in 2 input
strings, String1 and String2, like MyFun(String1,String2). Here are the
specs:

(1) If ALL the tokens in String2 are also tokens of String1, then MyFun
will output to "True", otherwise, it should return "False"

(2) Also, if either String1 or String2 is empty "", then MyFun will
return "False"

(3) The tokens in both input strings are delimited by "," except when
there is only one token contained in any input.

Example:
MyFun("YH,L,GT,W,B,Q","B,GT,YH") should return "True"

MyFun("H,C,KK,V","") should return "False"

MyFun("AY,DC,GJ,U","AY,U,Z") should return "False"

I would appreciate any assistance!
Thank you.
Jay Dean

*** Sent via Developersdex http://www.developersdex.com ***

Hi Jay Dean

This should do what you are looking for:

Public Function MyFun(String1 As String, String2 As String) As Boolean
Dim arr1 As Variant
Dim arr2 As Variant
If String1 = "" Or String2 = "" Then
Exit Function
End If
If InStr(1, String1, ",") Then
arr1 = Split(String1, ",")
Else
ReDim arr1(0)
arr1(0) = String1
End If

If InStr(1, String2, ",") Then
arr2 = Split(String2, ",")
Else
ReDim arr2(0)
arr2(0) = String2
End If

For c = LBound(arr2) To UBound(arr2)
For r = LBound(arr1) To UBound(arr1)
If arr2(c) = arr1(r) Then
Token = Token + 1
Exit For
End If
Next
Next
If Token = UBound(arr2) + 1 Then
MyFun = True
End If
End Function

Regards,
Per

"jay dean" skrev i meddelelsen
...
Hi -

I'm looking for a Boolean VBA function, MyFun, that takes in 2 input
strings, String1 and String2, like MyFun(String1,String2). Here are the
specs:

(1) If ALL the tokens in String2 are also tokens of String1, then MyFun
will output to "True", otherwise, it should return "False"

(2) Also, if either String1 or String2 is empty "", then MyFun will
return "False"

(3) The tokens in both input strings are delimited by "," except when
there is only one token contained in any input.

Example:
MyFun("YH,L,GT,W,B,Q","B,GT,YH") should return "True"

MyFun("H,C,KK,V","") should return "False"

MyFun("AY,DC,GJ,U","AY,U,Z") should return "False"

I would appreciate any assistance!
Thank you.
Jay Dean

*** Sent via Developersdex http://www.developersdex.com ***

Try this one

Function myfunc(ByVal s1 As String, ByVal s2 As String) As Boolean
Dim s1a, s2a, dummy

s1a = Split(s1, ",")
s2a = Split(s2, ",")
On Error GoTo ex:
For i = 0 To Application.Max(0, UBound(s2a))
dummy = WorksheetFunction.Match(s2a(i), s1a, 0)
Next
myfunc = True
Exit Function
ex:
myfunc = False
End Function

Keiji

jay dean wrote:
Hi -

I'm looking for a Boolean VBA function, MyFun, that takes in 2 input
strings, String1 and String2, like MyFun(String1,String2). Here are the
specs:

(1) If ALL the tokens in String2 are also tokens of String1, then MyFun
will output to "True", otherwise, it should return "False"

(2) Also, if either String1 or String2 is empty "", then MyFun will
return "False"

(3) The tokens in both input strings are delimited by "," except when
there is only one token contained in any input.

Example:
MyFun("YH,L,GT,W,B,Q","B,GT,YH") should return "True"

MyFun("H,C,KK,V","") should return "False"

MyFun("AY,DC,GJ,U","AY,U,Z") should return "False"

I would appreciate any assistance!
Thank you.
Jay Dean

*** Sent via Developersdex http://www.developersdex.com ***

Give this function a try...

Function MyFun(S1 As String, S2 As String) As Boolean
Dim X As Long, Cnt As Long
Dim Tokens() As String
If Len(S2) Then
Tokens = Split(S2, ",")
For X = 0 To UBound(Tokens)
If InStr("," & S1 & ",", "," & Tokens(X) & ",") Then Cnt = Cnt + 1
Next
If Cnt = UBound(Tokens) + 1 Then MyFun = True
End If
End Function

--
Rick (MVP - Excel)


"jay dean" wrote in message
...
Hi -

I'm looking for a Boolean VBA function, MyFun, that takes in 2 input
strings, String1 and String2, like MyFun(String1,String2). Here are the
specs:

(1) If ALL the tokens in String2 are also tokens of String1, then MyFun
will output to "True", otherwise, it should return "False"

(2) Also, if either String1 or String2 is empty "", then MyFun will
return "False"

(3) The tokens in both input strings are delimited by "," except when
there is only one token contained in any input.

Example:
MyFun("YH,L,GT,W,B,Q","B,GT,YH") should return "True"

MyFun("H,C,KK,V","") should return "False"

MyFun("AY,DC,GJ,U","AY,U,Z") should return "False"

I would appreciate any assistance!
Thank you.
Jay Dean

*** Sent via Developersdex http://www.developersdex.com ***

On Sun, 19 Jul 2009 02:23:44 -0700, jay dean wrote:

Hi -

I'm looking for a Boolean VBA function, MyFun, that takes in 2 input
strings, String1 and String2, like MyFun(String1,String2). Here are the
specs:

(1) If ALL the tokens in String2 are also tokens of String1, then MyFun
will output to "True", otherwise, it should return "False"

(2) Also, if either String1 or String2 is empty "", then MyFun will
return "False"

(3) The tokens in both input strings are delimited by "," except when
there is only one token contained in any input.

Example:
MyFun("YH,L,GT,W,B,Q","B,GT,YH") should return "True"

MyFun("H,C,KK,V","") should return "False"

MyFun("AY,DC,GJ,U","AY,U,Z") should return "False"

I would appreciate any assistance!
Thank you.
Jay Dean

*** Sent via Developersdex http://www.developersdex.com ***

Try this:

==============================
Option Explicit
Function MyFun(S1 As String, S2 As String) As Boolean
Dim sTokens2() As String
Dim i As Long

sTokens2 = Split(S2, ",")

For i = 0 To UBound(sTokens2)
If InStr(S1, sTokens2(i)) = 0 Then Exit For
Next i
If i 0 And i = UBound(sTokens2) + 1 Then MyFun = True
End Function
================================
--ron

I'm looking for a Boolean VBA function, MyFun, that takes in 2 input
strings, String1 and String2, like MyFun(String1,String2). Here are the
specs:

(1) If ALL the tokens in String2 are also tokens of String1, then MyFun
will output to "True", otherwise, it should return "False"

(2) Also, if either String1 or String2 is empty "", then MyFun will
return "False"

(3) The tokens in both input strings are delimited by "," except when
there is only one token contained in any input.

Example:
MyFun("YH,L,GT,W,B,Q","B,GT,YH") should return "True"

MyFun("H,C,KK,V","") should return "False"

MyFun("AY,DC,GJ,U","AY,U,Z") should return "False"

I would appreciate any assistance!

Try this:

==============================
Option Explicit
Function MyFun(S1 As String, S2 As String) As Boolean
Dim sTokens2() As String
Dim i As Long

sTokens2 = Split(S2, ",")

For i = 0 To UBound(sTokens2)
If InStr(S1, sTokens2(i)) = 0 Then Exit For
Next i
If i 0 And i = UBound(sTokens2) + 1 Then MyFun = True
End Function
================================

You can't test with InStr directly like that because it will return false
positives for short included string in S2. For example...

MsgBox MyFun("AA,BB,CC", "A,B")

will display True even though A and B do not appear, as is, in "AA,BB,CC".
That is why in the function I posted I concatenated the delimiter onto each
element of the second argument's string value and put a delimiter on each
side of the first argument's string value... doing this insures unique
matches. I do like your idea of testing for failure inside the loop
(although I would probably just Exit Function at that point) and then using
the For..Next loop's index for your logical test as opposed to the "extra"
counter variable I used. This is the line of I would use inside your loop to
account for the above comments...

If InStr("," & S1 & ",", "," & sTokens2(i) & ",") = 0 Then Exit Function

--
Rick (MVP - Excel)

Thanks Rick, Per Jessen, Keiji, and Ron.
A few comments:

Keiji, your code works just as the others but it doesn't seem to do a
binary compare. For example, using your code: MyFun("GG,TY,D","gg,D")
will evaluate to "True" even though the token "GG" < "gg".

I think agree with Rick's comments on Ron's code. In effect, it works
like the Find() function. It doesn't look for exact matches explicitly,
but also includes 'hits' that match partially.
Rick, is there a way to modify your code so that it ignores spaces as
the Trim() function will do? Example: Right now, using your code,
MyFun("YH,L,GT,W,B,Q", " B")
or MyFun("YH,L,GT,W,B,Q", " B,W ") produces "False" because of the
spaces in the tokens, even though "B" and "W" are elements of the first
input. Is there a way to have this ignore the spaces and ouput to "True"
as long as all the characters themselves match regardless of any spaces
in the tokens?

Thanks
Jay



*** Sent via Developersdex http://www.developersdex.com ***

Using the structure Ron posted (along with my modified line of code), I just
use the Replace function on *each* argument's string values to remove any
and all spaces from both of them...

Function MyFun(S1 As String, S2 As String) As Boolean
Dim i As Long
Dim sTokens2() As String
sTokens2 = Split(Replace(S2, " ", ""), ",")
For i = 0 To UBound(sTokens2)
If InStr("," & S1 & ",", "," & Replace(sTokens2(i), _
" ", "") & ",") = 0 Then Exit Function
Next i
If i 0 And i = UBound(sTokens2) + 1 Then MyFun = True
End Function

--
Rick (MVP - Excel)


"jay dean" wrote in message
...
Thanks Rick, Per Jessen, Keiji, and Ron.
A few comments:

Keiji, your code works just as the others but it doesn't seem to do a
binary compare. For example, using your code: MyFun("GG,TY,D","gg,D")
will evaluate to "True" even though the token "GG" < "gg".

I think agree with Rick's comments on Ron's code. In effect, it works
like the Find() function. It doesn't look for exact matches explicitly,
but also includes 'hits' that match partially.
Rick, is there a way to modify your code so that it ignores spaces as
the Trim() function will do? Example: Right now, using your code,
MyFun("YH,L,GT,W,B,Q", " B")
or MyFun("YH,L,GT,W,B,Q", " B,W ") produces "False" because of the
spaces in the tokens, even though "B" and "W" are elements of the first
input. Is there a way to have this ignore the spaces and ouput to "True"
as long as all the characters themselves match regardless of any spaces
in the tokens?

Thanks
Jay



*** Sent via Developersdex http://www.developersdex.com ***

Hi Rick
I'm a little bit interested in how you realized Jay's tokens are case
sensitive. By the way, you said you removed all spaces from *each*
argument's string values. but your code below seems to remove all spaces
from the second argument twice and I would like to apply Replace to S1
before for loop. I think this one is simple and looks smart, though it's
not my taste not to explicitly set a return value false assuming default
value.

Keiji

Rick Rothstein wrote:
Using the structure Ron posted (along with my modified line of code), I
just use the Replace function on *each* argument's string values to
remove any and all spaces from both of them...

Function MyFun(S1 As String, S2 As String) As Boolean
Dim i As Long
Dim sTokens2() As String
sTokens2 = Split(Replace(S2, " ", ""), ",")
For i = 0 To UBound(sTokens2)
If InStr("," & S1 & ",", "," & Replace(sTokens2(i), _
" ", "") & ",") = 0 Then Exit Function
Next i
If i 0 And i = UBound(sTokens2) + 1 Then MyFun = True
End Function

*** Code Correction ***

In a parallel posting, Keiji observed a problem with the function I posted.
Here is a modified function that fixes the problem.

Function MyFun(S1 As String, S2 As String) As Boolean
Dim i As Long, S1FixedUp As String, sTokens2() As String
S1FixedUp = "," & Replace(S1, " ", "") & ","
sTokens2 = Split(Replace(S2, " ", ""), ",")
For i = 0 To UBound(sTokens2)
If InStr(S1FixedUp, "," & sTokens2(i) & ",") = 0 Then Exit Function
Next
If i 0 And i = UBound(sTokens2) + 1 Then MyFun = True
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Using the structure Ron posted (along with my modified line of code), I
just use the Replace function on *each* argument's string values to remove
any and all spaces from both of them...

Function MyFun(S1 As String, S2 As String) As Boolean
Dim i As Long
Dim sTokens2() As String
sTokens2 = Split(Replace(S2, " ", ""), ",")
For i = 0 To UBound(sTokens2)
If InStr("," & S1 & ",", "," & Replace(sTokens2(i), _
" ", "") & ",") = 0 Then Exit Function
Next i
If i 0 And i = UBound(sTokens2) + 1 Then MyFun = True
End Function

--
Rick (MVP - Excel)


"jay dean" wrote in message
...
Thanks Rick, Per Jessen, Keiji, and Ron.
A few comments:

Keiji, your code works just as the others but it doesn't seem to do a
binary compare. For example, using your code: MyFun("GG,TY,D","gg,D")
will evaluate to "True" even though the token "GG" < "gg".

I think agree with Rick's comments on Ron's code. In effect, it works
like the Find() function. It doesn't look for exact matches explicitly,
but also includes 'hits' that match partially.
Rick, is there a way to modify your code so that it ignores spaces as
the Trim() function will do? Example: Right now, using your code,
MyFun("YH,L,GT,W,B,Q", " B")
or MyFun("YH,L,GT,W,B,Q", " B,W ") produces "False" because of the
spaces in the tokens, even though "B" and "W" are elements of the first
input. Is there a way to have this ignore the spaces and ouput to "True"
as long as all the characters themselves match regardless of any spaces
in the tokens?

Thanks
Jay



*** Sent via Developersdex http://www.developersdex.com ***

Thanks for your comment. I used match function that does not distinguish
between uppercase and lowercase letters. On the other hand, Instr
function that Rick and Ron used has options for a binary comparison or a
binary comparison.

Keiji

jay dean wrote:
Thanks Rick, Per Jessen, Keiji, and Ron.
A few comments:

Keiji, your code works just as the others but it doesn't seem to do a
binary compare. For example, using your code: MyFun("GG,TY,D","gg,D")
will evaluate to "True" even though the token "GG" < "gg".

I think agree with Rick's comments on Ron's code. In effect, it works
like the Find() function. It doesn't look for exact matches explicitly,
but also includes 'hits' that match partially.
Rick, is there a way to modify your code so that it ignores spaces as
the Trim() function will do? Example: Right now, using your code,
MyFun("YH,L,GT,W,B,Q", " B")
or MyFun("YH,L,GT,W,B,Q", " B,W ") produces "False" because of the
spaces in the tokens, even though "B" and "W" are elements of the first
input. Is there a way to have this ignore the spaces and ouput to "True"
as long as all the characters themselves match regardless of any spaces
in the tokens?

Thanks
Jay



*** Sent via Developersdex http://www.developersdex.com ***

On Sun, 19 Jul 2009 13:33:13 -0400, "Rick Rothstein"
wrote:

You can't test with InStr directly like that because it will return false
positives for short included string in S2. For example...

MsgBox MyFun("AA,BB,CC", "A,B")

will display True even though A and B do not appear, as is, in "AA,BB,CC".


Good point.

Well, here's a regex solution that I think works properly. Jay wrote in
another message that he wanted to also filter out spaces, so I added some code
to do that.

============================
Function MyFun(S1 As String, S2 As String) As Boolean
Dim re As Object, mc As Object
Dim lMatches As Long, lNumTokens As Long
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\b(" & _
Replace(Replace(S2, " ", ""), ",", "|") & ")\b"
lNumTokens = Len(S2) - Len(Replace(S2, ",", "")) + 1
Set mc = re.Execute(S1)
If mc.Count = lNumTokens Then MyFun = True
End Function
==============================

--ron