CSI 1306

PROGRAMMING IN VISUAL BASIC

PART 5

Part 5

1. Procedures 2. Sub Procedures 3. Function Procedures

ProceduresA procedure is a block of VB code entered in a

VB module that is executed (run) as a unitMany procedures can be entered in a single

moduleMany modules can be added to a single workbook

– To add a new module to a workbook, select Module from the Insert menu

Procedures

To simplify solving a problem, we– Divide the problem into a number of smaller problems,

and

– Develop an algorithm for each of these smaller problems, then

– Develop a main algorithm (with actual parameters) that assembles the algorithms (with formal parameters) as appropriate to solve the problem

Procedures

When we subdivide a problem, translating the solution into Visual Basic code involves– Translating the algorithm for each smaller problem into

a Visual Basic procedure

– Developing a main procedure (with actual parameters) that assembles the other procedures (with formal parameters) as appropriate

Procedures This technique is known as structured programming. It

is characterized as Modular (each procedure dedicated to one task which is

of limited and manageable size) Has a “Main-line procedure” that controls program flow

by branching to the various sub-problem procedures Single entry to, and single exit from, each procedure “GoTo-less” code (not using the GoTo statement avoids

spaghetti code)

Procedures

As we will see

– We can debug each procedure independently, a much simpler process than trying to debug a complex program as a whole

– The procedures are reusable. We can combine a procedure, as appropriate, with other procedures to solve other problems

ProceduresTo “call” a procedure means to run (execute) itWhen one procedure calls a second

– The first procedure is the calling procedure; the second procedure is the called procedure

– VB first looks for the called procedure in the module containing the calling procedure

– if not found, it looks in all the other modules in the workbook

There are two types of procedures– SUB procedures– FUNCTION procedures

2. Sub Procedures

Sub Procedures

A sub procedure performs a series of operations and actions but does not return a value to the calling procedureSub Name (arguments)

CODEEnd Sub

A sub procedure without arguments can be run on its own or be called by another procedure– For example, it can be run from a worksheet by

selecting Macro from the Tools menu (Tools >> Macro >> Macros)

• A sub procedure without arguments is known as a macro

Sub Procedures

A sub procedure with arguments can only execute when it is called by another procedure– Arguments are declared the same way as variables, but

without the word Dim– If there is more than one argument, they are separated

by commas– The calling procedure passes values for the arguments

to the called procedure

Sub Name (X as Integer, Y as String, Z as Single)CODE

End Sub

Sub ProceduresTo call a sub procedure without arguments, use

eitherCall Name

To call a sub procedure with arguments, use eitherCall Name (Argument1, Argument2,….)

• ie. Call FindMax (X, Y, Z)

Sub Procedures

If you call a procedure that has arguments, as we saw with algorithms,– The values of the arguments in the calling procedure

(the actual parameters) are passed to the arguments in the called procedure (the formal parameters)

– The arguments in the calling procedure may be• Constants, expressions or variables

Sub Procedures

Sub Main()

'The calling procedure. Will show up on Tools Macro

Dim X as Integer

Dim Y as Single

Dim Z as Boolean

Call One (X, Y, Z) 'OK

Call One (X, Y) 'Not OK what about Z?

Call One (Y, Z, X) 'Not OK order is Int, Single, Boolean!

End Sub

Sub One (A as Integer, B as Single, C as Boolean)

'The called procedure. Will not show on the Tools menu

CODE

End Sub

Sub Procedures

When the arguments in the calling procedure are variables, the called procedure can change the value of these variables (passing by reference or two-way passing), using the word ByRef

If you want to avoid the possibility of the called procedure changing the value of variables in the calling procedure, preface the arguments in the called procedure with the word ByVal (passing by value or one-way passing)

In either case, the number and type of arguments in the calling statement must agree with those in the called procedure

Sub Procedures

Call Name (X, Y) ‘a statement in the calling procedure

Sub Name (ByRef A as Integer, ByVal B as Integer)‘the header for the called procedure

Sub ProceduresSub Main ()

Dim X as IntegerDim Y as Single

X = 3Y = 3.1

Call One (X, Y)MsgBox (“X = “ & X & “Y = “ & Y)

End Sub

