VBA: matching multiple strings

Question

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 ?

Answer 1

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