This section discusses the control flow functions such as IIf, Choose and Switch functions.
You can choose to use these VBA control flow functions instead of always using the common control flow statements or control flow blocks like If-Else or Select Case.
See the following lessons for If-Else and Select Case statements:
Why to use control flow functions instead of the control flow blocks?
The IIf, Choose and Switch control flow functions reduce the amount of code you have to write. In many cases, just one line of code does your work, while the If or Select Case control flow blocks takes more lines.
The IIF function
You can use the IIf function instead of the If-Else statements.
Remember that the IIF function can never be the substitute for all the conditional If-Else problems. In fact, the If-blocks are very powerful in the sense that they can solve many complex problems. All the same the IIf function is useful especially when the problem is small.
Syntax
Value = IIf(Condition, True Part, False Part)
Example of IIF
str = IIf(n > 0, "Greater than zero", "Less than Zero")
Equivalent code
If n > 0 Then
str = "Greater than Zero"
Else
str = "Less than zero"
End If
So now this is clear that the IIf function does the same job of an If-Else block having five lines with just one line of code.
The Choose function
The choose function selects and returns a value from a list of arguments.
Example
m = Choose(2, "White", "Green", "Yello")
The index determines which argument value to be returned. Here the index is 2, so the string value “Green” is returned and assigned to m.
The Switch function
You can get the same effect of a Select Case block using the Switch function with just one line of code.
Example
size = Switch(num <= 9, 1, num <= 99, 2, num <= 999, 3)
Equivalent code
Select Case num
Case Is <= 9
size = 1
Case 10 To 99
size = 2
Case 100 To 999
size = 3
End Select
So the same result is achieved using the Switch function. But the Switch function evaluates all the expressions, even though only one is True, and that slows down the process.
Select Case block with Else
Select Case Color
Case vbRed
str = "Red"
Case vbBlue
str = "Blue"
Case vbBlack
str = "Black"
Case Else
str = "Other"
End Select
Equivalent code
str = Switch(Color = vbRed, "Red", Color = vbBlue, "Blue", Color = vbBlack, "Black", True, "Other")
Here the True argument indicates the else part.
Drawbacks
The control flow functions IIf, Switch and Choose are always slower than the If or Select Case structure and this is a big disadvantage of them. So always be aware that though these functions reduce the amount of code, they may slow down your application.