[ACCEPTED]-How to fall through a Select Case in Excel VBA?-office-2007

Accepted answer

Score: 16

Select Case cmd
    case "ONE", "TWO":   
                  if cmd = "ONE" THEN
                      MsgBox "one"
                  end if
                  MsgBox "two"

    case "THREE": MsgBox "three"

End select

Score: 4

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

Score: 3

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

Score: 2

I also had this problem recently.

I found 4 the most readable and scale-able solution 3 was to create a basic state-machine.

Simply 2 wrap the Select in a While and end each case with which 1 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

Score: 1

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

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

Score: 1

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 1 tested it.

Score: 0

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

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

Score: 0

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

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

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

Source: stackoverflow.com

[ACCEPTED]-How to fall through a Select Case in Excel VBA?-office-2007

Hiding a sheet in Excel 2007 (with a password) OR hide VBA code in Excel

Get the email address of the current user in Outlook 2007

Write a formula in an Excel Cell using VBA

VBA editor auto-deletes spaces at the ends of lines

Forcing Garbage Collection

Convert image (jpg) to base64 in Excel VBA?

VBA Cell address of Max()

How to push value into non pre specific array size?

Read XML Attribute VBA

VBA Excel - Word Wrap