Select case

Consider the If structure shown in Listing 6.5. Although the logic is simple, the coding is a little difficult to follow.

Listing 6.5 TicketPrice VBA Program

If (ticketPrice =20) Then

MsgBox "Standing at rear" Else

If (ticketPrice =30) Then

MsgBox "Front wing seats" Else

If (ticketPrice =40) Then

MsgBox "Lower front wing seats" Else

If (ticketPrice =50) Then

MsgBox " Balcony" Else

If (ticketPrice =60) Then

MsgBox "Upper Balcony" Else

MsgBox "Royal Box" End If End If End If End If End If

VBA supports the Select Case statement that can make it easier to understand, multiple-choice conditions than If...ElseIf...Else statements. The syntax of the Select Case statement is as follows:

Select Case Expression Case value one or more VBA statements Case value one or more VBA statements Case value one or more VBA statements Case Else one or more VBA statements End Select

Listing 6.6 is a Select Case version of Listing 6.5. Select Case organises the multiple-choice selections into a more manageable format.

Listing 6.6 Using Select Case to simplify complex If...Else statements

Sub ticketSelect() Dim ticketPrice As Integer ticketPrice = InputBox("Input ticket price") Select Case ticketPrice Case 20: MsgBox "Standing at rear" Case 30: MsgBox "Front wing seats" Case 40: MsgBox "Front lower wing seats" Case 50: MsgBox "Balcony" Case 60: MsgBox "Upper Balcony" Case Else: MsgBox "Royal Box" End Select End Sub

In this example, if ticketPrice holds 20, the message "Standing at rear" is displayed. If it holds 30, "Front wing seats" is displayed. The logic continues through the Case 60: statement. If ticketPrice holds a value that does not fall in the range 20 to 60, the final Case Else displays the message "Royal Box".

E Microsoft Excel - \ ;

e

Fiie Edit Vj:eJ,"' Ti bç'L ^i-'iaf ^iiiuu^1

Uefc

-

S x

& y un ®

" 1 Times New Roman

B J

u

»

A

B

1 »

1 D

P3

CO

"G 1

21

T7 .s„U -

22

■licrosoft EKcel

■ ' je

_

23

Sales To Date :

Input ticket price

1

|

24

Month No:

1

25

Cancel

26

27

28

Bonus Rate:

[40

Î

23

Month No:

1

30

Weekly Sales

31

Rep Name

Salts To Dale

1

2

3

4

MontJdy Total 1

M

32

Andy

135

54

5

6

12|

77

33

Bindi

459

32

Al

9R

is's

34

Katie

357

16

1 Microsoft Excel

2

35

Misba

■iSfiii

Si

f Front lower wing seats !

iää

t

36

AH

489

44

157

t

37

Amy Pete

333

20

-

1 :1

193

£ "'

38

3X:

21

■JF

LJ

123

x

39'

Rhiannon

453

33

"__

HHHH

_

_

4D

Gaieth

330:

27

ra

Tsf

-.122

—7

M «

► H \ 5heet2 1 Sheet 1 '). weekly sales / week sa | <

1

I \

.Rccd;/

5

Figure 6.5 Screenshot of Select Case example

Figure 6.5 Screenshot of Select Case example

The body of each Case can consist of more than one statement, just as the body of an If or If.. .Else can consist of more than one. VBA executes all the statements for any given Case match until the next Case is reached. Once VBA executes a matching Case value, it skips the remaining Case statements and continues with the code that follows End Select.

Notice the colons after each Case value statement. These are optional, but do help to separate the case being tested from the code that it executes.

0 0

Post a comment