Using Select Case for months of the year example

Question

I'm trying to run some code for the month that we're in, I was wondering if I'm on the right track with the below code:

Sub test6()

Select Case DatePart("m", Date).Value

Case 1
     ' ... Do something
Case 2
     ' ... Do something
Case 3
     ' ... Do something
Case 4
     ' ... Do something
Case 5
     ' ... Do something
Case 6
     ' ... Do something
Case 7
     ' ... Do something
Case 8
     ' ... Do something
Case 9
     ' ... Do something
Case 10
     ' ... Do something
Case 11
     ' ... Do something
Case 12
     ' ... Do something

End Select

End Sub

So as an example, if Datepart returns a 9 (September) than Case 9 line should run.

I read somewhere that you should steer clear of `case select` not sure why tho.. Keen to hear if anyone has any info as to why!? I tend to use `If` `Elseif` etc etc... — alowflyingpig, Sep 24 '19 at 04:58
Whether this is the "correct" approach mainly depends on what "Do something" involves in each case. If each set of actions is similar then you may be able to parameterise them and pass in the month directly. — Tim Williams, Sep 24 '19 at 05:01
@alowflyingpig I have never read that and I am interested in knowing your source. I almost invariably use `Select Case` instead of multiple `ElseIf` because I prefer the aesthetics, especially with a long list like the OP's example. I also use it like this... `Select Case MsgBox ("foo?",vbYesNo)` `Case vbYes` `Case vbNo`. And I enjoy the flexibility gained from interchanging the test and the result, for example: `Select Case foo = 1` `Case True` can also be written `Select Case True` `Case foo = 1` I find it really helps me stay with the flow instead of getting hung up on the condition. — ProfoundlyOblivious, Sep 24 '19 at 06:22
@ProfoundlyOblivious I read it about a yr ago when I was getting into VBA. Can't remember the url that I read it at.. Apart from aesthetics, why would you use `case select` over `elseif`? — alowflyingpig, Sep 24 '19 at 06:48
@alowflyingpig: A `Select Case` statement is easier to read and maintain in many circumstances, especially if one is selecting from a list of known objects. The OP's example is a very good one where a `Select Case` works well. If each conditional was a more complex construct (e.g. `If a=1 and b=c then : DoSomething1 : Elseif a=2 or b=d Then : DoSomething2` etc.), the `Else if` makes more sense. — AJD, Sep 24 '19 at 06:59
@AJD thats a pretty solid reason. i may need to brush up on my case select skills... — alowflyingpig, Sep 24 '19 at 07:01
@alowflyingpig Perhaps you are mixing the two usages of the word `Select`, the one here `Select case` which never has been deemed bad, and the one in conjunction with recording macros, where you often see f.ex. `Range().Select`. You can read about how to avoid the latter, here: https://stackoverflow.com/a/10717999/2292722 — Tom Brunberg, Sep 24 '19 at 07:48
@TomBrunberg thanks mate but im not confusing them. Apprecaite the link but i skilled enough to avoid the usage of `.select`. Funnily enough back when I wasn't, that link is exactly the page I learnt from! — alowflyingpig, Sep 25 '19 at 04:09

Michał Turczyn · Accepted Answer · 2019-09-24T07:12:04.987

2

If you want to apply totally different logiv for each month, this is the correct way.

If there is some code that will be duplicated, you could use If...Then:

If month = 1 Or month = 9 Then
  ' apply logic for january and september
ElseIf ...
  ...
End If

Using Else If can be also used for applying different logic to each month instead of Select Case.

EDIT: You can also use Case 1, 9 for grouping.

edited Sep 24 '19 at 07:12

answered Sep 24 '19 at 07:02

Michał Turczyn

32,028
14
47
69

"If there is some code that will be duplicated …" you can also use `Case 1, 9` which does the same thing. – AJD Sep 24 '19 at 07:07
@lowflyingpig in response to your question, there also the case (pardon the pun) used in comment above and it is possible to use `Case 1 to 9` if you wanted any month from Jan through Sept. Or even combine them, `Case 1 to 5, 8, Is > 10`. – ProfoundlyOblivious Sep 24 '19 at 07:56

Using Select Case for months of the year example

1 Answers1