VBA for Excel - Avoid type code again if deeper select case dont match criteria

Question

I was wondering if there is a workaround to not retype the code if a deeper select case dont' match the criteria.

I have to check whether the Month is one of those stated below, if it is I have to check if the day of that month match with the criteria. If the day dont match any case, the code that have to run is the one set in the above Select statement in the ELSE case.

The only way I know to workaround this is by using and IF statement instead of the nested select case and using a GO TO LABEL to bring me back to the CASE ELSE statement but it seems very unclassy.

Is there a way not to retype the same code in the else case if the deeper select case dont match the criteria ?

Select Case Month(Range("b" & Start))
                        Case 1

                        Case 4

                        Case 5

                        Case 6

                        Case 8
                         GiornoMese = listaattiva(j).Text & "/" & Month(Range("b" & Start)) & "/" & Year(Range("b" & Start))
                         Select Case Day(GiornoMese)
                                Case 15
                                    **HERE IF THE DAY IS NOT THE 15th
                                    CODE SHOULD JUMP OUT AND RUN CODE TYPED
                                    IN THE CASE ELSE STATEMENT**
                        Case 12

                        Case Else
                            'Debug.Print listaattiva(j).Text & "/" &
                            'Month (Range("b" & Start)) & "/" & Year(Range("b" & Start))
                            GiornoMese = listaattiva(j).Text & "/" & Month(Range("b" & Start)) & "/" & Year(Range("b" & Start))
                            If Weekday(GiornoMese, vbMonday) = 7 Then
                                'Domenica - Tutte le ore del giorno sono F3
                                For Each quarto In listaattiva(j).Attributes
                                    'Debug.Print listaattiva(j).Attributes.Length
                                    'Debug.Print quarto.Text
                                    F3 = F3 + quarto.Text
                                    'Debug.Print F3
                                Next

@MarcinSzaleniec This would make me retype the code (or ctrl-v paste) I've already stated in the CASE ELSE STATEMENT.

I got to check if the data listed in a XML file are on Public Holidays. To do so I check first the month and then the day.

If it's not an actual Public Holidays Month I just go with standard code in the ELSE STATEMENT, if it's a Month AND an Holiday day I write different code BUT if it's a Month but not a Holiday day I would run the already stated code for the ELSE STATEMENT.

Mine is more a "beauty" matter, I'd like to keep the code in the same SUB having a vertical work flow not having to make ppl jump on other subs.

score 1 · Answer 1 · answered Nov 03 '17 at 09:52

Make separate sub from the code in Case Else section. You may pass necessary variables as arguments. Try something like:

Sub DoIfElse (_Year as Integer, _Month as Integer)

(...)
end Sub

It is hard do decipher which variables are really needed because you published only fragment of the code, but I think you know what do you need. Then, you can use DoIfElse in various places in your main subroutine, as you wish.

VBA for Excel - Avoid type code again if deeper select case dont match criteria

1 Answers1