Using the If / Select Case Builder
This code builder helps create condition statements of which
If Then Else and
Select Case are the main types.
These are put together in one builder because when thinking about which to use users frequently switch to the other type.
Switching is done by selecting the appropriate Tab.
When the builder code box displays the appropriate result, press OK to have it inserted at the cursor position in your procedure.
After the code is inserted, you will add the condition statements between
and of course the appropriate statements to be executed between
End If (or
|Alt-CII | Menu: Code VBA » If » If Then Builder|
If Then Else statements
There are several ways in which If condition statements can be used in VBA, see Using If...Then...Else statements. The possible structures can be seen in the animation.Note that for the simplest variant, 'Multiple lines' unchecked, you will normally not use the builder because typing is faster.
Include Else statement
Normally, you will both specify both what needs be done if the condition expression evaluates to
Then, and what needs be done if it evaluates to
Sub AlertUser(value as Long) If value = 0 Then AlertLabel.ForeColor = vbRed Else AlertLabel.Forecolor = vbBlack End If End Sub
Include ElseIf statement
If there are more than two possible conditions that may occur and need different handling, the
ElseIf comes at hand.
The builder lets you specify the number of ElseIf statement
Sub AlertUser(value as Long) If value = 0 Then AlertLabel.ForeColor = vbRed ElseIf value = 1 Then AlertLabel.Forecolor = vbGreen Else AlertLabel.Forecolor = vbBlack End If End Sub
Select Case Builder
|Alt-CIS | Menu: Code VBA » If » Select Case Builder|
There are several ways in which Select Case statements can be used in VBA, see Using Select Case statements.
The first line
Select Case expects an Expression which can be selected from the combo box.
Note that if the selected variable is of type String, after the
"" (space + 2 doublequotes is added), so that you can simply insert the relevant string value, while in case of a numeric type, it is left empty, you just enter any value.
Depending on the type of the selected variable different additional options are available.
Special support for Enumerated type variables
If you follow the good practice of using enumerated types you will benefit here using the Select Case Builder in that it neatly insertes the different possible cases for you to add code to specify different required behaviours. The example AlertUser above, would have been better (less verbose) organized as
Select Case value Case 0 AlertLabel.ForeColor = vbRed Case 1 AlertLabel.Forecolor = vbGreen Case Else AlertLabel.Forecolor = vbBlack End Select
In the example below the tnu variable was of custom enumerated type Tenure with three possible values: past, present and future.
Select Case tnu Case entPastTenure Case entPresentTenure Case entFutureTenure End Select
This also works with Built-in enumerated types, e.g. variable dow As vbWeekday
Select Case dow Case vbSaturday Case vbSunday Case vbMonday '... End Select
Often, different cases are only followed by a single statement. Checking Compact would give you more compact code. In the example of AlertUser:
Select Case value Case 0 : AlertLabel.ForeColor = vbRed Case 1 : AlertLabel.Forecolor = vbGreen Case Else : AlertLabel.Forecolor = vbBlack End Select
Finally, a different but also useful way to fill the
Select Case expression position is using the constant True instead of a selected variable.
Here, instead of specifying values in the
Case expression position, you enter a Boolean condition expression, for example
Case lngQty > 100.
The VBA interpreter then moves through the listed cases until it evaluates a Case expression to True in which case it executes the code part of the Case and leaves the Select Case block.