Loop Using List of RegEx in worksheet

[MartinS]

I have a workbook 'tool' which needs to validate the names given to the files being output.
The validation requires that, where the filename starts with 'ABC_', it should be one of a set of known syntaxes, i.e.
ABC_2020_[MF]_(#_##%_S#_#_A#_##%_W#%) i.e. ABC_2020_M_(1_50%_S7_5_A0_25%_W1%)
ABC_2020_[MF]_(#_##%_S#_#_A#_##%_W##%) i.e. ABC_2020_M_(1_50%_S7_5_A0_25%_W10%)
ABC_2020_[MF]_(#_##%_S#_#_A#_##%_W###%) i.e. ABC_2020_M_(1_50%_S7_5_A0_25%_W100%)
ABC_2020_[MF]_(#_##%_S#_##_A#_##%_W#%) i.e. ABC_2020_M_(1_50%_S7_55_A0_25%_W1%)
ABC_2020_[MF]_(#_##%_S#_##_A#_##%_W##%) i.e. ABC_2020_M_(1_50%_S7_55_A0_25%_W10%)
ABC_2020_[MF]_(#_##%_S#_##_A#_##%_W###%) i.e. ABC_2020_M_(1_50%_S7_55_A0_25%_W100%)
ABC_202[1-9]_[MF]_(#_##%_W#%#%) i.e. ABC_2021_M_(1_50%_W0%1%)
ABC_202[2-9]_[MF]_(#_##%_S#_#_A#_##%_W#%#%#%) i.e. ABC_2022_M_(1_50%_S7_5_A0_25%_W0%0%1%)
ABC_202[2-9]_[MF]_(#_##%_S#_#_A#_##%_W#%#%##%) i.e. ABC_2022_M_(1_50%_S7_5_A0_25%_W0%0%25%)
ABC_202[2-9]_[MF]_(#_##%_S#_#_A#_##%_W#%#%###%) i.e. ABC_2022_M_(1_50%_S7_5_A0_25%_W0%0%100%)
ABC_202[2-9]_[MF]_(#_##%_S#_#_A#_##%_W#%##%#%) i.e. ABC_2022_M_(1_50%_S7_5_A0_25%_W0%15%1%)
ABC_202[2-9]_[MF]_(#_##%_S#_#_A#_##%_W#%##%##%) i.e. ABC_2022_M_(1_50%_S7_5_A0_25%_W0%15%20%)
ABC_202[2-9]_[MF]_(#_##%_S#_#_A#_##%_W#%##%###%) i.e. ABC_2022_M_(1_50%_S7_5_A0_25%_W0%15%100%)
ABC_202[2-9]_[MF]_(#_##%_S#_#_A#_##%_W###%###%###%) i.e. ABC_2022_M_(1_50%_S7_5_A0_25%_W100%100%100%)
The loop goes through the list (currently at 140 rows) and uses LIKE to see if the name provided matches one of the known syntaxes, i.e. ABC_2022_M_(1_50%_S7_5_A0_25%_W0%0%1%) would match the hi-lighted line above, and exit the loop. If no match found, the file isn't output.

Private Function ValidateTableName(strTableName As String) As Boolean
Dim blnReturnValue  As Boolean
Dim i               As Integer
Dim strTableVariant As String
Dim varSyntaxList   As Variant
    'Get the list of names into an array
    varSyntaxList = ThisWorkbook.Names("Improvement.Syntax.List").RefersToRange.Value
    'loop through the array
    For i = LBound(varSyntaxList) To UBound(varSyntaxList) Step 1
        'Get the current variant
        strTableVariant = varSyntaxList(i, 1)
        'Check to see if the table is like the variant
        If strTableName Like strTableVariant Then
            blnReturnValue = True
            Exit For
        End If
    Next i
    'Return the value to the function
    ValidateTableName = blnReturnValue
End Function

What I don't like is that there are in the examples above 7 lines that would allow the final three values to be any value from 0 to 100. The letters S, A and W are all fixed, as are the underscores.
Is there a way to supply this list in regex format in a worksheet and use that list to validate, i.e. pass the regex as a string? I'm expecting regex to make this list much shorter!
Thanks
Martin

 

[Domenic]

The following code assumes that the sheet containing the data is the active sheet, and that Column A contains the data starting at cell A1. Note that you'll need to set a reference (Visual Basic Editor >> Tools >> References) to VBScript Regular Expressions X.X.

Option Explicit

Dim regExp As VBScript_RegExp_55.regExp

Sub test()

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    Dim i As Long
    For i = 1 To lastRow
        Cells(i, "B").Value = ValidateTableName(Cells(i, "A").Value)
    Next i
   
End Sub


Private Function ValidateTableName(strTableName As String) As Boolean

    If regExp Is Nothing Then
        Set regExp = New VBScript_RegExp_55.regExp
    End If
   
    With regExp
        .Global = True
        .IgnoreCase = True
        .MultiLine = True
        .Pattern = "(?:ABC_202[02-9]_[MF]_\(\d_\d{2}%_S\d_\d{1,2}_A\d_\d{2}%_W\d{1,3}%(?:\d{1,3}%\d{1,3}%)?\))|(?:ABC_202[1-9]_[MF]_\(\d_\d{2}%_W\d%\d%\))"
        ValidateTableName = .test(strTableName)
    End With

End Function

Here's a small sample of data. However, please test for all possible formats...

martin.xlsm
	A	B
1	ABC_2020_M_(1_50%_S7_5_A0_25%_W1%)	TRUE
2	ABC_2020_M_(1_50%_S7_5_A0_25%_W10%)	TRUE
3	ABC_2020_M_(1_50%_S7_5_A0_25%_W100%)	TRUE
4	ABC_2021_M_(1_50%_S7_5_A0_25%_W1%)	FALSE
5	ABC_2020_M_(1_50%_S7_55_A0_25%_W1%)	TRUE
6	ABC_2020_M_(1_50%_S7_55_A0_25%_W10%)	TRUE
7	ABC_2020_M_(1_50%_S7_55_A0_25%_W100%)	TRUE
8	ABC_2021_M_(1_50%_W0%1%)	TRUE
9	ABC_2020_M_(1_50%_W0%1%)	FALSE
10	ABC_2022_M_(1_50%_S7_5_A0_25%_W0%0%1%)	TRUE
11	ABC_2022_M_(1_50%_S7_5_A0_25%_W0%0%25%)	TRUE
12	ABC_2022_M_(1_50%_S7_5_A0_25%_W0%0%100%)	TRUE
13	ABC_2022_M_(1_50%_S7_5_A0_25%_W0%15%1%)	TRUE
14	ABC_2022_M_(1_50%_S7_5_A0_25%_W0%15%20%)	TRUE
15	ABC_2022_M_(1_50%_S7_5_A0_25%_W0%15%100%)	TRUE
16	ABC_2022_M_(1_50%_S7_5_A0_25%_W100%100%100%)	TRUE
17	ABC_2020_F_(1_50%_S7_5_A0_25%_W1%)	TRUE
18	ABC_2020_F_(1_50%_S7_5_A0_25%_W10%)	TRUE
19	ABC_2020_F_(1_50%_S7_5_A0_25%_W100%)	TRUE
20	ABC_2021_F_(1_50%_S7_5_A0_25%_W1%)	FALSE
21	ABC_2020_F_(1_50%_S7_55_A0_25%_W1%)	TRUE
22	ABC_2020_F_(1_50%_S7_55_A0_25%_W10%)	TRUE
23	ABC_2020_F_(1_50%_S7_55_A0_25%_W100%)	TRUE
24	ABC_2021_F_(1_50%_W0%1%)	TRUE
25	ABC_2020_F_(1_50%_W0%1%)	FALSE
26	ABC_2022_F_(1_50%_S7_5_A0_25%_W0%0%1%)	TRUE
27	ABC_2022_F_(1_50%_S7_5_A0_25%_W0%0%25%)	TRUE
28	ABC_2022_F_(1_50%_S7_5_A0_25%_W0%0%100%)	TRUE
29	ABC_2022_F_(1_50%_S7_5_A0_25%_W0%15%1%)	TRUE
30	ABC_2022_F_(1_50%_S7_5_A0_25%_W0%15%20%)	TRUE
31	ABC_2022_F_(1_50%_S7_5_A0_25%_W0%15%100%)	TRUE
32	ABC_2022_F_(1_50%_S7_5_A0_25%_W100%100%100%)	TRUE
Sheet1

Regular Expression Example

Hope this helps!

 

[MartinS]

Thanks @Domenic
Just to clarify, the list of syntaxes would, ideally be the regular expression pattern, so for example, cell A1 would contain:
(?:ABC_202[02-9]_[MF]_\(\d_\d{2}%_S\d_\d{1,2}_A\d_\d{2}%_W\d{1,3}%(?:\d{1,3}%\d{1,3}%)?\))
And A2 would be
(?:ABC_202[1-9]_[MF]_\(\d_\d{2}%_W\d%\d%\))
And so on, so I'd expect there to be maybe a dozen different syntaxes.
I wanted to be sure that 'pattern' would accept a string, and based on your reply, I'd expect my existing function to change to:

Private Function ValidateTableName(strTableName As String) As Boolean
Dim blnReturnValue  As Boolean
Dim i               As Integer
Dim strTableVariant As String
Dim varSyntaxList   As Variant
    'Get the list of names into an array
    varSyntaxList = ThisWorkbook.Names("Improvement.Syntax.List").RefersToRange.Value
    If regExp Is Nothing Then
        Set regExp = New VBScript_RegExp_55.regExp
    End If
    'loop through the array
    For i = LBound(varSyntaxList) To UBound(varSyntaxList) Step 1
        'Get the current variant
        strTableVariant = varSyntaxList(i, 1)
        'Check to see if the table is like the variant
         With regExp
              .Global = True
              .IgnoreCase = True
              .MultiLine = True
              .Pattern = strTableVariant
              blnReturnValue = .Test(strTableName)
        End With
        If blnReturnValue Then Exit For
    Next i
    'Return the value to the function
    ValidateTableName = blnReturnValue
End Function

Based on your example above, you've answered my question - apologies I didn't make it clear what I was trying to achieve.
Many thanks
Martin

 

[MartinS]

One more question re: regex, how can you search for a number in the range 2016-2029?
I currently have two lines:
ABC_201[8-9]...
ABC_202[0-9]...
Is there a way to merge these into one row?
Thanks again
Martin

 

[Domenic]

See if this helps...

(?:ABC_20(?:1[8-9]|2[0-9])_[MF]_\(\d_\d{2}%_S\d_\d{1,2}_A\d_\d{2}%_W\d{1,3}%(?:\d{1,3}%\d{1,3}%)?\))|(?:ABC_202[1-9]_[MF]_\(\d_\d{2}%_W\d%\d%\))

 

[MartinS]

See if this helps...

(?:ABC_20(?:1[8-9]|2[0-9])_[MF]_\(\d_\d{2}%_S\d_\d{1,2}_A\d_\d{2}%_W\d{1,3}%(?:\d{1,3}%\d{1,3}%)?\))|(?:ABC_202[1-9]_[MF]_\(\d_\d{2}%_W\d%\d%\))

Perfect! Many thanks

 

[MartinS]

Can anyone help me understand why some syntaxes work in the regex101 builder, but not using VBA? I've saved some working examples to show my regex and the examples to validate against.
When testing the code in VBA, for the examples shown, I'm getting the error 'Method 'Test' of object 'IRegExp2' failed' using the following code (with error handling to confirm it works/show errors):

Function ValidateTableName(strTableName As String) As Variant
'Declare procedure level variables
Dim blnReturnValue  As Boolean
Dim i               As Integer
Dim strTableVariant As String
Dim varSyntaxList   As Variant
Dim regEx           As VBScript_RegExp_55.RegExp
    'Clear any existing errors
    Err.Clear
    'Ensure any errors get caught
    On Error GoTo TestFailed
    'Get the list of names into an array
    varSyntaxList = ThisWorkbook.Names("Regex.Syntax.List").RefersToRange.Value
    'Create reference to regular expressions
    If regEx Is Nothing Then
        Set regEx = New VBScript_RegExp_55.RegExp
    End If
    'loop through the array
    For i = LBound(varSyntaxList) To UBound(varSyntaxList) Step 1
        'Get the current variant
        strTableVariant = varSyntaxList(i, 1)
        'Check to see if the table is like the variant
         With regEx
            .Global = True
            .IgnoreCase = False
            .Pattern = strTableVariant
            blnReturnValue = .Test(strTableName)
        End With
        If blnReturnValue Then
            Exit For
        End If
    Next i
    'Return the value to the function
    ValidateTableName = blnReturnValue
'Normal exit point
Exit Function
'Line label
TestFailed:
    'Return the error detail
    ValidateTableName = Err.Description
End Function

Of the 42 regex syntaxes created, 25 work as expected, but the remaining 17 don't, all returning the same error.
Thanks in advance
Martin

 

[Domenic]

Can you post the regular expressions that cause an error?

Also, can you confirm which line causes the error?

 

[MartinS]

Can you post the regular expressions that cause an error?

Also, can you confirm which line causes the error?

That's odd, but actually, I've just finished looking at it and having removed some regex not being found, I also noted a spurious character in one syntax which after I removed it, they all worked.
So I'm all good now - thanks for your reply.

 

[Domenic]

That's great, glad you were able to figure it out.

Cheers!