Sub One (ByRef A as Integer, ByVal B as Single)A = 9B = 2.1

End Sub

Main One X Y A B 3 3 3.1 3 3.1 9 3.1 9 2.1 9 3.1

Example 1

Sub Procedures

Observe that we are not concerned about the possibility of identical names for variables or arguments in the calling and called procedures– Since they are in separate procedures, even if a variable

or argument name is identical in the two procedures, each is evaluated independently by Visual Basic (ie. as if they bear no relationship to one another)

This is why our procedure code is portable and reusable

However, we often want the called procedure to return a value

3. Function Procedures

Function Procedures

A function procedure performs a series of operations and returns a single valueFunction Name (arguments) as Type

CODEName = Expression

End Function

Observe that a function procedure will always contain an assignment statement that assigns a value to a variable with the name of the function

The type of that variable is defined in the function header line (as Type)

Function Procedures

A function procedure is never run by itself. It is either called from within another procedure or from a formula in a worksheet cell

To call a function procedure from within another procedure, use the function in an assignment statementvariable = Name(arguments)– For example, cells(6, 8) = Profit (us,pc,sp) calls the procedure

Function Profit(ByVal UnitsSold As Integer, ByVal ProdCost As Single, _

ByVal SalePrice As Single) As Single

Profit = UnitsSold * (SalePrice - ProdCost)

End Function

Function Procedures

Z = Name (X, Y) ‘a statement in the calling procedure

Function Name (ByRef A as Integer, ByVal X as Single) as IntegerName = ????

End Function

The variable X, an argument in the calling procedure, is a different variable than the variable X, an argument in the called procedure

Function ProceduresSub Main ()

Dim X as Integer

Dim Y as Single

Dim Z as Boolean

X = 1

Y= 1.2

Z = Compare (X, Y)

MsgBox (X & Y & Z)

End Sub

Function Compare (ByRef A as Integer, ByVal B as Single) as BooleanIf (A > B) Then

Compare = TrueElse

Compare = FalseEnd If

A = 3

B = 2.4

End Function

Main CompareX Y Z || A B Compare1 1.2 || || 1 || 1.2 || F || 3 || 2.43 1.2 F ||

Example 2

Function Procedures

a function procedure can be entered in a worksheet cell as a formula ie. called from a worksheet– The function procedure is then known as a user defined

function• It must be entered in a module code window• If it is entered in a worksheet code window (right click sheet

tab and select View code), it cannot be called from a worksheet

If the formula in cell A1 is =Compare(B1,C1)– Cell A1 would contain a True if the value in B1 is

greater than the value in C1. Otherwise, cell A1 would contain False

Translate 19 see 17.vb_5.xls

Translate algorithm 5.1 into Visual Basic using the worksheet CSI1234– Name is in column A– Midterm mark is in column B– Final exam mark is in column C– Final mark is in column D– Data starts in row 3

The algorithm is to find the number of students with a final mark greater than the average final mark

Algorithm 5.1

Putting it all togetherName: AVGLGivens :L, N

Change: NoneResult : AvgIntermediates: Sum, IDefinitionAvg := AVGL(L, N)

Get L, NLet Sum = 0Let I = 1Loop When (I <= N) Let Sum = Sum + LI

Let I = I + 1Finish LoopLet Avg = Sum/NGive Avg

Name: COUNTLGivens: L, N, V

Change: NoneResult: CountIntermediate: IDefinitionCount := COUNTL(L, N, V)

Get L, N, VLet Count = 0Let I = 1Loop When (I <=N)

If (LI > V) Let Count = Count + 1 Let I = I + 1Finish LoopGive Count

Name: MAINGivens: Marks, NStu

Change: NoneResult: NGoodIntermediate: AMarkDefinitionNGood:=MAIN(Marks,Nstu)

Get Marks, NStu

AMark := AVGL(Marks,NStu)

NGood := COUNTL(Marks,

NStu, AMark)

Give NGood

Algorithm 5.1

Our algorithms AVGL & COUNTL process data in a list L of length N.

Let’s rewrite them to process data in a list on a worksheet (WS) starting at a particular row (Row) of column (Col) and ending at the first blank cell in that column.

Algorithm 5.1

Name: AVGLGivens: L, N

