May 05 2022 09:05 AM
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.
May 05 2022 09:32 AM
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.