Excel Data Validation using regular expressions

[pascalemarquis]

Excel VBA Regex function to match strings

?

As indicate in here:

https://www.ablebits.com/office-addins-blog/regex-match-excel/

I am using Excel 2016:

I am copying this function in a module VBA

Public Function RegExpMatch(input_range As Range, pattern As String, Optional match_case As Boolean = True) As Variant

    Dim arRes() As Variant 'array to store the results

    Dim iInputCurRow, iInputCurCol, cntInputRows, cntInputCols As Long 'index of the current row in the source range, index of the current column in the source range, count of rows, count of columns

    On Error GoTo ErrHandl

    RegExpMatch = arRes

    Set regex = CreateObject("VBScript.RegExp")

    regex.pattern = pattern

    regex.Global = True

    regex.MultiLine = True

    If True = match_case Then

        regex.ignorecase = False

    Else

        regex.ignorecase = True

    End If

    cntInputRows = input_range.Rows.Count

    cntInputCols = input_range.Columns.Count

    ReDim arRes(1 To cntInputRows, 1 To cntInputCols)

    For iInputCurRow = 1 To cntInputRows

        For iInputCurCol = 1 To cntInputCols

            arRes(iInputCurRow, iInputCurCol) = regex.Test(input_range.Cells(iInputCurRow, iInputCurCol).Value)

        Next

    Next

    RegExpMatch = arRes

    Exit Function

    ErrHandl:

        RegExpMatch = CVErr(xlErrValue)

End Function

Save as Enable macro; Exiting VBA mode; going in Tools, Macro and my Function those not appear.

[#2]

@pascalemarquis 

A macro is a procedure (Sub) without arguments in a standard module.

A function is not a macro, so it will not be listed under macros.

You can use the function in cell formulas. The Ablebits page gives some examples.

And you can use that in data validation. I'll take their SKU example.

Let's say you want to enter SKUs in A2:A12.

Select B2.

Enter the formula =OR(A2="",RegExpMatch(A2,"\b[A-Z]{2}-\d{3}\b"))

Fill down to B12.

On the Data tab of the ribbon, click Data Validation.

Select Custom, and enter the formula =B2 in the box.

If you wish, enter an error message in the Error Alert tab.

Click OK.