Change: NoneResults: AvgIntermediates: Sum, IDefinitionAvg := AVGL(L, N)

Get L, N Let Sum = 0 Let I = 1 Loop When (I <= N) Let Sum = Sum + LI

Let I = I + 1 Finish Loop Let Avg = Sum/N Give Avg

Name: AVGLGivens: Row, Col

Change: NoneResults : AvgIntermediates: Sum, Count, ValueDefinitionAvg := AVGL(Row, Col)

Get Row, Col Let Sum = 0 Let Count = 0 Loop until empty cell Get Value(Row,Col)

Let Sum = Sum + Value Let Count = Count + 1 Let Row = Row + 1

Finish Loop Let Avg = Sum/Count Give Avg

Algorithm 5.1

Now, let’s translate the algorithm into a Visual Basic function procedure so that it can be called from another procedure or from a formula in a worksheet.

Algorithm 5.1Name: AVGLGivens: Row, Col

Change: NoneResults : AvgIntermediates: Sum, Count, ValueDefinitionAvg := AVGL(Row, Col)

Get Row, Col Let Sum = 0 Let Count = 0 Loop until empty cell Get Value(Row,Col)

Let Sum = Sum + Value Let Count = Count + 1 Let Row = Row + 1

Finish Loop Let Avg = Sum/Count Give Avg

Function AVGL(ByVal Row As Integer, _ ByVal Col as Integer) As Single

Dim Sum as Single Dim Value as Single Dim Count as Integer Dim Avg as Single

Sum = 0 Count = 0 Do Until (IsEmpty(Cells(Row, Col))) Value = Cells(Row, Col) Sum = Sum + Value Count = Count + 1 Row = Row + 1 Loop

Avg = Sum/Count AVGL = AvgEnd Function

Algorithm 5.1

Applying the same process to COUNTL produces the following function procedure.

Algorithm 5.1

Name: COUNTLGivens: Col, Row, V, Value

Change: NoneResult: CountIntermediate: DefinitionCount := COUNTL(Row, Col, V)

Get Row, Col, V Let Count = 0 Loop until empty cell

Get Value(Row,Col)

If (Value > V) Let Count = Count + 1 Row = Row + 1 Finish Loop Give Count

Function CountL(ByVal Row As Integer, ByVal Col As Integer, ByVal V As Single) As Integer

Dim Value As SingleDim Count As Integer

Count = 0Do Until IsEmpty(Cells(Row, Col)) Value = Cells(Row, Col) If (Value > V) Then Count = Count + 1 End If Row = Row + 1LoopCountL = Count

End Function

Name: MAINGivens: Marks, NStu

Change: NoneResults: NGoodIntermediate: AMarkDefinitionNGood:=MAIN(Marks,Nstu)

Get Marks, NStu

AMark := AVGL(Marks,NStu)

NGood := COUNTL(Marks,NStu, AMark)

Give NGood

Sub Main()

Dim Col as Integer

Dim Row As Integer

Dim Ngood as Integer

Dim AMark as Single

Worksheets(“CSI1234”).Activate

Row = 3 Col = 4 Amark = AVGL(Row, Col)

Ngood = COUNTL(Row, Col, Amark)

MsgBox(Ngood & “>Avg”)

End Sub

Algorithm 5.1

Name: MAINGivens: Row, Col, WS

Change: NoneResults: NGoodIntermediate: AMarkDefinitionNGood:=MAIN(Row, Col, WS)

Go to Worksheet WS

AMark := AVGL(Row, Col)

NGood := COUNTL(Row, Col, AMark)

Give NGood

Algorithm 5.1

Function AVGL(ByVal Row _ As Integer, ByVal Col As _ Integer) As Single

Dim Sum As Single

Dim Value As Single

Dim Count As Integer

Sum = 0

Count = 0

Do Until IsEmpty(Cells(Row, _ Col))

Value = Cells(Row, Col)

Sum = Sum + Value

Count = Count + 1

Row = Row + 1

Loop

AvgL = Sum / Count

End Function

Function COUNTL(ByVal _ Row As Integer, ByVal Col As _ Integer, ByVal V As Single) As _ Integer

Dim Value As Single

Dim Count As Integer

Count = 0

