1 / 41

CSI 1306

CSI 1306. PROGRAMMING IN VISUAL BASIC PART 5. Part 5. 1. Procedures 2. Sub Procedures 3. Function Procedures. Procedures. A procedure is a block of VB code entered in a VB module that is executed (run) as a unit Many procedures can be entered in a single module

psyche
Download Presentation

CSI 1306

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CSI 1306 PROGRAMMING IN VISUAL BASIC PART 5

  2. Part 5 • 1. Procedures • 2. Sub Procedures • 3. Function Procedures

  3. Procedures • A procedure is a block of VB code entered in a VB module that is executed (run) as a unit • Many procedures can be entered in a single module • Many modules can be added to a single workbook • To add a new module to a workbook, select Module from the Insert menu

  4. 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

  5. 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

  6. Procedures • This technique is known as structuredprogramming. 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)

  7. 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

  8. Procedures • To “call” a procedure means to run (execute) it • When 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

  9. 2. Sub Procedures

  10. Sub Procedures • A sub procedure performs a series of operations and actions but does not return a value to the calling procedure Sub Name (arguments) CODE End 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

  11. 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

  12. Sub Procedures • To call a sub procedure without arguments, use either Call Name • To call a sub procedure with arguments, use either Call Name (Argument1, Argument2,….) • ie. Call FindMax (X, Y, Z)

  13. 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

  14. 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

  15. Sub Procedures • When the arguments in the calling procedure are variables, the called procedure can change thevalue of these variables (passing by reference ortwo-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 (passingby 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

  16. 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

  17. Main One X Y A B 3 3 3.1 3 3.1 9 3.1 9 2.1 9 3.1 Sub Procedures Sub Main () Dim X as Integer Dim Y as Single X = 3 Y = 3.1 Call One (X, Y) MsgBox (“X = “ & X & “Y = “ & Y) End Sub Sub One (ByRef A as Integer, ByVal B as Single) A = 9 B = 2.1 End Sub

  18. 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

  19. 3. Function Procedures

  20. Function Procedures • A function procedure performs a series of operations and returns a single value Function Name (arguments) as Type CODE Name = 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)

  21. 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 statement variable = 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

  22. Function Procedures Z = Name (X, Y) ‘a statement in the calling procedure Function Name (ByRef A as Integer, ByVal X as Single) as Integer Name = ???? 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

  23. Sub 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 Boolean If (A > B) Then Compare = True Else Compare = False End If A = 3 B = 2.4 End Function Main Compare X Y Z || A B Compare 1 1.2 || || 1 || 1.2 || F || 3 || 2.4 3 1.2 F || Function Procedures

  24. 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

  25. 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

  26. Algorithm 5.1 • Putting it all together • Name: AVGL • Givens :L, N • Change: None • Result : Avg • Intermediates: Sum, I • Definition • Avg := 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: COUNTL • Givens: L, N, V • Change: None • Result: Count • Intermediate: I • Definition • Count := COUNTL(L, N, V) • Get L, N, V • Let Count = 0 • Let I = 1 • Loop When (I <=N) • If (LI > V) • Let Count = Count + 1 • Let I = I + 1 • Finish Loop • Give Count • Name: MAIN • Givens: Marks, NStu • Change: None • Result: NGood • Intermediate: AMark • Definition • NGood:=MAIN(Marks,Nstu) • Get Marks, NStu • AMark := AVGL(Marks,NStu) • NGood := COUNTL(Marks, • NStu, AMark) • Give NGood

  27. 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.

  28. Name: AVGL Givens: L, N Change: None Results: Avg Intermediates: Sum, I Definition Avg := 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: AVGL Givens: Row, Col Change: None Results : Avg Intermediates: Sum, Count, Value Definition Avg := 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

  29. 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.

  30. Name: AVGL Givens: Row, Col Change: None Results : Avg Intermediates: Sum, Count, Value Definition Avg := 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 = Avg End Function Algorithm 5.1

  31. Algorithm 5.1 • Applying the same process to COUNTL produces the following function procedure.

  32. Name: COUNTL Givens: Col, Row, V, Value Change: None Result: Count Intermediate: Definition Count := 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 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 Algorithm 5.1

  33. Algorithm 5.1 • Name: MAIN • Givens: Marks, NStu • Change: None • Results: NGood • Intermediate: AMark • Definition • NGood:=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 • Name: MAIN • Givens: Row, Col, WS • Change: None • Results: NGood • Intermediate: AMark • Definition • NGood:=MAIN(Row, Col, WS) • Go to Worksheet WS • AMark := AVGL(Row, Col) • NGood := COUNTL(Row, Col, AMark) • Give NGood

  34. Algorithm 5.1

  35. 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

  36. 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 Max 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 COUNTk (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

  37. CMAX (01) Get L, N (02) Max := MAXL(L,N) (03) Nmax := SEARCHK(L,N,Max) (04) Give NMax 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 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 Max COUNTk (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

  38. CMAX (01) Get L, N (02) Max := MAXL(L,N) (03) Nmax := SEARCHK(L,N,Max) (04) Give NMax 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 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 Max COUNTk (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

  39. Homework

  40. When and where do you use each of the following? Dim x As Integer ByRef x As Integer ByVal x As Integer

  41. 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.

More Related