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 ?

Florent B. · Accepted Answer · 2016-05-30 17:32:58Z

2

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

edited May 30, 2016 at 17:32

answered May 30, 2016 at 17:14

Florent B.

42.7k7 gold badges92 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Summer-Time · Accepted Answer · 2016-05-30 14:43:28Z

0

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

answered May 30, 2016 at 14:43

Summer-Time

1,89215 silver badges19 bronze badges

Comments

Nnanna · Accepted Answer · 2019-11-14 14:16:00Z

0

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.

answered Nov 14, 2019 at 14:16

Nnanna

1

Collectives™ on Stack Overflow

VBA: matching multiple strings

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related