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

 

[MartinS]

One last question (promise!)
I have the following syntax:
(?:ABC_202[1-9]_[MF]_\(\d_\d{2}%_A\d_\d{2}%_W\d{1,3}%\d{1,3}%\))
I also need to test for the same syntax with one or maybe two possible suffixes, i.e.
(?:ABC_202[1-9]_[MF]_\(\d_\d{2}%_A\d_\d{2}%_W\d{1,3}%\d{1,3}%\)_SUFFIX1)
(?:ABC_202[1-9]_[MF]_\(\d_\d{2}%_A\d_\d{2}%_W\d{1,3}%\d{1,3}%\)_SUFFIX2)
Can this be done in one regex? Or do I just do the two with a suffix in a single regex?:
(?:ABC_202[1-9]_[MF]_\(\d_\d{2}%_A\d_\d{2}%_W\d{1,3}%\d{1,3}%\)(?:_SUFFIX1|_SUFFIX2))
Thanks
Martin

 

[MartinS]

Working through my questions and having realised that the previous query isn't relevant in that it would only ever be SUFFIX1 or 2, I thought I had it all sorted, but when finally testing for all scenarios, for the very last regex syntax, is returning the wrong value - it's succeeding, but matching against the wrong syntax.
Example is HERE, which in regex101 selects the correct rows (lines 25 and 50), but in VBA, it returns 9?!
Here's the test function:

Function TestValidateTableName(strTableName As String) As Integer
'Declare procedure level variables
Dim blnReturnValue  As Boolean
Dim intReturnValue  As Integer
Dim i               As Integer
Dim j               As Integer
Dim k               As Integer
Dim strTableVariant As String
Dim varSyntaxList   As Variant
Dim varSyntaxSuffix As Variant
Dim regEx           As VBScript_RegExp_55.RegExp
    'Get the list of names into an array
    varSyntaxList = ThisWorkbook.Names("Regex.Syntax.List").RefersToRange.Value
    'Get the list of suffix variants into an array
    varSyntaxSuffix = ThisWorkbook.Names("Suffix.Variants").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)
        'Test the table name
        blnReturnValue = RunRegEx(strTableName, strTableVariant, regEx)
        'Exit loop when match found
        If blnReturnValue Then
            intReturnValue = i
            Exit For
        Else
            'loop through each suffix option
            For j = LBound(varSyntaxSuffix, 1) To UBound(varSyntaxSuffix, 1) Step 1
                'If the suffix is found
                If InStr(1, strTableVariant, varSyntaxSuffix(j, 1), vbTextCompare) > 0 Then
                    'Loop through each alternative
                    For k = LBound(varSyntaxSuffix, 2) + 1 To UBound(varSyntaxSuffix, 2) Step 1
                        'Reset the current variant
                        strTableVariant = varSyntaxList(i, 1)
                        'Switch the suffix fo each suggested one to test
                        strTableVariant = Replace(strTableVariant, varSyntaxSuffix(j, 1), varSyntaxSuffix(j, k), 1, , vbTextCompare)
                        'Test the table name
                        blnReturnValue = RunRegEx(strTableName, strTableVariant, regEx)
                        'Exit loop if match found
                        If blnReturnValue Then
                            intReturnValue = i
                            Exit For
                        End If
                    'Repeat for next suffix
                    Next k
                End If
                'Exit loop if match found
                If blnReturnValue Then
                    intReturnValue = i
                    Exit For
                End If
             Next j
        End If
    Next i
    'Return the value to the function
    TestValidateTableName = intReturnValue
End Function

Private Function RunRegEx(strTableName As String, strTableVariant As String, regEx As VBScript_RegExp_55.RegExp) As Boolean
'Declare procedure level variables
Dim blnReturnValue As Boolean
    'Ensure if any fail, it doesn't exit early
    On Error Resume Next
    'Check to see if the table is a match
    With regEx
        .Global = True
        .IgnoreCase = False
        .Pattern = strTableVariant
        blnReturnValue = .Test(strTableName)
    End With
    'Reset error handler
    On Error GoTo 0
    'Return result to function
    RunRegEx = blnReturnValue
End Function

The range Regex.Syntax.List is a list of all the relevant regex syntaxes (24 in total) to test against
The range Suffix.Variants is a range, two rows by 3 columns, containing in column 1:
_SUFFIX1
_SUFFIX2
And in column 3 (column 2 is blank):
_NO_TAPER
_RLGSTRESSED202[0-9]
The VBA function is matching against syntax 9, i.e.
(ABC_202[1-9]_[MF]_\(\d_\d{2}%_(S\d_\d{1,2}|A\d_\d{2}%)_W(\d{1,2}_\d|\d{1,3})%(\d{1,2}_\d|\d{1,3})%\))
instead of syntax 24, i.e.
(ABC_202[1-9]_[MF]_\(\d_\d{2}%_A\d_\d{2}%_W(\d{1,2}_\d|\d{1,3})%(\d{1,2}_\d|\d{1,3})%\)_SUFFIX2)
Anyone know why it's not matching correctly?
Thanks again
Martin

 

