InStr for array of values (Possible?)

excel, vba

Solution

Use this if `WBname` must contain all words

Dim WBname As String
WBname = ThisWorkbook.Name

Dim arrWords As Variant, aWord As Variant
arrWords = Array("aa", "bb", "cc") 'input your words list here

For Each aWord In arrWords
    If Not InStr(WBname, aWord) > 0 Then
        MsgBox ("NotOK")
        Exit For
    End If
Next

Use this if `WBname` must contain at least one word

Dim WBname As String
WBname = ThisWorkbook.Name

Dim arrWords As Variant, aWord As Variant
arrWords = Array("aa", "bb", "cc") 'input your words list here

Dim wordFound As Boolean
wordFound = False
For Each aWord In arrWords
    If InStr(WBname, aWord) > 0 Then
        wordFound = True
        Exit For
    End If
Next
If Not wordFound Then
    MsgBox ("NotOK")
End If

Problem

``` Private Sub Workbook_Open() Dim WBname As String WBname = ThisWorkbook.name If Not InStr(WBname, "test") > 0 Then MsgBox ("NotOK") End If End Sub ``` EDIT: For more clarification. I now test if "Test" is in the Workbook name. But I want to test if more words than just "Test" are in the Workbook name without copy-pasting the code a thousand times.

Original source