Download Microsoft Excel VBA User Manual

Excel Visual Basic for Applications
Example
The message box displays the second item in the list:
Sub Main()
x = 2
y = Choose(x, "Tom", "Dick", "Harry")
MsgBox y
End Sub
Switch Function
Evaluates a list of pairs of expressions and values and returns the value associated with
the first expression in the list that is True.
Switch(expr1, value1, expr2, value2, etc.)
The expressions are evaluated from left to right but can be entered in any order.
Example
The message box displays "STG", the value associated with the x="UK" expression:
Sub Main()
x = "UK"
y = Switch(x = "UK", "STG", x = "USA", "USD", x = "DEN", "DKK")
MsgBox y
End Sub
Decision making code is a matter of personal taste and judgement. Generally speaking,
If-Then-Else is the most flexible, Case Statements are best where you are testing one
expression over many different conditions, the CHOOSE function is best for processing
sets of numbers and SWITCH is best for substitution.
In the following example, all four methods are demonstrated. An organisation has a
financial year that starts in April and we need to take the current calendar month value
and convert it into the current accounting month value; April is 1 etc. The x variable
stores the current month as returned by the Month and Date functions and we have to
calculate the value of the MonthNo variable:
x = Month(Date)
'If the date is 4 or more; deduct 3, otherwise add 9.
If x >= 4 Then
MonthNo = x - 3
Else
MonthNo = x + 9
End If
'When the date is from 4 to 12, deduct 3. When it is from 1 to 3, add 9.
Select Case x
Case 4 To 12
MonthNo = x - 3
Case 1 To 3
MonthNo = x + 9
End Select
'Pick the value from the list.
MonthNo = Choose(x, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9)
'Match the pair in the list.
MonthNo = Switch(x = 1, 10, x = 2, 11, x = 3, 12, x = 4, 1, _
x = 5, 2, x = 6, 3, x = 7, 4, x = 8, 5, x = 9, 6, _
x = 10, 7, x = 11, 8, x = 12, 9)
Page 8