Using parameters to extend the scope of functions

Parameters provide a way of transferring data between sub procedures and function procedures. We can often extend the capabilities of a function by using parameters. For example, suppose we wanted to extend the getMaxSales function that was developed in Listing 9.6 to provide the cell address and the RepName of the salesperson who recorded the maximum sale. The extended function procedure, called GetMaxSaleslnfo, is shown in Listing 9.7. Notice that in the new function definition, we have passed two arguments: one called maxSalesAddress, and the other called thisRepName. The function only differs from that of Listing 9.6 in that whenever maxSales > myCell, maxSalesAddress is assigned to myCell.Addess and thisRepName is assigned to Worksheets("weeklysales").Cells(myCell.Row, 1). Value. This means that the final values of these arguments will contain the cell address, and name of the rep giving the highest sales during the period. Notice, this function is called using Listing 9.8. This uses a MsgBox to output the maximum sales, the cell address, and the corresponding rep name.

Listing 9.7 Function getMaxSales()

Function GetMaxSalesInfo(maxSalesAddress As String, thisRepName As

String) As Integer

Dim maxSales As Integer

Dim myCell As Object

maxSales = 0

'initialise to the lowest possible value

For Each myCell In Range("Week_sales")

If myCell > maxSales Then

maxSales = myCell

'we have found a bigger sales

maxSalesAddress = myCell.Address

thisRepName = Worksheets("Weeklysales").CeNs(myCeN.Row,1).Value

End If

Next

GetMaxSalesInfo = maxSales

End Function

Listing 9.8 Calling the extended function

Sub testMaxSalesInfo()

Dim theAddress As String

Dim theRepName As String

Dim theMax As Integer

theMax = GetMaxSalesInfo(theAddress, theRepName)

MsgBox "the maximum is: " & theMax & vbCrLf &

"the address is: " & theAddress & vbCrLf &

"the rep name is: " & theRepName

End Sub

E2 Microsoft Excel - SALESMAN

-I'D®

■ ]

File Edit View Insert Format Tools Data Window Help

Type a question for help t _ ß x

y [ ü üb

** Times New Roman

. 10 - B I U

mi'» _-

■ <3» - A - »

v-

f-

A

B

fe

DIE Fl

:

I H

I —

12

Salesman Workbook Data

13

System Information

14

Workbook Name:

SALESMAN

i

15

Wo i k'. lit'i" Name:

weeklysales

16

System Purpose:

To maintain weekly sales data for each representative, and

17

"to calculate sales to date, the monthly bonus for each

s ale sp ers on & month total

18

for each representative.

19

20

Each cell value in this is t

__I____________J

21

End of Month Sales:

the maximum isr 402

the address isL

the rep name.is: Rhiannon

5 To Date to the Monthly Total

22

Monikly Bonus;

Each cell value in this is <

ithly Total by the Bonus Rate

23

Sales To Date:

Each cell in this range is i

lonth Sales column for the beginning of each t

24

Month No:

This cell value is increme

ttonth

25

OK

26

Safes D

27

28

Bonus Rate:

5

4Ü2

39

Month No:

¡ il

r

30

Weekly Sales

31

Rep Name

Sales To Date

i

1 2 1 3 1 4 1 MontSdyTotai

Monthly Bemis

Ettrt of Month Sa!

Bmdi

Katie

Misfca

Pete

Rhiannon r;»,pti-,

135

54

771

£ 385 J)D

33

4J9

32

1 43| 28| 33| 1361

£ imr,a

34

357

23-

1 331 1501 166|

3721

£ 1Ü6GJM)

35

J40

32

771 521 19Í 1B0|

£ 9(¡0D[I

"-!.

439

44,

ra srw id

£ 785.00

i—

37

3Î3

20

! 56| 391 m

193|

£ 965110

38

336

21

iiGöi si -7[ m

£ 6Í5H0

39

453

33

1 541 4021 68 f

5571

£ _2,?85.0U

Ii

Üfl

Tin

üfl

■H i "i"; ! 1 Hf

F Ainnn

Figure 9. 5 Screenshot of Listing 9.8

0 0

Post a comment