Since function containsAny uses some non-built VBA functions, they also must be included in your code for the function to work properly.
Otherwise the following error will occur: Compile error: Sub or Function not defined.
Required functions are listed below. You can get to each function's source code by clicking its name:
When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown: Compile error: Ambiguous name detected: function_name.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
'**********************************************************************************************************
' Name: containsAny
' Author: mielk | 2013-05-16
'
' Comment: Checks if the given string contains any of substrings given in parameter [strings].
'
' Parameters:
' isCaseSensitive Parameter defining if text matching is case sensitive.
' In most functions this parameter is optional, however since there is a ParamArray
' parameter in this function, it is not allowed to define this argument as optional.
' baseString Base string to be checked
' strings Array of strings that function will be look for in the base string.
'
' Returns:
' Boolean True - if base string contains any of substrings declared in [strings] param array.
' False - otherwise.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-05-16 mielk Class created.
'**********************************************************************************************************
Public Function containsAny(isCaseSensitive As Boolean, baseString As String, _
ParamArray strings() As Variant) As Boolean
Const METHOD_NAME As String = "containsAny"
'------------------------------------------------------------------------------------------------------
Dim varText As Variant
Dim uCompareMethod As VBA.VbCompareMethod
'------------------------------------------------------------------------------------------------------
'Convert [isCaseSensitive] parameter of Boolean type to the [VbCompareMethod] enumeration. ----------|
If isCaseSensitive Then '|
uCompareMethod = VBA.vbBinaryCompare '|
Else '|
uCompareMethod = VBA.vbTextCompare '|
End If '|
'----------------------------------------------------------------------------------------------------|
'Iterate through all the substrings declared in [strings] param array. ------------------------------|
For Each varText In strings '|
'|
'Check if this substring is contained in base string. ---------------------------------------| '|
If VBA.InStr(1, baseString, stringify(varText), uCompareMethod) Then '| '|
'| '|
containsAny = True '| '|
'| '|
'If any substring has been found in the base string, there is no point to check '| '|
'the rest of substring, since function will return True anyway. That is why the '| '|
'function leaves this loop once first substring is found. '| '|
Exit For '| '|
'| '|
End If '| '|
'--------------------------------------------------------------------------------------------| '|
'|
Next varText '|
'----------------------------------------------------------------------------------------------------|
End Function