'How to fall through a Select Case in Excel VBA?

Given

Select Case cmd

    case "ONE":   MsgBox "one"

    case "TWO":   MsgBox "two"

    case "THREE": MsgBox "three"

End select

My requirement is if cmd = "ONE" I need "one" and then "two" displayed however currently I am getting "one" displayed and then the program is breaking out of the select case...

Solution 1:^[1]

Some if could do the job:

If cmd = "ONE" Then 
    MsgBox "one"
    cmd = "TWO"
End If
If cmd = "TWO" Then 
    MsgBox "two"
    cmd = "THREE"
End If
If Cmd = "THREE" Then 
    MsgBox "three"
End If

Solution 2:^[2]

You'll just have to do it the long way.

Select Case cmd

    case "ONE":   MsgBox "one"
                  MsgBox "two"
                  MsgBox "three"

    case "TWO":   MsgBox "two"
                  MsgBox "three"

    case "THREE": MsgBox "three"

End select

Solution 3:^[3]

I've found the most readable and scale-able solution to be a small state-machine.

Simply wrap the Select in a While and end each case with which case is next.

While cmd <> ""
Select Case cmd
    Case "ONE"
        MsgBox  "one"
        cmd = "TWO"
    Case "TWO"
        MsgBox  "two"
        cmd = ""
    Case "THREE"
        MsgBox  "three"
        cmd = ""
End Select
Wend

Solution 4:^[4]

That is by design. http://msdn.microsoft.com/en-us/library/ee177199%28PROT.10%29.aspx

You could try using 'goto' or procedure calls to get around it.

Solution 5:^[5]

GoTo would work well, I think.

Select Case cmd

    Case "ONE"
        MsgBox "one"
        GoTo AfterONE:

    Case "TWO"
AfterONE:
        MsgBox "two"

    Case "THREE"
        MsgBox "three"

End Select

It works; I tested it.

Solution 6:^[6]

Why use Select Case for this? All you're doing is printing the lower-case version of whatever value 'cmd' is.

If cmd = "ONE" Then
  MsgBox LCase$(cmd)
  MsgBox "two"
Else
  Msgbox LCase$(cmd)
End If

Solution 7:^[7]

'Fall through' in a non-empty Case is a big source of errors in C/C++, so not implemented in most other languages (including VBA). So, as stated elsewhere, do it the long way and consider procedure calls to avoid duplication.

However, if like me you ended up on this page because you couldn't remember how to do empty drop-through, then a slight variation on the accepted answer is that you can (as with all VBA's commands) use an underscore to denote continuation onto the next line:

Select Case cmd
Case "ONE", _
     "TWO":   
    if cmd = "ONE" Then MsgBox "one"
    MsgBox "two"
Case "THREE":
    MsgBox "three"
End select

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	Lance Roberts
Solution 3
Solution 4	Michael Todd
Solution 5	Stephen Fluharty
Solution 6	JimmyPena
Solution 7	sjh2014

'How to fall through a Select Case in Excel VBA?

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]