1 / 52

CSI 1306

CSI 1306. PROGRAMMING IN VISUAL BASIC PART 4. Part 4. 1. Communicating with the Worksheet 2. More about the MsgBox 3. Additional Material. 1. Communicating with the Worksheet. GET/GIVE from/to the Worksheet. In Excel, when writing a formula,

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

  2. Part 4 • 1. Communicating with the Worksheet • 2. More about the MsgBox • 3. Additional Material

  3. 1. Communicating with the Worksheet

  4. GET/GIVE from/to the Worksheet • In Excel, when writing a formula, • We reference a cell by using its cell address (column and row coordinates) • A1 or C2 or D3 • If the cell is on another worksheet, we preface the cell address with name of that worksheet and an ! • Optimistic!A1 or Judy!C2 or Case1!D3

  5. GET/GIVE from/to the Worksheet • In Visual Basic • We reference a cell by using the cells function, Cells(row,column) • Cells(1, 1) for A1, Cells(2, 3) for C2, Cells(3, 4) for D3 • Both the row and column coordinates are expressed as numbers • The row coordinate precedes the column coordinate • We reference the worksheet by using the statement, Worksheets(“Name”).Activate, in our program • Worksheets(“Optimistic”).Activate, Worksheets(“Judy”).Activate, Worksheets(“Case1”).Activate

  6. GET/GIVE from/to the Worksheet • Get • Variable = Cells(row, col) • We are assigning the value in the worksheet cell to a variable in our program. (Cells function is right of the = sign) X = Cells(5, 9) • Assigns the value in the worksheet cell (row 5, column 9) to the variable X • Give • Cells(row, col) = expression • We are assigning the value of the expression in our program to a cell on a worksheet. (Cells function is left of the = sign) Cells(5,9) = X • Assigns the value in X to the worksheet cell (row 5, column 9)

  7. GET/GIVE from/to the Worksheet • Three additional useful functions • IsEmpty() tests if a specific cell is empty • Returns a True or False • IsEmpty(Cells(1, 2)) • If Cells(1, 2) is empty, returns a True • If there is a value in the cell, returns a False • Do Until (IsEmpty Cells(1, 2)) • Application.Function allows your program to use an Excel function • Cells(1, 2) = Application.RoundUp(apples,0)

  8. GET/GIVE from/to the Worksheet • We occasionally need to refer in our Visual Basic program to the active cell in a worksheet ActiveCell.Row The row of the active cell ActiveCell.Column  The column of the active cell • If the active cell is D5 • col = ActiveCell.Column • col becomes 4 • row = ActiveCell.Row • row becomes 5

  9. Translating Get/Give from/to the Worksheet • Look at the METHOD. • For any GET or GIVE that refers to the worksheet rather than to the user: • Activate the worksheet • Worksheets(“Name”).Activate • Get • Variable = Cells(row, col) • Give • Cells(row, col) = expression

  10. Example Worksheet Name is Stats

  11. Example • Write a program that places the product of Cat 1 and Cat 2 in each row into the Result column in that row Algorithm Go To Worksheet Stats Loop from row 2 to 5 Get Cat1 Get Cat2 Let Result = Cat1*Cat2 Give Result End Loop

  12. Example (See 14.vb_4a.xls) OPTION EXPLICIT 'Written By T. James Sub Prod1() Dim Row as Integer 'Current row Dim Cat1 as Integer 'Val 1 Dim Cat2 as Integer 'Val 2 Dim Result as Integer 'Result Worksheets("Stats").Activate For Row = 2 to 5 Cat1 = Cells(Row, 2) Cat2 = Cells(Row, 3) Result = Cat1 * Cat2 Cells(Row, 4) = Result Next Row End Sub

  13. Write the program such that: Result is 1 if the number in either category is greater than 5 Result is 2 if both numbers are greater than 5 Result is zero otherwise Algorithm Go to Worksheet Stats Loop from row 2 to 5 Get Cat1 Get Cat2 If (Cat1>5) and (Cat2 >5) Let Result = 2 Else If (Cat1 > 5) or (Cat2 > 5) Let Result = 1 Else Let Result = 0 Give Result End Loop Example

  14. Example (see 14.vb_4a.xls) OPTION EXPLICIT 'Written By T. James Sub Prod2() Dim Row as Integer 'Current row Dim Cat1 as Integer 'Val 1 Dim Cat2 as Integer 'Val 2 Dim Result as Integer 'Result Worksheets("Stats").Activate For Row = 2 to 5 Cat1 = Cells(Row, 2) Cat2 = Cells(Row, 3) If (Cat1 > 5) and (Cat2 > 5) Then Result = 2 ElseIf (Cat1 > 5) or (Cat2 > 5) Then Result = 1 Else Result = 0 End If Cells(Row, 4) = Result Next Row End Sub

  15. Example

  16. Example • Using data as shown on the worksheet on the previous slide, write a procedure which when executed will write in cell E2 the number of Music students who received a grade larger than 75 in both CSI1100 and CSI1101 • Worksheet name is Grades

  17. Example • Algorithm Go to Worksheet Grades Count = 0 Loop for each Student Get CSI1100 mark Get CSI1101 mark Get Department If (CSI1101 > 75) and (CSI1100>75) and _ (Department = “Music”) Let Count = Count + 1 Go to Next Student End Loop Give Count

  18. Example (see 14.vb_4a.xls) OPTION EXPLICIT Sub Music() Dim Count as Integer 'Number of students Dim CSI1101 as Integer 'Mark Dim CSI1100 as Integer 'Mark Dim Dept as String 'Department Dim Row as Integer 'Row of Student Worksheets("Grades").Activate Count = 0 Row = 2 Do Until (IsEmpty(Cells(Row, 1))) CSI1100 = Cells(Row, 3) CSI1101 = Cells(Row, 4) Dept = Cells(Row, 2) If (CSI1101 > 75) and (CSI1100 > 75) and _ (Dept = "Music") Then Count = Count + 1 End If Row = Row + 1 Loop Cells(2, 5) = Count End Sub

  19. 2. More about The MsgBox

  20. GET/GIVE from/to the User • A MsgBox is used to send information to the screen for the user to view • MsgBox(“message”) • Message is the information that will be displayed in the MsgBox on the screen • The MsgBox will also contain an OK button • When the user has read the message, he/she will click the OK button and the MsgBox will disappear from the screen

  21. GET/GIVE from/to the User • MsgBox(“The Tax is “ & Tax) • Takes the string “The Tax is “ and concatenates it with the value of the variable Tax • Displays the result on the screen in a MsgBox • Concatenation can also be accomplished with + • Preferable to use &

  22. GET/GIVE from/to the User • An InputBox is used to get information that is entered on the keyboard by the user • Variable = InputBox(“prompt message”, “title”, “default input”, Xcor, Ycor) • Variable stores the information entered by the user • Prompt message describes what we want the user to enter • Title appears in the title bar at the top of the InputBox • Default input shows a sample of the type of information to be entered • Xcor and Ycor are the X and Y axes coordinates that position the InputBox on the screen

  23. GET/GIVE from/to the User • But how do we read the click of a mouse when the user is selecting from 2 or more options? • With the MsgBox • Now we are going to use the MsgBox to translate a Get that involves the user clicking the mouse

  24. GET/GIVE from/to the User • Variable = MsgBox(“message”, icons & command buttons, “title”) • Variable stores the information entered by the user. In this case, an integer value which describes which command button was clicked. • Message describes the question we are asking the user • Icons are pictures which appear in the MsgBox • Command buttons are buttons which appear in the MsgBox • Title appears in the title bar of the MsgBox

  25. GET/GIVE from/to the User

  26. GET/GIVE from/to the User

  27. GET/GIVE from/to the User • YesNo = MsgBox(“Do you wish to Continue?”,36,“Continue?”) • Displays a MsgBox on the user’s screen • Title is Continue? • Message is Do you wish to Continue? • A question mark icon appears in the MsgBox (32) • MsgBox has 2 command buttons, a Yes button and a No button (4) • If user clicks the Yes button, 6 is stored in the variable YesNo • If user clicks the No button, 7 is stored in the variable YesNo

  28. Translation of GET/GIVE from/to the User • Look at the METHOD. For any Get that requires a user to select an option through a mouse click, use the MsgBox function • Variable = MsgBox(“message”, icons & command buttons, “title”)

  29. Example • Write a program to check if the user wants to start a Sum. If yes, continue until the user does not want to add to the Sum. Each time the user says he/she wants to proceed, ask him/her for the value to be added to the Sum.

  30. Example • Algorithm • Let Sum = 0 • Do you want to add to the Sum • If Yes then • Loop Until User says NO • What Value do you want to add? • Let Sum = Sum + Value • Do you want to add more numbers? • End Loop • Give Sum

  31. Example OPTION EXPLICIT 'Written by T. James Sub Total() Dim YesNo as Integer 'Answer to Yes/No Dim Sum as Integer 'The total Dim Value as Integer 'Value entered by user Sum = 0 YesNo = MsgBox("Do you wish to add to Total?", 4) Do Until (YesNo = 7) Value =InputBox("Enter a value to add") Sum = Sum + Value YesNo = MsgBox("More Numbers to Add?", 4) Loop MsgBox("The Total Value is " & Sum) End Sub

  32. 3. Additional Material

  33. Translation Set

  34. Algorithm 3.3 • Translate Algorithm 3.3 using the MsgBox for "Again"

  35. Option Explicit Sub Average10() Dim N as Integer Dim Avg as Single Dim Count as Integer Dim Sum as Integer Dim Again as Integer Count = 0 Sum = 0 Again = 6 '6 is yes Do While (Count<10) And (Again=6) N = InputBox("N") Sum = Sum + N Count = Count + 1 Again = MsgBox("Again", 4) Loop Avg = Sum/Count MsgBox("Average is " & Avg) End Sub • Name: AVERAGE10 • Givens: N • Change: None • Results: AVG • Intermediates: Count, Sum • Again • Definition: • AVG = AVERAGE10(N) • Count = 0 • Sum = 0 • Again = Yes • Loop When (Count < 10) AND • (Again) • Get N • Sum = Sum + N • Count = Count + 1 • Get Again • Finish Loop • AVG = Sum/Count • Give AVG

  36. Algorithm 3.4 • Translate Algorithm 3.4 using the MsgBox for "Again"

  37. Name: AVGPOS • Givens: N • Change: None • Results: Avg • Intermediates: • Again, Sum, Count • Definition: • Avg := AVGPOS(N) Option Explicit 'Written By T. James Sub AvgPos() Dim N as Integer Dim Avg as Single Dim Again as Integer Dim Sum as Integer Dim Count as Integer Sum = 0 Count = 0 Do N = InputBox("Give N") If (N > 0) Then Sum = Sum + N Count = Count + 1 End If Again = MsgBox("Again", 4 + 32) Loop Until (Again = 7) Avg = Sum/Count MsgBox("The Average is " & Avg) End Sub Method Sum = 0 Count = 0 Loop Get N If (N > 0) Sum = Sum + N Count = Count + 1 Get Again Finish Loop When Not(Again) Avg = Sum/Count Give Avg

  38. Programming Style

  39. Programming Style • Write your program so that it is easy to read • For yourself and for anyone else • Helps when debugging • Helps when making changes at a later time • Three useful techniques • Comments • Line spacing • Line indentation

  40. Comments • Comments (or remarks) follow a single quotation mark • They are ignored by the Visual Basic editor • An important comment is the • ‘Written By …… • Where else should we use comments?

  41. Comments • To describe what the program does. Sub Calc_Grades() ‘ This program calculates student grades using ‘ the Bias U rules End Sub VS Sub Calc_Grades() End Sub

  42. Comments • To describe variables Dim A1 as Integer ‘Assignment 1 mark out of 10 Dim A2 as Integer ‘Assignment 2 mark out of 10 Dim A3 as Integer ‘Assignment 3 mark out of 20 Dim Name as String ‘Name of a student VS Dim A1 as Integer Dim A2 as Integer Dim A3 as Integer Dim Name as String

  43. Comments • To describe what the code is doing ‘Initialize at the first row containing student marks Worksheets(“Marks”).Activate row = 3 ‘Get assignment marks A1 = Cells(row, 2) A2 = Cells(row, 3) versus Worksheets(“Marks”).Activate row = 3 A1 = Cells(row, 2) A2 = Cells(Row, 3) Do not add superfluous comments a = b + c ‘Assigns the sum of the values in b and c to a

  44. Line Spacing • Insert blank lines between blocks of code

  45. Option Explicit ‘Written By T. James Sub Q1 () Dim A as Single Dim B as Single A = Val(InputBox(“A”)) B = Val(InputBox(“B”)) B = B + 1 A = A * 2 MsgBox(A & B) End Sub Option Explicit ‘Written By T. James Sub Q1() Dim A as Single Dim B as Single A=Val(InputBox(“A”)) B=Val(InputBox(“B”)) B = B + 1 A = A * 2 MsgBox(A & B) End Sub Line Spacing

  46. Line Spacing • To add more than one line to a MsgBox, use the CHR(13) command MsgBox(“X is “ & X & CHR(13) & “ Y is “ & Y)

  47. Line Spacing • To break a long line of code into multiple lines, enter a space followed by the underscore ( _ ) • DO NOT BREAK WITHIN QUOTES MsgBox(“X is “ & X & CHR(13) & _ “Y is “ & Y)

  48. Line Indentation • Indent code within a block

  49. Line Indentation For X = 1 to 5 For Y = 1 to 10 If (X > Y) Then If (X > 3) Then Z = Z + 1 Else Z = Z + 2 End If Else If (Y > 3) Then Z = Z + 3 Else Z = Z + 4 End If End If Next Y Next X

  50. Homework

More Related