find a complex string in a substring in VBA-CodePudding

how can I get in VBA a complex string in a substring, e.g. if i = InStr("test-VBA", " this is a test") then msgbox "a part of searching Item exist"

with function "Instr" didn't work because the seraching word is "test-VBA" and ofcourse dosen't

exist as a one word but what I search for if a complete part of the searching item ("test" in the

example as part of "test-VBA") exists should I get a msgbox like described above

Thanks a lot.

CodePudding user response：

Function IsInStr_IgnoreCase(ByVal Str As String, ByVal Value As String) As Boolean
Dim objRegEx as Object
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.IgnoreCase = True
    objRegEx.pattern = Value
    IsInStr_IgnoreCase = objRegEx.test(Str) ' objRegEx.test(Str) returns True if Sustr exists.
End Function


Sub test()

' this returns TRUE if you get a match
MsgBox IsInStr_IgnoreCase_AsBoolean("CHINA-Country", "Move the dev from China to Newseeland")

' this returns all matches of 'Value' in 'Str'
MsgBox IsInStr_IgnoreCase_AsString("CHINA-Country", "Move the dev from China to Newseeland")

' you need to adjust objRegEx.Pattern if you need to get a specific match. See RegEx.
End Sub


Function IsInStr_IgnoreCase_AsBoolean(ByVal Value As String, ByVal Value As String) As Boolean
Dim objRegEx As Object
Dim tStr, tVal, iStr, iVal
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.Global = True
    objRegEx.Pattern = "(\b\w )|(\b\d )"
    Set tStr = objRegEx.Execute(Str)
    Set tVal = objRegEx.Execute(Value)
    objRegEx.IgnoreCase = True
    For Each iStr In tStr
        For Each iVal In tVal
            objRegEx.Pattern = iVal
            If objRegEx.test(iStr) Then
                IsInStr_IgnoreCase_AsBoolean = True
                Exit Function
            End If
        Next
    Next
End Function


Function IsInStr_IgnoreCase_AsString(ByVal Str As String, ByVal Value As String) As String
Dim objRegEx As Object
Dim tStr, tVal, iStr, iVal
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.Global = True
    objRegEx.Pattern = "(\b\w )|(\b\d )"
    Set tStr = objRegEx.Execute(Str)
    Set tVal = objRegEx.Execute(Value)
    objRegEx.IgnoreCase = True
    For Each iStr In tStr
        For Each iVal In tVal
            objRegEx.Pattern = iVal
            If objRegEx.test(iStr) Then
                IsInStr_IgnoreCase_AsString = IsInStr_IgnoreCase_AsString & iStr & "; "
            End If
        Next
    Next
End Function