Adding Control Statements

With the Select Case statement, you can specify comparison statements, a range of values, or check for multiple values.

TYPE THIS:

Select Case NumSales

Case 1 To 5

Commission = Total *

.05

Case 6 To 15

Commission = Total *

.1

End Select

The Select Case statement checks the value of NumSales to see whether it falls into one of the two specified ranges to calculate the commission rate.

TYPE THIS:

Select Case

NumStudents

Case Is <10

MsgBox("Not

enough students enrolled")

End Select

The Select Case statement displays the message box if the value of NumStudents is less than 10.

TYPE THIS:

RESULT:

If the value of State equals TX or CA the total is calculated using 8.5% for the sales tax.

—Q Repeat steps 5 and 6 for each expected value of the expression.

—0 Type the code statements to execute for all other values of the expression.

L"E Type End Select.

— Switch to Excel and run the associated macro.

m

File Edit

View Insert Format Tools

Data Window Help

Type a question for he

- . B

1

t □ S

m #

3l X

• I

4 E •

11 ©

« 110% . g .

f Aric

M ih

m $ ?

, too iW IF

- ■!»

. A .

C2

»

fx 5.375

A

B

c

D

E

F

G

H

J

K

-

1

Total

State

Total with Tax

2

$5.00 TX

$5.38

3

$6.50 CA

$7.07

4

$17.65 FL

$19.15

fa

$385.00 UT

$419.65

R

$52.00 TX

$55.90

7

$683.00

TX

$734.23

8

$14.50

CA

$15.78

9

$3.00

FL

$3.26

10

$11.45 TU

$12.31

11

12

13

14

15

16

17

18

19

20

21

22

23

24

-1

25

H

► M \ She

etl / Sheet

/ Sheet1! }. 5heet3 j

■ ■

Ready

The macro checks the values of the specified expression.

JUMP TO A SPECIFIC LOCATION IN A MACRO

You can jump to a named location within your macro using the GoTo command. In order to use a GoTo statement, you need to have a label within your procedure that the GoTo statement can reference. The label is just a text string followed by a colon. The GoTo command references that label and passes control from the current location in the procedure to the labeled section.

GoTo commands date back to the days when you performed programming on mainframe computers and each line of code had a specific line number. The GoTo command jumped directly to the specified line of code. Now, you only use GoTo commands in situations where you cannot obtain the desired results using other conditional statements and loopings structures. Although you frequently use the command for trapping errors in VBA, the hardcore programming community considers using the GoTo command too frequently bad programming. See Chapter 8 for more information on debugging your macro code.

The GoTo command actually consists of two pieces: The GoTo statement and the name of the source label, which follows the statement. You can place the label anywhere in the code of your procedure. Excel often references the labeled area of code as a subprocedure.

The GoTo command can only jump to subprocedures within the same procedure. It cannot reference a subprocedure you place outside the current procedure, even if they both are in the same module. For example, if you have the code GoTo ChangeValue, somewhere else within the same procedure you need to have a ChangeValue statement. When Excel encounters the GoTo ChangeValue statement, it jumps from that location to the statements that follow the ChangeValue statement.

You can add multiple GoTo commands to the same procedure. Each GoTo command can jump to the same labeled command, or to separate commands.

JUMP TO A SPECIFIC LOCATION IN A MACRO

JUMP TO A SPECIFIC LOCATION IN A MACRO

—n Create a new subroutine

0 Type the code that determines when the GoTo statement is needed.

^0 Type GoTo Label, replacing Label with the name of the subprocedure to which you want to jump.

□ Type additional code for procedure.

—n Create a new subroutine

0 Type the code that determines when the GoTo statement is needed.

^0 Type GoTo Label, replacing Label with the name of the subprocedure to which you want to jump.

□ Type additional code for procedure.

0 0

Post a comment