VBA Statement Select Case End Select

First, Select case syntax and parameter introduction

Select Case testexpression

[Case Expressionlist-n

[Statements-n]] ...

[Case Else

[Elsestatements]]

End Select

The syntax for the Select case statement has the following sections:

testexpression necessary parameters. Any numeric expression or string expression.

Expressionlist-n If a case appears, it is the necessary parameter. A list of one or more constituent boundaries in the form of expression,expression to expression,is comparisonoperator expression. The To keyword can be used to specify a range of values. If you use the To keyword, the smaller values appear before to. When you use the IS keyword, you can specify a range of values with the comparison operator (except is and like). If not provided, the IS keyword is automatically inserted.

Statements-n Optional Parameters. One or more statements that are executed when testexpression matches any part of the expressionlist-n.

Elsestatements Optional Parameters. One or more statements that are executed when testexpression does not match any part of the case clause.

If testexpression matches a case expressionlist expression, after the case clause, until the statements of the next one is executed, and if it is the last clause, it is executed to the end Select. Control is then transferred to the statement after the end Select. If testexpression matches a expressionlist expression in more than one case clause, only the statement following the first match is executed.

The case ELSE clause is used to indicate elsestatements, and these statements are executed when testexpression and expressionlist in all case clauses do not match. Although not necessary, in the Select case block, it is best to add the case Else statement to handle the unforeseeable testexpression value. If no case expressionlist matches testexpression, and there is no case Else statement, the program continues execution from the statement after the end Select.

Second, the case of Select cases

① cell range b1:b5, cell value =1→ red, =2→ yellow, =3→ aqua, = outside → Green

Dim Myrng as Range

Dim C as Range

Dim MyColor as Integer

Set myrng = Range ("B1:b5")

For each C in myrng

Select Case C.value

Case 1

MyColor = 3 ' Red

Case 2

MyColor = 6 ' Yellow

Case 3

MyColor = 8 ' Aqua

Case Else

MyColor = 10 ' 緑

End Select

C.interior.colorindex = MyColor

Next C

② cell range b1:b5, cell value <10→ red, <20→ yellow, <30→ aqua, outside → Green

Dim Myrng as Range

Dim C as Range

Dim MyColor as Integer

Set myrng = Range ("B1:b5")

For each C in myrng

Select Case C.value

Case is < 10

MyColor = 3 ' Red

Case is < 20

MyColor = 6 ' Yellow

Case is < 30

MyColor = 8 ' Aqua

Case Else

MyColor = 10 ' 緑

End Select

C.interior.colorindex = MyColor

Next C