Loop Using List of RegEx in worksheet

MartinS

MartinS

Active Member
Joined
Jun 17, 2003
Messages
487
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code: 
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
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.

VBA Code: 
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
AB
1ABC_2020_M_(1_50%_S7_5_A0_25%_W1%)TRUE
2ABC_2020_M_(1_50%_S7_5_A0_25%_W10%)TRUE
3ABC_2020_M_(1_50%_S7_5_A0_25%_W100%)TRUE
4ABC_2021_M_(1_50%_S7_5_A0_25%_W1%)FALSE
5ABC_2020_M_(1_50%_S7_55_A0_25%_W1%)TRUE
6ABC_2020_M_(1_50%_S7_55_A0_25%_W10%)TRUE
7ABC_2020_M_(1_50%_S7_55_A0_25%_W100%)TRUE
8ABC_2021_M_(1_50%_W0%1%)TRUE
9ABC_2020_M_(1_50%_W0%1%)FALSE
10ABC_2022_M_(1_50%_S7_5_A0_25%_W0%0%1%)TRUE
11ABC_2022_M_(1_50%_S7_5_A0_25%_W0%0%25%)TRUE
12ABC_2022_M_(1_50%_S7_5_A0_25%_W0%0%100%)TRUE
13ABC_2022_M_(1_50%_S7_5_A0_25%_W0%15%1%)TRUE
14ABC_2022_M_(1_50%_S7_5_A0_25%_W0%15%20%)TRUE
15ABC_2022_M_(1_50%_S7_5_A0_25%_W0%15%100%)TRUE
16ABC_2022_M_(1_50%_S7_5_A0_25%_W100%100%100%)TRUE
17ABC_2020_F_(1_50%_S7_5_A0_25%_W1%)TRUE
18ABC_2020_F_(1_50%_S7_5_A0_25%_W10%)TRUE
19ABC_2020_F_(1_50%_S7_5_A0_25%_W100%)TRUE
20ABC_2021_F_(1_50%_S7_5_A0_25%_W1%)FALSE
21ABC_2020_F_(1_50%_S7_55_A0_25%_W1%)TRUE
22ABC_2020_F_(1_50%_S7_55_A0_25%_W10%)TRUE
23ABC_2020_F_(1_50%_S7_55_A0_25%_W100%)TRUE
24ABC_2021_F_(1_50%_W0%1%)TRUE
25ABC_2020_F_(1_50%_W0%1%)FALSE
26ABC_2022_F_(1_50%_S7_5_A0_25%_W0%0%1%)TRUE
27ABC_2022_F_(1_50%_S7_5_A0_25%_W0%0%25%)TRUE
28ABC_2022_F_(1_50%_S7_5_A0_25%_W0%0%100%)TRUE
29ABC_2022_F_(1_50%_S7_5_A0_25%_W0%15%1%)TRUE
30ABC_2022_F_(1_50%_S7_5_A0_25%_W0%15%20%)TRUE
31ABC_2022_F_(1_50%_S7_5_A0_25%_W0%15%100%)TRUE
32ABC_2022_F_(1_50%_S7_5_A0_25%_W100%100%100%)TRUE
Sheet1


Regular Expression Example

Hope this helps!
 
Upvote 0
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:
VBA Code: 
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
 
Upvote 0
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
 
Upvote 0
See if this helps...

VBA Code: 
(?: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%\))
 
Upvote 0
Domenic said:
See if this helps...

VBA Code: 
(?: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%\))
Click to expand...
Perfect! Many thanks
 
Upvote 0
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):
VBA Code: 
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
 
Upvote 0
Can you post the regular expressions that cause an error?

Also, can you confirm which line causes the error?
 
Upvote 0
Domenic said:
Can you post the regular expressions that cause an error?

Also, can you confirm which line causes the error?
Click to expand...
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.
 
Upvote 0
That's great, glad you were able to figure it out.

Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

MartinS
  • Solved
Evaluate Constant Names in a Loop
Replies
3
Views
346
RoryA
RoryA

Forum statistics

Threads
1,215,139
Messages
6,123,263
Members
449,093
Latest member
Vincent Khandagale

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top