InStr
 

I need to test strings for 14 different included strings. The InStr function
is limited to search for only 2 different strings. Is there another function
available or a work around or do I just need to code 7 if statements?

TIA

InStr
 

I would say it depends on what you wish to do. A series of if statements
will work fine though you will need 14 if statements to test for 14 different
included strings.
InStr([start],[string1],[string2],[Compare as VbCompareMethod =
VbBinaryCompare])
In the above, string1 is the string you are searching and string2 is what
you are searching for.

For simplicity, I would probably suggest putting all the strings you are
looking for in an array and checking the string against each token.

Function LookForSomething(ByVal Text As String) As Boolean
Dim Token(3) As String
Dim Index As Byte
Dim Result As Boolean
Token(0) = "one"
Token(1) = "two"
Token(2) = "three"
Token(3) = "four"

Result = True 'to match all tokens
'Result = False 'to match any token
For Index = 0 To UBound(Token)
Result = Result And InStr(1, Text, Token(Index), vbTextCompare) 0
'to match all tokens
'Result = Result Or InStr(1, Text, Token(Index), vbTextCompare) 0
'to match any token
Next
LookForSomething = Result
End Function


If you want to get a little more specific you could use a regular expression.

Add a reference to Microsoft VBScript Regular Expressions 5.5.

Function LookForSomething(ByVal Text As String) As Boolean
Dim reCheck As RegExp
Set reCheck = New RegExp
reCheck.Pattern = "\b(one|two|three|four)\b"
LookForSomething = reCheck.test(Text)
Set reCheck = Nothing
End Function

The regular expression "\b(one|two|three|four)\b" basically says match one
or two or three or four as long as they are on a word boundary (that's what
\b does). So "this is one thing" will match but "there are fourteen of them"
will not match. Regular expressions can be very flexible but they are not
always the best choice and they can be difficult to debug if they get too
complicated.

"Harley" wrote:

I need to test strings for 14 different included strings. The InStr function
is limited to search for only 2 different strings. Is there another function
available or a work around or do I just need to code 7 if statements?

TIA

InStr
 

Harley,

As far as I know the Inst function is limited to searching for only one
string within another string. Instr actually returns the position of the
searched-for string inside the searched string, returning 0 if not found.
Instead of If statements, I'd use a For-Next loop:

Sub test()
Dim searched_for_array
Dim i As Long
Dim found_position As Long

searched_for_array = Array("do", "re", "mi", "fa", "so", "la", "ti")
For i = LBound(searched_for_array) To UBound(searched_for_array)
found_position = InStr("doremice famoso latitude",
searched_for_array(i))
If found_position 0 Then
Debug.Print "String " & searched_for_array(i) & " found at " &
found_position
End If
Next i

End Sub

hth,

Doug

"Harley" wrote in message
...
I need to test strings for 14 different included strings. The InStr
function
is limited to search for only 2 different strings. Is there another
function
available or a work around or do I just need to code 7 if statements?

TIA

InStr
 

Thank you Stefan & Doug. Both of you used the array function in your sample
code.I have very little experience using arrays and this will be a good
chance to learn how to use it.

"Doug Glancy" wrote:

Harley,

As far as I know the Inst function is limited to searching for only one
string within another string. Instr actually returns the position of the
searched-for string inside the searched string, returning 0 if not found.
Instead of If statements, I'd use a For-Next loop:

Sub test()
Dim searched_for_array
Dim i As Long
Dim found_position As Long

searched_for_array = Array("do", "re", "mi", "fa", "so", "la", "ti")
For i = LBound(searched_for_array) To UBound(searched_for_array)
found_position = InStr("doremice famoso latitude",
searched_for_array(i))
If found_position 0 Then
Debug.Print "String " & searched_for_array(i) & " found at " &
found_position
End If
Next i

End Sub

hth,

Doug

"Harley" wrote in message
...
I need to test strings for 14 different included strings. The InStr
function
is limited to search for only 2 different strings. Is there another
function
available or a work around or do I just need to code 7 if statements?

TIA