1 / 46

CSI 1306

CSI 1306. PROGRAMMING IN VISUAL BASIC PART 6. Part 6. 1. Event Procedures 2. Translation Set 5 3. Summary. 1. Event Procedures. Objects. GUI displays information in a window (form) a worksheet is a form The Forms toolbar contains drawings of objects

chogan
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 6

  2. Part 6 • 1. Event Procedures • 2. Translation Set 5 • 3. Summary

  3. 1. Event Procedures

  4. Objects • GUI displays information in a window (form) • a worksheet is a form • The Forms toolbar contains drawings of objects • command button, option button, check box, list box • We place objects (controls) on a form for • the user to perform an action or • manipulation by VB procedures or • appearance • Objects have • methods (actions they can do) • properties (characteristics that control their appearance or behaviour)

  5. Objects • Properties define an object’s appearance (size, colour, location, etc.) and name • each object has > 20 properties • font, height, width, left, top, enabled, visible • Some properties can only be changed in a procedure • Others can be changed from their default values by using the Control command on the Format menu

  6. Event Procedures • 2 types of SUB procedures • general procedure (not connected to an object) • event procedure (connected to a user interface object) • run when a specific event occurs to the object, ie. when user or program does something (click with the mouse, press Enter on the keyboard) • code in procedure must initiate some action • one object can respond to >1 event (mouse click & keyboard entry)

  7. Event Procedures • the code in the event procedure runs only when a specific event occurs and can • calculate a result • modify the properties of another object • txtMessage.Text = “This is printed on the screen” • some, such as name, can only be changed by using their property window • Name property • name is referenced in code; caption is seen on screen (default is same as default name) • to change the caption on a command button on a worksheet • select the object by pressing CTRL while clicking on the object • type the new caption on the command button

  8. Event Procedures • To place an object on a worksheet and link it to a sub procedure • 1. Place an object on the worksheet • 2. Set its initial properties • 3. Assign a procedure to the event associated with an object • 1. Placing an object on a worksheet • Forms toolbar contains drawings of objects • click the object on the toolbar • position mouse pointer on the form • drag to draw the object

  9. Event Procedures • 2. Set the initial properties for an object • Selecting an object on a worksheet • while pressing CTRL, click the control • change properties • move by dragging handles or borders • assign a procedure to it • View or change properties • select the object • select Control from the Format menu • choose the Properties tab

  10. Event Procedures • 3. Assigning a procedure to the event associated with an object (linking an object to a worksheet) • (a) an existing procedure • (b) a new procedure • (c) editing an existing procedure • (a) an existing procedure • right click the object • choose Assign Macro • select the procedure name • (the Assign macro dialog box also appears after the object is drawn) • the control is now linked to the procedure, ie. clicking a command button will run the procedure

  11. Event Procedures • (b) a new procedure • select the control • click the Edit Code button on the Forms toolbar & a module sheet appears with the opening and closing lines of a sub procedure • Sub Button1_Click() • End Sub • (c) edit a procedure previously associated with the event belonging to a control • select the control • click the Edit Code button on the Forms toolbar & the module sheet with the code opens for editing • OR open the module sheet containing the code

  12. VB Programs • Create custom dialog boxes • group controls from the Forms toolbar together like the built-in dialog boxes supported by Excel • Place the dialog boxes on a worksheet or a dialog sheet • Create data entry forms, wizards, custom menus and custom toolbars • Package as an add-in application with compiled code which is executable and cannot be read by anyone

  13. 2. Translation Set 5

  14. Translate 21 see 18.vb_6.xls • Translate algorithm 5.7 to find out if the value in cell C1 is greater than the total of the column A on worksheet E6

  15. Sub LargeL() Dim T as Single Dim Column as Integer Dim Ttl as Single Worksheets("E6").Activate T = Cells(1,3) Column = 1 Call SumL(Column, Ttl) If (T > Ttl) Then MsgBox("C1 > A") Else MsgBox("C1 <= A") End If End Sub Sub SumL(ByVal Col as Integer, _ ByRef Total as Single) Dim Row as Integer Dim Value as Single Total = 0 Row = 1 Do Until (IsEmpty(Cells(Row, Col))) Value = Cells(Row, Col) Total = Total + Value Row = Row + 1 Loop End Sub LARGEL (11)Get L, N, T (12)Ttl := SUML (L, N) (13) If (T > Ttl) (14) Let Gtr = True (15)Else (16) Let Gtr = False (17)Give Gtr SUML (21)Get L, N (22)Let Total = 0 (23)Let I = 1 (24)Loop When (I <= N) (25) Let Total = Total + LI (26) Let I = I + 1 (27)Finish Loop (28)Give Total

  16. Translate 22 see 18.vb_6.xls • Write an algorithm to calculate the final grade using the “Bias U” method. • For Male Students • Grade = 60%M + 40%F • For Female Students • Grade = 40%M + 60%F • Then translate the algorithm into Visual Basic as a single program.

  17. Name BiasU Given: G F M Change: None Results: Grade Intermediates: None Definition Grade := BiasU(G, F, M) Method Get G Get F Get M If (G = Male) Let Grade = 0.6 * M + 0.4 F Else Let Grade = 0.4 * M + 0.6 F Give Grade Option Explicit 'Written By T. James Sub BiasU () Dim Gender as String*1 Dim Fin as Single Dim Mid as Single Dim Grade as Single Gender = InputBox("Gender") Fin = InputBox("F") Mid = InputBox("M") If (Gender = "M") Then Grade = 0.6*Mid + 0.4*Fin Else Grade = 0.4*Mid + 0.6*Fin End If MsgBox("Grade is " & Grade) End Sub

  18. Translate 23 • Write a function that calculates the grade of a student according to the BiasU method. Use the function in a Visual Basic Program

  19. Name BiasU Given: G F M Change: None Results: Grade Intermediates: None Definition Grade := BiasU(G, F, M) Method Get G Get F Get M If (G = Male) Let Grade = 0.6 * M + 0.4 F Else Let Grade = 0.4 * M + 0.6 F Give Grade Sub CalcGrade () Dim Gender as String*1 Dim Fin as Single Dim Mid as Single Dim Grade as Single Gender = InputBox("Gender") Fin = InputBox("F") Mid = InputBox("M") Grade = BiasU(Mid, Fin, Gender) MsgBox("Grade is " & Grade) End Sub Function BiasU(ByVal M as Single _ ByVal F as Single _ ByVal G as String) _ as Single Dim Grd as Single If (G = "M") Then Grd = 0.6*M + 0.4*F Else Grd = 0.4*M + 0.6*F End If BiasU = Grd End Function

  20. Translate 24 • Use the BiasU function in an Excel worksheet, where Student Name is in Column A, Gender in Column B, Midterm in Column C, and Final in Column D. Put the Grade in Column E.

  21. Translate 24

  22. 3. Summary

  23. Algorithm Get Visual Basic Translation Entered by user = InputBox Button clicked by user = MsgBox From a worksheet = cells(x, y) Computer: Input Data

  24. Algorithm Givens Intermediates Results = Visual Basic Translation Allocate storage space Dim Const Assign a value = Computer: Store Data

  25. Algorithm Arithmetic +, -, /, *, ^, \ Repeat instructions Loop Conditional Branch If Visual Basic Translation +, -, /, *, ^, \ Do while Do until For next If Selectcase Computer: Process Data

  26. Algorithm Give Visual Basic Translation To a user MsgBox To a worksheet Cells(x,y) = Computer: Output Data

  27. Visual Basic • Use of Visual Basic to • Run algorithms • Express complex logic • Provide a suitable user interface • Translating the algorithm definition • Dimensioning 9 types of variables • Boolean, integer, long, single, double, currency, date, string, variant • Const to define a constant • Assignment statements Variable = expression

  28. Visual Basic • Get information from the user • InputBox Variable = InputBox(“prompt message”, “title”, “default input”, Xcor, Ycor) • MsgBox (choice of options by mouse click) Variable = MsgBox(“message”, icons and command buttons, “title”) • Give information to the user • MsgBox MsgBox(“message”)

  29. Visual Basic • Conditional branch statements If – Then - End If If – Then – Else - End If If – Then – ElseIf – Else - End If Select Case - End Select (Case or Case is) • Loop/Repetition statements Do While - Loop • Do - Loop While Do Until - Loop • Do - Loop Until For – Next

  30. Visual Basic • Get information from the worksheet Variable = Cells(row, col) • Give information to the worksheet Cells(row, col) = variable or expression • Worksheets(“Name”).Activate • Layout of a program • Option explicit • Programming style • Comments, line spacing, line indentation

  31. Visual Basic • Debugging programs • Watch • Step into • Step over • Breakpoint • Quick watch • Structured programming • Modules of restricted size and scope • Main module calling others • One entry and one exit from each module • “Go to less” programming

  32. Visual Basic • Sub procedures Sub Name (arguments) Code End Sub • Function procedures Function Name (arguments) as Type Code Name = expression End Function

  33. Visual Basic • Calling a sub procedure Name Argument1, Argument2 Call Name(Argument1, Argument2) • Macro (called from the Tools menu, no arguments) • Calling a function procedure Variable = Name(Arguments) • User defined function (entered in worksheet cell as a formula) • Passing arguments • By reference (ByRef): two way passing • By value (ByVal): one way passing

  34. Visual Basic • Event procedures • Objects with methods and properties • Name property • Linking a worksheet object to a sub procedure • Operators and order of precedence • Boolean or logical operators • Comparison operators • Arithmetic operators

  35. Visual Basic • String functions • Three useful functions for worksheets • IsEmpty() • Application.Function • ActiveCell.Row and ActiveCell.Column

  36. Additional Material

  37. BiasU Going from a Call To a Worksheet Function

  38. Merge the Give and the Function For Row = 3 to 20 Gender = Cells(Row, 2) Fin = Cells(Row, 4) Mid = Cells(Row, 3) Grade = BiasU(Mid, Fin, Gender) Cells(Row, 5) = Grade NextRow For Row = 3 to 20 Gender = Cells(Row, 2) Fin = Cells(Row, 4) Mid = Cells(Row, 3) Cells(Row, 5) = BiasU(Mid, Fin, Gender) NextRow

  39. Merge Get Mid with Function For Row = 3 to 20 Gender = Cells(Row, 2) Fin = Cells(Row, 4) Mid = Cells(Row, 3) Cells(Row, 5) = BiasU(Mid, Fin, Gender) NextRow For Row = 3 to 20 Gender = Cells(Row, 2) Fin = Cells(Row, 4) Cells(Row,5) = BiasU(Cells(Row,3), Fin, Gender) NextRow

  40. Merge All Gets with Function For Row = 3 to 20 Gender = Cells(Row, 2) Fin = Cells(Row, 4) Cells(Row,5) = BiasU(Cells(Row,3), Fin, Gender) NextRow For Row = 3 to 20 Cells(Row,5) = BiasU(Cells(Row,3), Cells(Row,4), Cells(Row,2)) NextRow

  41. Break the Loop Apart For Row = 3 to 20 Cells(Row,5) = BiasU(Cells(Row,3), Cells(Row,4), Cells(Row,2)) NextRow Cells(3,5) = BiasU(Cells(3,3), Cells(3,4), Cells(3,2)) Cells(4,5) = BiasU(Cells(4,3), Cells(4,4), Cells(4,2)) Cells(6,5) = BiasU(Cells(5,3), Cells(5,4), Cells(5,2)) Cells(7,5) = BiasU(Cells(6,3), Cells(6,4), Cells(6,2)) Cells(8,5) = BiasU(Cells(7,3), Cells(7,4), Cells(7,2)) ------------------------------------------------------------ Cells(20,5) = BiasU(Cells(20,3), Cells(20,4), Cells(20,2))

  42. Change Cells() to Cell References Cells(3,5) = BiasU(Cells(3,3), Cells(3,4), Cells(3,2)) Cells(4,5) = BiasU(Cells(4,3), Cells(4,4), Cells(4,2)) Cells(6,5) = BiasU(Cells(5,3), Cells(5,4), Cells(5,2)) Cells(7,5) = BiasU(Cells(6,3), Cells(6,4), Cells(6,2)) Cells(8,5) = BiasU(Cells(7,3), Cells(7,4), Cells(7,2)) ------------------------------------------------------------ Cells(20,5) = BiasU(Cells(20,3), Cells(20,4), Cells(20,2)) E3 = BiasU(C3, D3, B3) E4 = BiasU(C4, D4, B4) E5 = BiasU(C5, D5, B5) E6 = BiasU(C6, D6, B6) E7 = BiasU(C7, D7, B7) -------------------------- E20 = BiasU(C20, D20, B20)

  43. Homework

  44. In the code that follows, What is displayed in each MsgBox? Which of the procedures is a macro? Which of the procedures can be used in a formula in a worksheet cell? List the actual and formal parameters? What is the significance of being able to pass parameters between procedures?

  45. Sub Main() Dim a As Integer, c As Integer Dim Result As Single, b As Single Const d = 1.5 a = 1 b = 2 c = 3 Result = Apples(a, b + d, c) MsgBox ("a= " & a & " b= " & b & " c= " & c & _ “ Result= " & Result) Call Oranges(b + d, Result, a) MsgBox ("a= " & a & " b= " & b & " c= " & c & _ “ Result= " & Result) End Sub CONTINUED

  46. Function Apples(x As Integer, y As Single, ByVal z As _ Integer) As Single x = 2 * x y = 2 * y z = 2 * z Apples = x + y + z End Function Sub Oranges(a As Single, b As Single, c As Integer) a = 2 * a b = 2 * b c = 2 * c End Sub

More Related