Notes on Listing

The purpose of this program is to validate the week_sales range data input to ensure that each item in the range is both numeric and between the range 0-100. The VBA macro has been called validateWeekSales.

Dim cell as Object declares that cell is an Object. The purpose of this line will become more meaningful when we look at variables in Chapter 5.

Worksheets("Weeklysales").select will ensure that the ActiveSheet is the Weeklysales worksheet.

For Each Cell in Range("week_sales")

takes each cell in the range of cells called week_sales in order to do something to it. You will see that there is a Next line later in the program. Any use of a For statement must terminate with a Next statement. This is because the For statement tells the macro to repeat some lines a number of times (in this case the number of cells in the range week_sales), this means the program needs to know at what point the next cell in the range is to be selected. Chapter 7 will look in more detail at statements using For... Next.

If Not IsNumeric(Cell)

will test to see if the cell is numeric. This uses an If.. .Then structure (see in more detail in Chapter 6). The purpose of this statement is to check to see if each cell in the range is not numeric; if it is not then the following statements will be executed:

MsgBox "Please enter a number in cell " &Cell.Address With Cell.Interior .Colorlndex = 3 .Pattern = xlSolid .PatternColorlndex = xlAutomatic End With

In the line following this the ranges is tested:

Elself (Cell < 0) Or (Cell > 100) Then and if the number is invalid, the cell's colour and pattern are set to show the error.

The With construct was described earlier in this chapter.

The next line Next was described a little earlier. Finally, the program terminates with an End Sub line. Figure 4.5 gives a screenshot during the macro run.

S Microsoft Excel - SALESMAN

¡^T) File Edit View Insert Format lools Data Window Help

A

B 1 G

D

m

1 F

0

1 H

18

for each representative.

13

20

I Formulae Descriptions:

21

r'lil oi \I'.".:h Sales:

[Each cell value in this is c

alculated by adding the Sales To Date to the Murjthli.r

22

Motr'T'lv BoJlliS:

Each cell value in this is c

alculated by trailtijjlymg Monthly Total by the Bonus

'23

Sales To Date:

Each cell in this range is carried over from the End of Month Sales column for tl

24

Month rio:

This cell value is

incremented at the start of each new month

25

26

Sales Data:

27

28

H <> c. 1 i> Pate:

5

23

Month No:

! il

30.

Weekly Sales

31

Rep Name

Sales To Date

1

2

3

4

Monthly Total

1 Montltly Bum

32

Andy

1-3S-"

54

5

6

12

77

£

3S3-.I

33

Bindi

4J9

32

43

28

33

136

630 1

34'

Katie

3}7

16

33

mm,

mm

m

35

Misba

J40

32

77

I m

IRQ

i

900.1

36

AH

489

Microsoft Exce

I

7$51

37

Amy

33Ï

Flease enter number between 0 & 100 in CellfE|36

965 1

38

Pete-

336

613!

33

Rhrannon

4i3

2,783-J

i

OK

40

Gareth

330

1_

J

610.1

41

¡TOTALS

34321

95

^UUI

r "i

Figure 4.5 Screenshot of Listing 4.2 during program run

0 0

Post a comment