VBA basics
22. Conditional branch (Select Case)

Excel macro basics and applications, introduction to Excel VBA
last updated:08-25-2019

22. Conditional branch (Select Case)

Some of this topic was processed by machine translation. Original

VBA macro excel

There is another way to write multi-limb conditional branches other than the previous ElseIf.

That is Select Case.
Rather than ElseIf, this Select Case is more suitable for multiple-limb conditional branching.
Select Case is a statement that specializes in multiple conditional branching.

Select Case statement syntax

Select Case testexpression
    [Case expressionlist-n
    [Case Else
End Select

testexpression Must be specified.
Specify any mathematical expression or string expression.

It must be specified if there is a Case clause. Use the following format:
Separate multiple items with a comma (,).
expression To expressionIs
comparisonoperator expression
The argument expression is an expression or string, and the argument comparisonoperator is a comparison operator.
The keyword To is a keyword that specifies a range of values.
When specifying a range of values using the keyword To, specify the smaller value first (left side).
The keyword Is is a keyword that specifies a range of values, and is used with comparison operators other than the Is and Like operators.
The keyword Is is set automatically even if you do not specify it.

statements-n It can be omitted.
When the argument testexpression matches one of the arguments expressionlist-n, the statement with the matching argument statements-n is executed.
elsestatements It can be omitted.
If the argument testexpression does not match any of the Case clauses, the statement in the argument elsestatements is executed.

*This is a help reference.

It's a little difficult to understand, especially the expressionlist-n specification.
Please deepen your understanding with the following example sentences.

Select Case example

First of all basic form
Select Case variable
  Case 1
    'Processing of variable = 1
  Case 2
    'Processing of variable = 2
  Case Else
    'Processing of variable = 3
End Select

How to write Or condition

Select Case variable
  Case 1, 2
    'Process with variable = 1 or variable = 2
  Case 3, 4, 5
    'Processing of variable = 3 Or variable = 4 Or variable = 5
  Case Else
    'Processing other than the above variables
End Select

Range specification

Select Case variable
  Case 1 To 2
    'Processing of variable = 1 to 2
  Case 3 To 5
    'Processing of variable = 3 to 5
  Case Else
    'Processing other than the above variables
End Select

How to use the inequality sign

Select Case variable
  Case Is <= 2 'Is is not written, it is automatically inserted.
    'Processing of variable <= 2
  Case Is <= 5
    'Processing of variable <= 5
  Case Else
    'Processing other than the above variables
End Select

Select Case is a versatile statement that is very versatile.
If-ElseIf-End If can be rewritten with Select Case.

When writing VBA using ElseIf

If Condition1 Then
  'Condition1 is true processing
ElseIf Condition2 Then
  'Condition2 is true processing
ElseIf Condition3 Then
  'Condition3 is true processing
  'All conditional expressions are false
End If

When VBA is written in Select Case
Select Case True
  Case Condition1
    'Condition1 is true processing
  Case Condition2
    'Condition2 is true processing
  Case Condition3
    'Condition3 is true processing
  Case Else
    'All conditional expressions are false
End Select

The above two (ElseIf and Select Case) are the same.
I prefer to use Select Case instead of ElseIf.
I feel it is easy to see.
It ’s definitely easy to see.

Articles with the same theme "VBA basics"

19. General practice question 1
20. Conditional branch (If)
21. Conditional branch (ElseIf)
22. Conditional branch (Select Case)
23. Message box (MsgBox function)
24. Input box (InputBox function)
25. About named arguments
26. General practice question 2
27. Select book / sheet (Select, Activate)
28. Select cell / row / column (Select, Activate)
29. Delete / Insert cell / row / column (Delete, Insert)

  • >
  • >
  • 22. Conditional branch (Select Case)

  • If you find this site useful, please share and bookmark.

    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.

    If you find this site useful, please share and bookmark.
    To the bottom of the text