SELECT CASE with a list of strings in CASE expression

[boim]

Hi,

I want to process table columns based on the column name. Some columns will be deleted, some will have its format changed, etc. I try the SELECT statement; however, I also want to make the delimited list in the CASE statement dynamic (not hard-coded).

Ex:

For Each col in myTable.ListColumns
    Select Case col.Name
        Case ListColName1
            do this...
        Case ListColName2
            do that...
    End Select
Next

The problem is that if I define ListColName as a string of """Name1"",""Name2"",""Name3""", it doesn't work because it's apparently regarded as just one string by VBA instead of a delimited list of strings.

So,
1. How do I go about this?
2. Perhaps a different way of doing the same job?

Thanks

 

[Jonmo1]

Dim ListColName as Variant

ListColName = Array("Name1","Name2","Name3")

then your case statements are like

Case ListColName(0) 'Name1

Case ListColName(1) 'Name2

Case ListColName(2) 'Name3


Hope that helps..

 

[boim]

Okay, after posting the problem it just occurred to me to do it the following way, I hope it can also be of help to others:

For Each col in myTable.ListColumns
    Select Case True
        Case 0 < InStr(1, ListColName1, col.Name, vbTextCompare)
            do this...
        Case 0 < InStr(1, ListColName2, col.Name, vbTextCompare)
            do that...
    End Select
Next

But if anyone has another trick, my second question is still open.

@jonmo:
Thanks, but that won't work because in each Case statement, the ListColName can contain more than one string / column name.