CODEWITHSUNDEEP
vba
mynameisJEFF
mynameisJEFF

Reputation: 4239

VBA: matching multiple strings

MY question would best be understood be the following example, my goal is to classify the following string into category if the string matches any one of the strings defined in those categories. For example,

dim test_str as string

test_str = "tomato"

If the test string tomato matches any one of the keywords (1) potato, (2) tomato and (3) spaghetti, then tomato will be classified as food.

I have a very inefficient way of doing this now, which involves using multiple strcomp, i.e. 

if(strcomp(test_str, "potato", vbtextcompare) = 0 or _
strcomp(test_str, "tomato", vbtextcompare) =0 or _ 
strcomp(test_str, "spaghetti", vbtextcompare)=0 ) then 
     'label test str as "food"

However, if I have 10 keywords defined within "food", I would then need 10 strcomp statements, which would be tedious. Is there a better way to do this ?

Upvotes: 3

Views: 3671

Answers (3)

Nnanna
Nnanna

Reputation: 1

This is my first time posting; excuse my formatting. Have not been using VBA for too long but was able to piece this together.

  Sub vinden4()

  Dim EXCEPT() As String, a As Integer

  EM = "[email protected]"

  Exceptions = "no-Reply,noreply,nO.reply,"

        EXCEPT = Split(Exceptions, ",")
        For i = LBound(EXCEPT) To UBound(EXCEPT)            

NOREPLY = InStr(1, EM, EXCEPT(i), vbTextCompare)

If NOREPLY > 0 Then
'CbEM.Value = True '~food~
EM = InputBox("NOREPLY E-MAILADRES", "Geef E-mailadres aan", EM)
'else
'CbEM.Value = False ~not food~
End If

        Next i

MsgBox EM

End Sub

Hope this can help someone.

Upvotes: 0

Florent B.
Florent B.

Reputation: 42538

I would simply store all the combinations in a string and check that the value is present with InStr:

Const food = "|potato|tomato|spaghetti|"

Dim test_str As String
test_str = "tomato"

If InStr(1, food, "|" & test_str & "|", vbTextCompare) Then
  Debug.Print "food"
Else
  Debug.Print "not food"
End If

Upvotes: 2

Summer-Time
Summer-Time

Reputation: 1874

Write a function that helps you

Function ArrayWordNotInText(textValue, arrayKeyword)
   Dim i
   ArrayWordNotInText = -1
   For i = LBound(arrayKeyword) To UBound(arrayKeyword)
      If Not StrComp(textValue, arrayKeyword(i), vbTextCompare) Then ArrayWordNotInText = i
   Next i
End Function

If the return value = -1 ... no Match, >0 the index of the word

Upvotes: 0

Related Questions