Using Select Case on a Worksheet

Have you ever nested an If...Then...Else on a worksheet to return a value? The Select...Case statement available in VBA case makes this a lot easier—but you can't use Select Case statements in a worksheet formula. So, create a UDF (see Figure 4.20). The following function shows how you can use Select statements to produce the results of a nested If...Then statement:

Function state_period(mth As Integer, yr As Integer) Select Case mth Case 1

state_period = "July 1, " & yr - 1 & " through July 31, " & yr - 1 Case 2

state_period = "August 1, " & yr - 1 & " through August 31, " & yr - 1 Case 3

state_period = "September 1, " & yr - 1 & " _ through September 30, " & yr - 1

Case 4

state_period = "October 1, " & yr - 1 & " through October 31, " & yr - 1 Case 5

state_period = "November 1, " & yr - 1 & " _ through November 30, " & yr - 1

Case 6

state_period = "December 1, " & yr - 1 & " _ through December 31, " & yr - 1

Case 7

state_period = "January 1, " & yr & " through January 31, " & yr Case 8

state_period = "February 1, " & yr & " through February 28, " & yr Case 9

state_period = "March 1, " & yr & " through March 31, " & yr Case 10

state_period = "April 1, " & yr & " through April 30, " & yr Case 11

state_period = "May 1, " & yr & " through May 31, " & yr Case 12

state_period = "June 1, " & yr & " through June 30, " & yr Case 13

state_period = "Pre-Final" Case 14

state_period = "Closeout" End Select End Function

Figure 4.20

An example of using a Select...Case structure in a UDF rather than nested If...Then statements.

A5 * fil ■= slat a_pe rid d(AZ,B2)

0 0

Post a comment