Loops controlled by a number

The previous example used a For .. .Next loop to go through all the cells in a range - this is a very powerful way to process spreadsheets. You can also use a number to control how many times a For .Next loop executed. This kind of loop is very common in traditional programming languages when general variables are used. For example, the weekly sales data in the SALESMAN workbook might require calculations to find the total weekly mileage from seven daily totals. Rather than do this manually, a macro could be written whose purpose is to provide an input box for the user to enter the number of sales recorded for a salesman for each day for seven days of the week, add them up and display the total in a message box (see Figure 7.2). In structured English, the program might look like this:

Get Daily Sales set the totalSales to zero For dayCount = 1 To 7

get the sales for day dayCount add this sales to totalSales Next day value return the totalSales End

The macro is written in Listing 7.2 and a screenshot taken during the program run is shown in Figure 7.3.

Sub getDailySales()

Dim totalSales As Integer Dim thisDaySales As Variant Dim dayCount As Integer totalSales = 0 For dayCount = 1 To 7 thisDaySales = InputBox(prompt:="enter sales for day: " & dayCount) totalSales = totalSales + thisDaySales Next strTotalSales = str(totalSales)

MsgBox "The total sales over the 7 days are=" + strTotalSales End Sub

ES Microsoft Excel - SALESMAN

Jolii

File Edit VîevM Insert Format Tools Data Window Help - ¿P X

End ofMnnih Sales'. Monthly Boiuis: Sales To Date: ,r:ter soles for day: i. Month No:

Month Wo:

Rep Name

Andy

Bindi

K.atie

Mist) a

Pets

Rhiannon tjaieth

Sales Te Dite

13 j 43S

3J7 SO

333 336 453_ 330

Weekly Sales

52 13

402 68

Ready

33l_18

Monthly Total

Figure 7.2 Screenshot during run of Listing 7.2

Figure 7.2 shows the output from this program. Notice that during the run each day's sales is input using a separate InputBox. The program output total is then included in the Active Cell as well as a MsgBox telling the user the total of the 7 days' sales.

S Microsoft Excel - SALESMAN

a] File Edit View Insert Format lools Data Window Help

A

B.

0

a

M . F 6

m

laid of Month Sales:

Each cell value in thisis calculated:by adding . . 7 !"."■ to tj

Monthly Bonus:

Each cell value in this is calculated ^ multiplying Monthly Total by

Sales To Date:

Each i ell in this range is..earned over from the End of Month Sales ■ 1

24

Month No:

This cell value is

incremented at the start of each new :nfilth

25-

:26-

Sales Data:

MB

ÎÏR:

Bonus Sate:

s

20-

Month No:

1

■--

■ 30:

31

Rep Mame

Sales To Dale

The total sales over the 7 days are= 30

M

M

A.t>f-y

fife

77

I il

Eincii

459

136

t

34

Katie

3J7

iassSSiisssH

372

t

35

Misba

Î4Q:

ISO

* •

SM HPI

36

All

4S9

44

54

■ 59|

m

£

37

Amy

â»

■20

56'

391 70|'

193

t ■

30-

Pete'

336-

21

100

9 -7

123

I

39:

Pihiaimon

433,

33

54

wi est

■557:

t

tec

;5i

44

H -isr

IS

I -

H

► $ \ Sheet2 / Shiest 1 \weeklysales

week sa | 4

P,5dCy

Figure 7.3 MsgBox output on Excel weeksales sheet

The For... Next loop in this example works using a counter. The counter:

♦ starts at a particular value, in this case 1

♦ terminates at a particular value, in this case 7

♦ increases by a step value each time through the loop, in this case +1 This form of a For. Next loop, has the general syntax:

For count = lowerValue to upperValue Step stepValue

Where lowerValue, upperValue and stepValue are all integers. For example:

In this loop, the lowerValue =3, upperValue =15 and stepValue =3. This means that the loop will be executed with values of count of 3, 6, 9,12, 15.

The loop terminates when the count becomes larger than the upperValue. There's an exception to this: If you code a negative stepValue, the loop terminates when the count becomes smaller than the upperValue. If you specify a negative stepValue, upperValue must be less than lowerValue or VBA will execute the loop only once.

0 0

Post a comment