VBA - Check If String Sentence Contains ANY Word From List

Question

String Sentence: "may have linked documents from another"

Word Dictionary: linked, may, from

Pseudocode:

If StringSentence containsAny(wordDictionary) Then
     MsgBox "contains a word"
Else
     MsgBox "doesn't containt any word from list"
End If

The comparison from word list to sentence should be case insensitive. I have tried creating an object with properties and using exists but I get false positives. Perhaps because of the word casing.

imo the best way to deal with *words* to avoid false substring matches and to deal with punctuation is a regex \b(linked|may|from)\b see: https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Alex K., Dec 19 '17 at 16:21

Vityata · Accepted Answer · 2017-12-19T16:41:01.090

2

The trick is to split the wordDictionary and to search for every single one of the splited items. If even one is found the boolean hasWord is changed to True. Then, based on this boolean value, the correct MsgBox text is given:

Sub TestMe()

    Dim sentence        As String
    Dim wordDictonary   As String

    Dim myArray         As Variant
    Dim cnt             As Long
    Dim hasWord         As Boolean

    sentence = "may have linked documents from another"
    wordDictonary = "linkeD, mAy, From "    
    sentence = LCase(sentence)
    wordDictonary = LCase(wordDictonary)        
    myArray = Split(wordDictonary, ",")

    For cnt = LBound(myArray) To UBound(myArray)
        If InStr(1, sentence, Trim(myArray(cnt))) Then hasWord = True : Exit For
    Next cnt

    MsgBox (IIf(hasWord, "Contains a word", "Does not contain a word!"))

End Sub

To disregard big and small letters, the sentence and the wordDictionary variables are lowercased with LCase.

edited Dec 19 '17 at 16:41

answered Dec 19 '17 at 16:19

Vityata

42,633
8
55
100

1

May as well `Exit For` if `hasWord` is `True`, speeds up execution if we don't care which word it contains – Greedo Dec 19 '17 at 16:37
@Greedo - if the `OP` abuses the code so much that `Exit For` will noticeably speed up the execution, then he/she deserves a slow code. :) But in general, it is good to add it. – Vityata Dec 19 '17 at 16:38
Worked like a charm. Much appreciated! – user3704920 Dec 19 '17 at 17:12

braX · Answer 2 · 2017-12-19T17:06:02.407

1

You can use an Array and make uses of the Instr function. How you create your array is up to you, but here's an example:

Public Sub Test()
  Dim sArray() As Variant
  Dim sSentence As String
  Dim s As Variant
  Dim sDict as String
  sDict = "linked, may, from"

  sSentence = "may have linked documents from another"
  sArray = Split(sDict,",")

  For Each s In sArray
    If InStr(1, sSentence, Trim$(s), VbCompareMethod.vbTextCompare) > 0 Then
      MsgBox ("'" & s & "' was found.")
    Else
      MsgBox ("'" & s & "' was NOT found.")
    End If
  Next

End Sub

edited Dec 19 '17 at 17:06

answered Dec 19 '17 at 16:20

braX

11,506
5
20
33

You need the status of the task for all words, not per word. E.g. `doesn't containt any word from list` or `contains a word`. – Vityata Dec 19 '17 at 16:24

VBA - Check If String Sentence Contains ANY Word From List

2 Answers2