Evaluate Constant Names in a Loop

[MartinS]

Morning
Might be a better way to do this, but what I have is a list of 136 (at present) possibilities for the format of an input string, and I need to validate the input to ensure it is correct.
Initially I had a long list of string Like constants but it's jumped from 78 possibilities to 136 and the number of items is jut too many to try and use.
Here's the code (will only include 30 rows for simplicity):

Function ValidateCMITable(strTableName As String) As Boolean
'Define the required syntax formats
Const cstrSyntax001 As String = "CMI_20[0-2][0-9]_[MF]_(#_##%)" 'i.e. CMI_2020_M_(1_50%)
Const cstrSyntax002 As String = "CMI_201[6-9]_[MF]_(#_##%_S#_#)" 'i.e. CMI_2017_M_(1_50%_S7_5)
Const cstrSyntax003 As String = "CMI_202[0-9]_[MF]_(#_##%_S#_#)" 'i.e. CMI_2021_M_(1_50%_S7_5)
Const cstrSyntax004 As String = "CMI_201[6-9]_[MF]_(#_##%_S#_##)" 'i.e. CMI_2017_M_(1_50%_S7_55)
Const cstrSyntax005 As String = "CMI_202[0-9]_[MF]_(#_##%_S#_##)" 'i.e. CMI_2021_M_(1_50%_S7_55)
Const cstrSyntax006 As String = "CMI_201[8-9]_[MF]_(#_##%_A#_##%)" 'i.e. CMI_2019_M_(1_50%_A0_25%)
Const cstrSyntax007 As String = "CMI_202[0-9]_[MF]_(#_##%_A#_##%)" 'i.e. CMI_2021_M_(1_50%_A0_25%)
Const cstrSyntax008 As String = "CMI_201[8-9]_[MF]_(#_##%_S#_#_A#_##%)" 'i.e. CMI_2018_M_(1_50%_S7_5_A0_25%)
Const cstrSyntax009 As String = "CMI_202[0-9]_[MF]_(#_##%_S#_#_A#_##%)" 'i.e. CMI_2021_M_(1_50%_S7_5_A0_25%)
Const cstrSyntax010 As String = "CMI_201[8-9]_[MF]_(#_##%_S#_##_A#_##%)" 'i.e. CMI_2018_M_(1_50%_S7_55_A0_25%)
Const cstrSyntax011 As String = "CMI_202[0-9]_[MF]_(#_##%_S#_##_A#_##%)" 'i.e. CMI_2021_M_(1_50%_S7_55_A0_25%)
Const cstrSyntax012 As String = "CMI_2020_[MF]_(#_##%_W#%)" 'i.e. CMI_2020_M_(1_50%_W1%)
Const cstrSyntax013 As String = "CMI_2020_[MF]_(#_##%_W##%)" 'i.e. CMI_2020_M_(1_50%_W10%)
Const cstrSyntax014 As String = "CMI_2020_[MF]_(#_##%_W100%)" 'i.e. CMI_2020_M_(1_50%_W100%)
Const cstrSyntax015 As String = "CMI_2020_[MF]_(#_##%_A#_##%_W#%)" 'i.e. CMI_2020_M_(1_50%_A0_25%_W1%)
Const cstrSyntax016 As String = "CMI_2020_[MF]_(#_##%_A#_##%_W##%)" 'i.e. CMI_2020_M_(1_50%_A0_25%_W10%)
Const cstrSyntax017 As String = "CMI_2020_[MF]_(#_##%_A#_##%_W100%)" 'i.e. CMI_2020_M_(1_50%_A0_25%_W100%)
Const cstrSyntax018 As String = "CMI_2020_[MF]_(#_##%_S#_#_W#%)" 'i.e. CMI_2020_M_(1_50%_S7_5_W1%)
Const cstrSyntax019 As String = "CMI_2020_[MF]_(#_##%_S#_#_W##%)" 'i.e. CMI_2020_M_(1_50%_S7_5_W10%)
Const cstrSyntax020 As String = "CMI_2020_[MF]_(#_##%_S#_#_W100%)" 'i.e. CMI_2020_M_(1_50%_S7_5_W100%)
Const cstrSyntax021 As String = "CMI_2020_[MF]_(#_##%_S#_##_W#%)" 'i.e. CMI_2020_M_(1_50%_S7_55_W1%)
Const cstrSyntax022 As String = "CMI_2020_[MF]_(#_##%_S#_##_W##%)" 'i.e. CMI_2020_M_(1_50%_S7_55_W10%)
Const cstrSyntax023 As String = "CMI_2020_[MF]_(#_##%_S#_##_W100%)" 'i.e. CMI_2020_M_(1_50%_S7_55_W100%)
Const cstrSyntax024 As String = "CMI_2020_[MF]_(#_##%_S#_#_A#_##%_W#%)" 'i.e. CMI_2020_M_(1_50%_S7_5_A0_25%_W1%)
Const cstrSyntax025 As String = "CMI_2020_[MF]_(#_##%_S#_#_A#_##%_W##%)" 'i.e. CMI_2020_M_(1_50%_S7_5_A0_25%_W10%)
Const cstrSyntax026 As String = "CMI_2020_[MF]_(#_##%_S#_#_A#_##%_W100%)" 'i.e. CMI_2020_M_(1_50%_S7_5_A0_25%_W100%)
Const cstrSyntax027 As String = "CMI_2020_[MF]_(#_##%_S#_##_A#_##%_W#%)" 'i.e. CMI_2020_M_(1_50%_S7_55_A0_25%_W1%)
Const cstrSyntax028 As String = "CMI_2020_[MF]_(#_##%_S#_##_A#_##%_W##%)" 'i.e. CMI_2020_M_(1_50%_S7_55_A0_25%_W10%)
Const cstrSyntax029 As String = "CMI_2020_[MF]_(#_##%_S#_##_A#_##%_W100%)" 'i.e. CMI_2020_M_(1_50%_S7_55_A0_25%_W100%)
Const cstrSyntax030 As String = "CMI_202[1-9]_[MF]_(#_##%_W0%#%)" 'i.e. CMI_2021_M_(1_50%_W0%1%)

    'Validate table name against defined syntax list
    ValidateCMITable = (strTableName Like cstrSyntax01 Or strTableName Like cstrSyntax02 Or strTableName Like cstrSyntax03 Or strTableName Like cstrSyntax04 _
            Or strTableName Like cstrSyntax05 Or strTableName Like cstrSyntax06 Or strTableName Like cstrSyntax07 Or strTableName Like cstrSyntax08 _
            Or strTableName Like cstrSyntax09 Or strTableName Like cstrSyntax10 Or strTableName Like cstrSyntax11 Or strTableName Like cstrSyntax12 _
            Or strTableName Like cstrSyntax13 Or strTableName Like cstrSyntax14 Or strTableName Like cstrSyntax15 Or strTableName Like cstrSyntax16 _
            Or strTableName Like cstrSyntax17 Or strTableName Like cstrSyntax18 Or strTableName Like cstrSyntax19 Or strTableName Like cstrSyntax20 _
            Or strTableName Like cstrSyntax21 Or strTableName Like cstrSyntax22 Or strTableName Like cstrSyntax23 Or strTableName Like cstrSyntax24 _
            Or strTableName Like cstrSyntax25 Or strTableName Like cstrSyntax26 Or strTableName Like cstrSyntax27 Or strTableName Like cstrSyntax28 _
            Or strTableName Like cstrSyntax29 Or strTableName Like cstrSyntax30)
