concat in excel code example

Example 1: excel vba multiple string search with InStr function

'VBA function to check if ANY of a list of substrings is contained
'within a string:

Function AnyIn(s$, ParamArray checks()) As Boolean
    Dim e
    For Each e In checks
        If InStrB(s, e) Then AnyIn = True: Exit Function
    Next
End Function
  
'-------------------------------------------------------------------
  
MsgBox AnyIn("abcde", "o", 5, "z", "a")   '<--displays: True
MsgBox AnyIn("abcde", "o", 5, "z", "p")   '<--displays: False

Example 2: excel vba replaceall in string

'VBA function to make multiple replacements in a template string:

Function TemplateReplace$(template$, ParamArray replacements())
    Dim c&, s$, t$, e
    s = template
    For Each e In replacements
        c = c + 1
        t = "|%" & c & "|"
        If InStrB(e, "~~") Then e = Replace(e, "~~", Chr(34))
        If InStrB(s, t) Then s = Replace(s, t, e)
    Next
    TemplateReplace = s
End Function

'--------------------------------------------------------------------

Const TEMPLATE = "SELECT * FROM |%1| WHERE (survived = |%2|)"
MsgBox TemplateReplace$(TEMPLATE, "titanic", "~~true~~")

'Displays: SELECT * FROM titanic WHERE (survived = "true")