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 If (or ElseIf) and Then, and of course the appropriate statements to be executed between Then and End If (or ElseIf).

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.

Include Else statement

Normally, you will both specify both what needs be done if the condition expression evaluates to True, after Then, and what needs be done if it evaluates to False, between Else and End If.

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

There are several ways in which Select Case statements can be used in VBA, see Using Select Case statements.

Expression

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 Case keyword "" (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

Compact

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

Expression True

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.