End Function

So I was thinking could this be done with an array, but wasn't sure how to convert a constant name into a value:

'Declare procedure level variables
Dim blnReturnValue As Boolean
Dim i As Integer
    For i = 1 To 30 Step 1
        If strTableName Like Evaluate("cstrSyntax" & Format(i, "00")) Then
            blnReturnValue = True
        End If
    Next i

Hope that makes sense! I suspect I could simply move the list of constants to a sheet and Evaluate would work by looping through a range, but I was just curious if my logic was possible?
Thanks in advance
Martin

 

[RoryA]

I would definitely move it to a range, but there are a few ways you could do it. For example, you could make the constants into public members of a class, or just use a function that returns an array of the values.

 

[MartinS]

Thanks
I've moved the list to a range, and this works perfectly:

Dim blnReturnValue  As Boolean
Dim i               As Integer
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
        'change flag when a match found
        If strTableName Like varSyntaxList(i) Then
            blnReturnValue = True
        End If
    Next i
   
    ValidateCMITable = blnReturnValue

Intrigued to know how you could do it with your suggestion of public members of a class?
Thanks
Martin

 

[RoryA]

You'd need to either create property procedures for each (or one that returns an array of all of them) or use variables instead (you can't have public constants in a class). You can then get the values using CallByName and passing the name of the relevant property/variable. More trouble than it's worth, IMO.