Do Until IsEmpty(Cells(Row, _ Col))

Value = Cells(Row, Col)

If (Value > V) Then

Count = Count + 1

End If

Row = Row + 1

Loop

CountL = Count

End Function

Sub Main()

Dim Col as Integer

Dim Row As Integer

Dim Ngood as Integer

Dim AMark as Single

Worksheets("CSI1234").Activate

Row = 3 Col = 4 Amark = AVGL(Row, Col)

NGood = COUNTL(Row, _ Col, Amark)

MsgBox(Ngood & ">Avg")

End Sub

Translate 20 see 17.vb_5.xls

Use algorithm 5.4 to find how many ties there were for the maximum grade in CSI1234– Name is in column A

– Midterm mark is in column B

– Final exam mark is in column C

– Final mark is in column D

– Data starts in row 3 and ends in row 20

CMAX(01) Get L, N(02) Max := MAXL(L,N)(03) Nmax :=

SEARCHK(L,N,Max)(04) Give NMax

MAXL(11) Get L, N(12) Max = -1(13) I = 1(14) Loop When (I <= N)(15) If (LI > Max)(16) Max = LI

(17) I = I + 1(18) Finish Loop(19) Give MaxCOUNTk(21) Get L, N, K(22) C = 0(23) I = 1(24) Loop When (I <= N)(25) If (LI = K)(26) C = C + 1

(27) I = I + 1(28) Finish Loop

(29) Give C

Sub Cmax() Dim FinCol as Integer Dim MaxFin as Single Dim CFin as Integer Worksheets("CSI1234").Activate FinCol = 4 Call MaxL(FinCol, MaxFin) Call CountK(FinCol, MaxFin, Cfin) MsgBox(Cfin & " Tied For Max")End Sub

CMAX(01) Get L, N(02) Max := MAXL(L,N)(03) Nmax :=

SEARCHK(L,N,Max)(04) Give NMax

MAXL(11) Get L, N(12) Max = -1(13) I = 1(14) Loop When (I <= N)(15) If (LI > Max)(16) Max = LI

(17) I = I + 1(18) Finish Loop(19) Give MaxCOUNTk(21) Get L, N, K(22) C = 0(23) I = 1(24) Loop When (I <= N)(25) If (LI = K)(26) C = C + 1

(27) I = I + 1(28) Finish Loop

(29) Give C

Sub MaxL (ByVal Col as Integer, _

ByRef Max as Single)

Dim Row as Integer

Dim Value as Single

Const FirstRow = 3

Const LastRow = 20

Max = -1

For Row = FirstRow to LastRow

Value = Cells(Row, Col)

If (Value > Max) Then

Max = Value

End If

Next Row

End Sub

CMAX(01) Get L, N(02) Max := MAXL(L,N)(03) Nmax :=

SEARCHK(L,N,Max)(04) Give NMax

MAXL(11) Get L, N(12) Max = -1(13) I = 1(14) Loop When (I <= N)(15) If (LI > Max)(16) Max = LI

(17) I = I + 1(18) Finish Loop(19) Give MaxCOUNTk(21) Get L, N, K(22) C = 0(23) I = 1(24) Loop When (I <= N)(25) If (LI = K)(26) C = C + 1

(27) I = I + 1(28) Finish Loop

(29) Give C

Sub COUNTK (ByVal Col as Integer, _

ByVal V as Single, _

ByRef C as Integer)

Dim Row as Integer

Dim Value as Single

Const FirstRow = 3

Const LastRow = 20

C = 0

For Row = FirstRow to LastRow

Value = Cells(Row, Col)

If (Value = V) Then

C = C + 1

End If

Next Row

End Sub

Homework

When and where do you use each of the following?

Dim x As Integer

ByRef x As Integer

ByVal x As Integer

Write a sub procedure that takes one parameter and produces a message box that says “The argument sent to me was: “ and then displays the value of the parameter. Now write a sub procedure that calls this procedure.

Create a function procedure that uses several arguments and returns a value based on a calculation. Write a sub procedure that calls this function procedure.

Write a sub procedure that consists entirely of calls to other procedures. Use at least three procedure calls, one function procedure, one sub procedure and one requiring arguments. Now write the procedures that it calls.