The Choose Function

In the previous section, I showed you how the IIf function is an efficient replacement for If...Then...Else when all you need to do is assign a value to a variable based on the results of the test. Suppose now you have a similar situation with the Select Case structure. In other words, you want to test a number of possible values and assign the result to a variable.

For example, you saw in Chapter 4 that VBA's Weekday function returns the current day of the week as a number. Here's a procedure fragment that takes the day number and uses a Select Case structure to assign the name of the deity associated with that day to the dayDeity variable:

Dim dayDeity As String Select Case Weekday(Now)

Case 1

dayDeity =

"Sun"

Case 2

dayDeity =

"Moon"

Case 3

dayDeity =

"Tiw"

Case 4

dayDeity =

"Woden"

Case 5

dayDeity =

"Thor"

Case 6

dayDeity =

"Freya"

Case 7

dayDeity =

"Saturn

End Select

Again, this seems like way too much effort for a simple variable assignment. And, in fact, it is too much work thanks to VBA's Choose function. Choose encapsulates the essence of the preceding Select Case structure—the test value and the various possible results—into a single statement. Here's the syntax: Choose(index, value1, value2,...)

index A numeric expression that determines which of the values in the list is returned. If index is 1, value1 is returned. If index is 2, value2 is returned (and so on). Note that if index is less than 1 or greater than the number of values in the list, the function returns Null. value1, value2... A list of values from which Choose selects the return value.

The values can be any valid VBA expression.

Listing 6.9 shows a function called DayDeity that returns the name of a day's deity by using Choose to replace the Select Case structure shown earlier.

0 0

Post a comment