The most important condition statements used in Excel VBA are the statements If … Then и Select Case. Both of these expressions test one or more conditions and, depending on the result, perform different actions. We’ll talk about these two conditional operators in more detail next.
“If…Then” Statement in Visual Basic
Operator If … Then checks the condition and, if it is true (TRUE), then the specified set of actions is performed. It can also define a set of actions to be performed if the condition is FALSE.
Operator syntax If … Then like this:
If Условие1 Then
Действия в случае, если выполняется Условие1
ElseIf Условие2 Then
Действия в случае, если выполняется Условие2
Else
Действия в случае, если не выполнено ни одно из Условий
End If
In this expression, the elements ElseIf и else conditions operator can be omitted if they are not needed.
Below is an example in which, using the operator If … Then The fill color of the active cell changes depending on the value in it:
If ActiveCell.Value < 5 Then ActiveCell.Interior.Color = 65280 'Ячейка окрашивается в зелёный цвет ElseIf ActiveCell.Value < 10 Then ActiveCell.Interior.Color = 49407 'Ячейка окрашивается в оранжевый цвет Else ActiveCell.Interior.Color = 255 'Ячейка окрашивается в красный цвет End If
Note that as soon as the condition becomes true, the execution of the conditional statement is aborted. Therefore, if the value of the variable ActiveCell less than 5, then the first condition becomes true and the cell is colored green. After that, the execution of the statement If … Then is interrupted and the other conditions are not checked.
Learn more about using the conditional operator in VBA If … Then can be found on the Microsoft Developer Network.
"Select Case" Statement in Visual Basic
Operator Select Case similar to operator If … Then in that it also checks the truth of the condition and, depending on the result, chooses one of the options.
Operator syntax Select Case like this:
Select Case Выражение
Case Значение1
Действия в случае, если результат Выражения соответствует Значению1
Case Значение2
Действия в случае, если результат Выражения соответствует Значению2
...
Case Else
Действия в случае, если результат Выражения не соответствует ни одному из перечисленных вариантов Значения
End Select
Element Case Else is not required, but is recommended for handling unexpected values.
In the following example, using the construct Select Case changes the fill color of the current cell depending on the value in it:
Select Case ActiveCell.Value Case Is <= 5 ActiveCell.Interior.Color = 65280 'Ячейка окрашивается в зелёный цвет Case 6, 7, 8, 9 ActiveCell.Interior.Color = 49407 'Ячейка окрашивается в оранжевый цвет Case 10 ActiveCell.Interior.Color = 65535 'Ячейка окрашивается в жёлтый цвет Case 11 To 20 ActiveCell.Interior.Color = 10498160 'Ячейка окрашивается в лиловый цвет Case Else ActiveCell.Interior.Color = 255 'Ячейка окрашивается в красный цвет End Select
The example above shows how you can set a value for an element in various ways. Case in construction Select Case. These are the ways:
| Case Is <= 5 | Thus, using the keyword Case Is you can check if the value satisfies Expressions condition of the form <= 5. |
| Case 6, 7, 8, 9 | This way you can check if the value matches Expressions with one of the listed values. The listed values are separated by commas. |
| Case 10 | This checks if the value matches Expressions with a given value. |
| Case 11 That's it 20 | Thus, you can write an expression to check if the value satisfies Expressions condition of the form from 11 to 20 (equivalent to the inequality "11<=value<=20"). |
| Case Else | Like this, using the keyword else, actions are indicated for the case if the value Expressions does not match any of the options listed Case. |
As soon as one of the conditions is found, the corresponding actions are performed and the structure exits. Select Case. That is, in any case, only one of the listed branches will be executed. Case.
More information about the operation of the VBA statement Select Case can be found on the Microsoft Developer Network.