[Domenic]

You say that you get an error when you run the code, specifically Error 9, subscript out of range. Which line causes the error?

 

[MartinS]

You say that you get an error when you run the code, specifically Error 9, subscript out of range. Which line causes the error?

No error, it just returns the wrong match.
As you can see from the VBA code, it loops through the list of possible regex masks, looking for a match with the name passed in, but for the last example name, it returns the wrong match, i.e. the name 'ABC_2021_M_(1_50%_A0_25%_W10%10%)_RLGSTRESSED2022' matches against
(ABC_202[1-9]_[MF]_\(\d_\d{2}%_(S\d_\d{1,2}|A\d_\d{2}%)_W(\d{1,2}_\d|\d{1,3})%(\d{1,2}_\d|\d{1,3})%\))
instead of the correct syntax, i.e.
(ABC_202[1-9]_[MF]_\(\d_\d{2}%_A\d_\d{2}%_W(\d{1,2}_\d|\d{1,3})%(\d{1,2}_\d|\d{1,3})%\)_SUFFIX2)
The logic behind _SUFFIX1 and _SUFFIX2 is that should another suffix be needed, it an easily be added to the table without having to modify the existing regex masks, i.e. if we wanted to add _SUFFIX3 and _WITH_TAPER to the range Suffix.Variants, adding extra regex masks would allow the new suffix to be used. The code checks and replaces the SUFFIX text if a match against the regex initially fails, but obviously, in this case, the match is incorrect.
Happy to provide more data if that helps
Thanks
Martin

 

[MartinS]

I've (hopefully) simplified the function code, but still getting the same issue.

Function TestValidateTableName(strTableName As String) As Integer
'Declare procedure level variables
Dim blnReturnValue  As Boolean
Dim intReturnValue  As Integer
Dim i               As Integer
Dim j               As Integer
Dim strRegexMask As String
Dim varSyntaxList   As Variant
Dim varSyntaxSuffix As Variant
Dim regEx           As VBScript_RegExp_55.RegExp
    'Get the list of names into an array
    varSyntaxList = ThisWorkbook.Names("Regex.Syntax.List").RefersToRange.Value
    'Get the list of suffix variants into an array
    varSyntaxSuffix = ThisWorkbook.Names("Suffix.Variants").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
        strRegexMask = varSyntaxList(i, 1)
        'Test the table name
        blnReturnValue = RunRegEx(strTableName, strRegexMask, regEx)
        'Exit loop when match found
        If blnReturnValue Then
            intReturnValue = i
            Exit For
        End If
        'Otherwise, loop through each suffix option
        For j = LBound(varSyntaxSuffix, 1) To UBound(varSyntaxSuffix, 1) Step 1
            'If the suffix is found in the mask
            If InStr(1, strRegexMask, varSyntaxSuffix(j, 1), vbTextCompare) > 0 Then
                'Reset the current variant
                strRegexMask = varSyntaxList(i, 1)
                'Switch the suffix fo each suggested one to test
                strRegexMask = Replace(strRegexMask, varSyntaxSuffix(j, 1), varSyntaxSuffix(j, 2), 1, , vbTextCompare)
                'Test the table name
                blnReturnValue = RunRegEx(strTableName, strRegexMask, regEx)
                'Exit loop if match found
                If blnReturnValue Then
                    intReturnValue = i
                    Exit For
                End If
            End If
        Next j
        'Exit outer loop if match found
        If blnReturnValue Then Exit For
    Next i
    'Return the value to the function
    TestValidateTableName = intReturnValue
End Function

Private Function RunRegEx(strTableName As String, strTableVariant As String, regEx As VBScript_RegExp_55.RegExp) As Boolean
'Declare procedure level variables
Dim blnReturnValue As Boolean
    'Ensure if any fail, it doesn't exit early
    On Error Resume Next
    'Check to see if the table is a match
    With regEx
        .Global = True
        .IgnoreCase = False
        .Pattern = strTableVariant
        blnReturnValue = .Test(strTableName)
    End With
    'Reset error handler
    On Error GoTo 0
    'Return result to function
    RunRegEx = blnReturnValue
End Function

Thanks

 

[Domenic]

Sorry, when you said that it returned 9, somehow I mistakenly thought that you meant that you got a runtime error 9.

Assuming that each name/string starts at the beginning of a line and ends at the end of a line, as per your sample data, use ^ to assert the start of the line and $ to assert the end of a line...

^(ABC_202 . . . )$

Hope this helps!

 

[MartinS]

Sorry, when you said that it returned 9, somehow I mistakenly thought that you meant that you got a runtime error 9.

Assuming that each name/string starts at the beginning of a line and ends at the end of a line, as per your sample data, use ^ to assert the start of the line and $ to assert the end of a line...

^(ABC_202 . . . )$

Hope this helps!

Sorry for the confusion...
Perfect!! Many thanks