Is there a limit to how many terms you can stick in a Case Selection?

[Aussiebear]

I recently came across a post where the creator of the code used this:

Sub deleteIrrelevantColumns()
Dim currentColumn As Integer
Dim columnHeading As String
' ActiveSheet.Columns("G").Delete
' List = Sheets("to keep").Range("D1:D30")
currentColumn = 5
For currentColumn = ActiveSheet.UsedRange.Columns.Count To 29 Step -1  ' start from last col to 29 onwards only
columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value
    'CHECK WHETHER TO KEEP THE COLUMN
    Select Case columnHeading
        ' Insert list reference here instead of specifying in code
        ' headerstodelete
        Case "Acq_WK_1", "Acq_WK_20", "Acq_WK_34", "Area_Hemel_Hempstead", "Area_Reading", "Area_South_West_London", "ctype_guest", "email", "fo_Category_KNITWEAR", _
        "fo_Category_OUTERWEAR", "fo_Category_WOVEN", "fo_Category_WOVEN_TROUSERS", "fo_device_mobile", "fo_discount_dummy", "fo_discountandfreedelivery_dummy", _
        "fo_discountrate", "fo_freedelivery_dummy", "fo_Mth_Aug", "fo_part_returner_dummy", "fo_total_discountorderstable", "fo_totalvalue", "fo_visit_cpc", "fo_visit_display", _
        "fo_visit_email", "Forecast", "mailedbook_andy", "multi_order_customer", "recency_dayssincelastorder", "visits", "gets_email_andyandnotsubscribed", "Acq_MTH_1", "Acq_MTH_10", _
        "Acq_MTH_11", "Acq_MTH_3", "Acq_MTH_4", "Acq_MTH_5", "Acq_MTH_6", "Acq_MTH_9", "Acq_WK_10", "Acq_WK_16", "Acq_WK_19", "Acq_WK_40", "acquisition_year", _
        "age_missing_dummy", "age_nullsreplavg", "Area_Aberdeen", "Area_Guildford", "Area_North_London", "Area_Redhill", "Area_South_West_London", "Area_West_London", _
        "cold_book_redeem", "ctype_customer", "ctype_guest", "ctype_prospect", "gender_missing_dummy", "gendermale_dummy", "gets_email_andy", "households_avg_repzero", _
        "location_london", "mailedbook_andy", "no_postcode_dummy", "population_avg_repzero", "propensityscore_andy", "unsubscribed_from_email", "visits"
            'Do nothing
        Case Else
            'Delete if the cell doesn't contain "Homer"
            If InStr(1, ActiveSheet.UsedRange.Cells(1, currentColumn).Value, "Homer", vbBinaryCompare) = 0 Then
                ActiveSheet.Columns(currentColumn).Delete
            End If
    End Select
Next
End Sub

Where the first Case is stuffed full of everything but the kitchen sink, only to do nothing if true. Whereas the real test is hidden in the second Case "if it didn't contain the value "Homer" then delete the column.

Clearly the logic here was if the value "Homer" didn't exist then delete the column, so why go to all the hassle of the first case listing all the exemptions. So ignoring the logic of this for a moment, is there a limit to how much you can stick inside a Case (definitions)?

[arnelgp]

you can also ask Copilot or ChatGPT.

[Aussiebear]

I'm not asking Copilot or ChatGPT, as I am clearly asking the members here.

[Bob Phillips]

I am sure that there is, although I wouldn't want to write or see the code that gets to that point, but at the very least he will run into too many line continuations at some point.

Wouldn't be surprised if that code was written by ChatGPT. I saw this AI generated code recently

    Set tbl = Activesheet.Listobjects(1)
    tbl.Delete
    tbl.ClearFormats

When everyone says that AI will replace coders and the code will be better, just think of all of the crap code currently being written by AI with no human mitigation or code review. The AI zealots, say yes, but the next version will get better, and I am sure it will, but that version will be hoovering up all of the crap code that the previous version generated, and 'thinking' that it is good code.

I agree with everything in this article

[Aussiebear]

I agree Bob. The use of AI is extremely limited because all it is doing is searching for responses on the Net and then trying to apply them to something. All too often the indicated methods are incorrect.

[Logit]

Did some research out of curiosity and learned that SELECT CASE statement is meant to replace IF/ELSE IF. Likewise, you can have as many SELECT CASE statements as you desire but of course that wouldn't be good
coding practice. ALSO ... each module is limited to 64k so when you exceed that level the SELECT CASE statements will crash.

[Jan Karel Pieterse]

As a side note: Select Case is often slightly slower than an If...ElseIf...EndIf construct.

[Bob Phillips]

Yeah, but a heap more elegant Jan Karel.

[Paul_Hossler]

So ignoring the logic of this for a moment, is there a limit to how much you can stick inside a Case (definitions)?

1

http://www.vbaexpress.com/forum/show...of-Case-select

2.

I wouldn't write it that way


3. IIRC (and going back many^2 years) Case Select was a 'Switch' and an integer (maybe byte) was computed to jump directly to the appropriate Case. FORTRAN called something like this a Computed GoTO

Case Select is oft times advertised as an alternate to a bunch of If / Then / Elseif / ..... but I'd think that each If / Elseif has to be evaluated there's a lot more CPU cycles involved with all that testing

So instead of testing 100 Elseif's, Case Select just says 'Go to the 100th Case'



Not sure about any of this

[Bob Phillips]

1
3. IIRC (and going back many^2 years) Case Select was a 'Switch' and an integer (maybe byte) was computed to jump directly to the appropriate Case. FORTRAN called something like this a Computed GoTO

Not the same, but this comment reminded me of a previous life, when we bought an electoral register system written in Cobol from another local authority, and had to adapt it to our particular situation.

The problem was that this system had been written by a bunch of guys fresh off their training course, and they used one particularly horrible construct called alterable Gotos, i.e there would be a Goto statement in the code, and somewhere else in the code that Goto would be modified to go somewhere else.

I cannot recall the code exactly so this is more pseudo-code, but hopefully showing what I mean.

Label1:
    Goto Label2
Label2:
    do some stuff
     ...
Label3:
    do some more stuff
    ...
Label4:
    Alter Label1 To Proceed To Label98
    Goto Label1

Can you imagine trying to follow code like this, the system was absolutely smothered in this sh#t. My poor old programmer did a great job modifying it and getting rid of them all, but I didn't envy him one bit.

[Paul_Hossler]

I vaguely, kindof, sortof recall seeing something like this

Must have drive the next maintenace programmer to drink

DegFreedom = 3

...
...

GoTo 3*DegFreedom

...
...
'-----------------------------------------------------------------------------------
00009:

...
...

If Z = 1 Then
    Goto DegFreedom - 1
    DegFreedom =  4
Else
    Goto DegFreedom + 2
    DegFreedom = 6
End If

'-----------------------------------------------------------------------------------
0002:
...
...

End

00005:

...
...
End