VBA：Excel ：Tips：正規表現

	B	C	D
			..(BD|EF).......
7	TRG	PTN	RES
8	ABCDEFGHIJK	..(CD|EF).......	1
9	ABCDEFGHIJK	..(CD|EF).......	1
…

Sub Sample()

    Dim arr(0 To 20000) As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    
    Dim i As Long
    Dim sTrg As String
    Dim sPtn As String
    For i = LBound(arr) To UBound(arr)
        DoEvents
        sTrg = ws.Cells(7 + i, "B").Value
        sPtn = ws.Cells(7 + i, "C").Value
        arr(i) = myFncRegExp(sTrg, sPtn)
        'Debug.Print i
        
    Next i
    
    ws.Range(Cells(7, "D"), Cells(7 + UBound(arr), "D")) = WorksheetFunction.Transpose(arr)

End Sub

Public Function myFncRegExp( _
                            ByVal argTrg As String, _
                            ByVal argPtn As String _
                            ) As Integer
                            
    If argTrg = "" Then
        myFncRegExp = -1
        Exit Function
    End If
                            
    Dim bRes As Boolean
    Dim RE, strPattern As String
    Set RE = CreateObject("VBScript.RegExp")
    With RE
        .Pattern = argPtn       ''検索パターンを設定
        .IgnoreCase = True          ''大文字と小文字を区別しない
        .Global = True              ''文字列全体を検索
        bRes = .Test(argTrg)
    End With
    
    myFncRegExp = IIf(bRes, 1, 0)
    

End Function