1 / 54

CSI 1306

CSI 1306. PROGRAMMING IN VISUAL BASIC PART 5. Moving from Lists to Excel. When we did algorithms on Lists, we could treat the full List as one Item Simpler to use L, N vs L1, L2, L3… LN We need to make changes to our algorithms to work within Excel. Moving from Lists to Excel.

dane
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. Moving from Lists to Excel • When we did algorithms on Lists, we could treat the full List as one Item • Simpler to use L, N vs L1, L2, L3… LN • We need to make changes to our algorithms to work within Excel

  3. Moving from Lists to Excel • Where is the Information? • In our List Algorithms, "L, N" did not have a physical place • Excel all information is stored physically on a Worksheet • Change #1 add the line Go to Worksheet ????

  4. Moving from Lists to Excel • Where on the worksheet is the data • When working with Lists, the variable "I" worked well since it is used by mathematicians for series notations. The same is true for subscript notation • Excel works with rows and columns • Usually we will be going down the worksheet, and need to define a variable "Row" • If we are working across a worksheet, we will need to define a variable "Col" • Change # 2 "I" will be converted to either "Row" or "Col"

  5. Moving from Lists to Excel • Lists had a definite size "N" • All Lists started at "1" and finished at "N" • In Excel, data seldom starts in Row 1, due to header rows • Since we design the worksheet, we know which row will be the first, and which row will be the last • Change #3.1 Add an Intermediate variable called "FirstRow" ** Depending on Loop, LastRow may be defined • Change #3.2 Change your loop to say "Loop for each row"

  6. Moving from Lists to Excel • Working with each Element • With Lists we could use one "Get" or "Give" • Get L, N • Give L • With Excel we need to access only one cell at a time • So one "Get" or "Give" becomes many • Moves to INSIDE the Loop • Remember I became Row/Col • Change #4 Get L, N moves inside the Loop to Get LRow

  7. 1. Translation Set 4

  8. Translate 11 • Translate Algorithm 4.4 into Visual Basic, using the Cells A1 - A100 on Worksheet Translate11 • See 15.vb_4b.xls

  9. Algorithm 4.4 • Given a list X of N numbers, find the sum of the numbers in X • Name:SUMLIST • Given: X, N • Change: None • Result: Total • Intermediate: I • Definition • Total := SUMLIST(X,N) Method Get X, N Let Total = 0 Let I = 1 Loop When (I <= N) Let Total = Total + XI Let I = I + 1 Finish Loop Give Total

  10. Algorithm 4.4 Change #1 • Given a list X of N numbers, find the sum of the numbers in X • Name:SUMLIST • Given: X, N • Change: None • Result: Total • Intermediate: I • Definition • Total := SUMLIST(X,N) Method Go to Worksheet "Translate 11" Get X, N Let Total = 0 Let I = 1 Loop When (I <= N) Let Total = Total + XI Let I = I + 1 Finish Loop Give Total

  11. Algorithm 4.4 Change #2 • Given a list X of N numbers, find the sum of the numbers in X • Name:SUMLIST • Given: X, N • Change: None • Result: Total • Intermediate: Row • Definition • Total := SUMLIST(X,N) Method Go to Worksheet "Translate 11" Get X, N Let Total = 0 Let Row = 1 Loop When (Row <= N) Let Total = Total + XRow Let Row = Row + 1 Finish Loop Give Total

  12. Algorithm 4.4 Change #3 • Given a list X of N numbers, find the sum of the numbers in X • Name:SUMLIST • Given: X, N • Change: None • Result: Total • Intermediate: Row • FirstRow LastRow • Definition • Total := SUMLIST(X,N) Method Go to Worksheet "Translate 11" Get X, N Let Total = 0 Let Row = FirstRow Loop For Each Row Let Total = Total + XRow Let Row = Row + 1 Finish Loop Give Total

  13. Algorithm 4.4 Change #4 • Given a list X of N numbers, find the sum of the numbers in X • Name:SUMLIST • Given: X, N • Change: None • Result: Total • Intermediate: Row • FirstRow LastRow • Definition • Total := SUMLIST(X,N) Method Go to Worksheet "Translate 11" Get X,N Let Total = 0 Let Row = FirstRow Loop For Each Row Get XRow Let Total = Total + XRow Let Row = Row + 1 Finish Loop Give Total

  14. Name:SUMLIST • Given: X • Change: None • Result: Total • Intermediate: Row • FirstRow LastRow • Definition • Total := SUMLIST(X) Option Explicit 'Written By T. James Sub SumList() Dim X as Integer Dim Total as Integer Dim Row as Integer Const FirstRow = 1 Const LastRow = 100 Worksheets("Translate11").Activate Total = 0 For Row = FirstRow to LastRow X = Cells (Row, 1) 'A Col Total = Total + X Next Row MsgBox("Total is " & Total) End Sub Method Go to Worksheet "Translate 11" Let Total = 0 Let Row = FirstRow Loop For Each Row Get XRow Let Total = Total + XRow Let Row = Row + 1 Finish Loop Give Total

  15. Translate 12-15 Worksheet Name is Marks, use above info for 12-15

  16. Translate 12 • Use Algorithm 4.5 to search for a Student’s Name • See 15.vb4b.xls

  17. Name: SEARCHX Given: X, N, V Change: None Result: Found Intermediate: I Definition: Found := SEARCHX(X,N,V) Method Get X, N Get V Let Found = False Let I = 1 Loop If (XI = V) Let Found = True Else Let I = I + 1 Finish Loop When (I >N) or (Found) Give Found Name: SEARCHX Given: X, V Change: None Result: Found Intermediate: Row FirstRow, LastRow Definition: Found := SEARCHX(X,V) Method Go to Worksheet "Marks" Get V Let Found = False Let Row = FirstRow Loop Get XRow If (XRow = V) Let Found = True Else Let I = I + 1 Finish Loop When (Row >LastRow) or (Found) Give Found

  18. Name: SEARCHX Given: X, V Change: None Result: Found Intermediate: Row FirstRow, LastRow Definition: Found := SEARCHX(X,V) Method Go to Worksheet "Marks" Get V Let Found = False Let Row = FirstRow Loop Get XRow If (XRow = V) Let Found = True Else Let I = I + 1 Finish Loop When (Row >LastRow) or (Found) Give Found Sub SearchX() Dim V as String Dim X as String Dim Row as Integer Dim Found as Boolean Const FirstRow = 2 Const LastRow = 20 Worksheets("Marks").Activate V = InputBox("Enter Name") Row = FirstRow Found = False Do X = Cells(Row, 1) 'Col A If (X = V) Then Found = True Else Row = Row + 1 EndIf Loop Until (Row>LastRow) Or (Found) If (Found) Then MsgBox(V & " is a Student"&Chr(13)&_ "Found in Row "& Row) Else MsgBox(V & " is not a Student" End If End Sub

  19. Translate 13 • Use Algorithm 4.6 to find the highest final exam mark • See 15.vb_4b.xls

  20. Name: MAXLIST Given: X, N Change:None Result: Max, Loc Intermediate: I Definition: (Loc,Max) := MAXLIST(X,N) Method Get X, N Let Max = -1 Let I = 1 Loop When (I <= N) If (XI > Max) Let Max = XI Let Loc = I Let I = I + 1 Finish Loop Give Max Give Loc Name: MAXLIST Given: Final Change:None Result: Max, Loc Intermediate: Row FirstRow, LastRow Definition: (Loc,Max) := MAXLIST(X) Method Go to Worksheet "Marks" Let Max = -1 Let Row = FirstRow Loop For each Row Get FinalRow If (FinalRow > Max) Let Max = FinalRow Let Loc = Row Let Row = Row + 1 Finish Loop Give Max Give Loc

  21. Name: MAXLIST Given: Final Change:None Result: Max, Loc Intermediate: Row FirstRow, LastRow Definition: (Loc,Max) := MAXLIST(X) Method Go to Worksheet "Marks" Let Max = -1 Let Row = FirstRow Loop For each Row Get FinalRow If (FinalRow > Max) Let Max = FinalRow Let Loc = Row Let Row = Row + 1 Finish Loop Give Max Give Loc Option Explicit 'Written by T. James Sub MaxList() Dim Final as Single Dim Max as Single Dim Loc as Integer Dim Row as Integer Const FirstRow = 2 Const LastRow = 20 Worksheets("Marks").Activate Max = -1 For Row = FirstRow to LastRow Final = Cells(Row, 6) 'Col F If (Final > Max) Then Max = Final Loc = Row EndIf Next Row MsgBox (Max & " Is the Max Final"&Chr(13)& _ "Found in Row "& Loc) End Sub

  22. Translate 14 • Use Algorithm 4.7 to find out how many students did not hand in Assignment 3 • See 15.vb_4b.xls

  23. Name: NUM0 Given: X, N Change: None Result: Count Intermediate: I Definition: Count := NUM0(X,N) Method Get X, N Let Count = 0 Let I = 1 Loop When (I <= N) If (XI = 0) Let Count = Count + 1 Let I = I + 1 Finish Loop Give Count Name: NUM0 Given: A3 Change: None Result: Count Intermediate: Row FirstRow, LastRow Definition: Count := NUM0(X) Method Go to Worksheet "Marks" Let Count = 0 Let Row = FirstRow Loop When (Row <= LastRow) If (A3Rowis Blank) Let Count = Count + 1 Let Row = Row + 1 Finish Loop Give Count

  24. Name: NUM0 Given: A3 Change: None Result: Count Intermediate: Row FirstRow, LastRow Definition: Count := NUM0(X) Method Go to Worksheet "Marks" Let Count = 0 Let Row = FirstRow Loop When (Row <= LastRow) If (A3Row is Blank) Let Count = Count + 1 Let Row = Row + 1 Finish Loop Give Count Option Explicit 'Written by T. James Sub NUM0() Dim A3 as Single Dim Count as Integer Dim Row as Integer Const FirstRow = 2 Const LastRow = 20 Worksheets("Marks").Activate Count = 0 Row = FirstRow Do While (Row <= LastRow) If (IsEmpty(Cells(Row,4))) Then Count = Count + 1 EndIf Row = Row + 1 Loop MsgBox(Count & " Missing A3s") EndSub

  25. Translate 15 • Use Algorithm 4.8 to find out if two final exams have the same mark • See 15.vb_4b.xls

  26. Name: DUPLICATE Given: X, N Change: None Result: Double Intermediate: I, Test Definition: Double := DUPLICATE(X,N) Method Get X, N Let Double = False Let Test = 1 Loop Let I = Test + 1 Loop If (XTest = XI) Let Double = True Else Let I = I + 1 Finish Loop When (I >N) or (Double) If (Not (Double)) Let Test = Test + 1 Finish Loop When (Test = N) or (Double) Give Double Name: DUPLICATE Given: Final, FinalT Change: None Result: Double Intermediate: Row, TestRow FirstRow, LastRow Definition: Double := DUPLICATE(X) Method Go to Worksheet "Marks" Let Double = False Let TestRow = FirstRow Loop Get FinalTestRow Let Row = Test Row + 1 Loop Get FinalRow If (FinalTestRow = FinalRow) Let Double = True Else Let Row = Row + 1 Finish Loop When (Row >LastRow) or (Double) If (Not (Double)) Let TestRow = TestRow + 1 Finish Loop When (TestRow = LastRow) or (Double) Give Double

  27. Name: DUPLICATE Given: Final, FinalT Change: None Result: Double Intermediate: Row, TestRow FirstRow, LastRow Definition: Double := DUPLICATE(X) Method Go to Worksheet "Marks" Let Double = False Let TestRow = FirstRow Loop Get FinalTestRow Let Row = Test Row + 1 Loop Get FinalRow If (FinalTestRow = FinalRow) Let Double = True Else Let Row = Row + 1 Finish Loop When (Row >LastRow) or (Double) If (Not (Double)) Let TestRow = TestRow + 1 Finish Loop When (TestRow = LastRow) or (Double) Give Double Sub Duplicate() Dim Final as Single Dim FinalT as Single Dim Dbl as Boolean Dim Row as Integer Dim TestRow as Integer Const FirstRow = 2 Const LastRow = 20 Worksheets("Marks").Activate Dbl = False TestRow = FirstRow Do FinalT = Cells(TestRow, 6) Row = TestRow + 1 Do Final = Cells(Row, 6) If (FinalT = Final) Then Dbl = True Else Row = Row + 1 EndIf Loop Until(Row > LastRow) or (Dbl) If Not(Dbl) Then TestRow = TestRow +1 EndIf Loop Until (TestRow = LastRow) or (Dbl) If (Dbl) Then MsgBox("Double "&TestRow&" "&Row) Else MsgBox("No Doubles") EndIf End Sub

  28. Translate 16 • Translate algorithm 4.9 into Visual Basic using the worksheet CSI1234, where Name is in Column A, Midterm in Column B, Final in Column C, and Grade is to go into Column D. The students marks start in Row 3 and continue for any number of rows

  29. Translate 16 • In the algorithm we can continue to use "Loop for each row" • In Visual basic, we need to replace the concept of "LastRow" • First translate as before

  30. Name: FINDGRADE Given: M, F, N Change: None Result: G Intermediate: I Definition: G := FINDGRADE(M,F,N) Method Get M, F, N Let I = 1 Loop When (I <= N) Let GI = 0.75*FI + 0.25*MI Let I = I + 1 Finish Loop Give G Name: FINDGRADE Given: M, F Change: None Result: G Intermediate: Row FirstRow, LastRow Definition: G := FINDGRADE(M,F) Method Go to Worksheet "CSI1234" Let Row = FirstRow Loop For each Row Get FRow, MRow Let GRow = 0.75*FRow + 0.25*MRow Give Grow Let Row = Row + 1 Finish Loop

  31. Translation 16 • Lets translate in the same style as we did Algorithm 4.7 (Assuming Rows 3-17) • Const FirstRow = 3 • Const LastRow = 17 • Row = FirstRow • Do While (Row <= LastRow) • Row = Row + 1 • Loop

  32. Name: FINDGRADE Given: M, F Change: None Result: G Intermediate: Row FirstRow, LastRow Definition: G := FINDGRADE(M,F) Method Go to Worksheet "CSI1234" Let Row = FirstRow Loop For each Row Get FRow, MRow Let GRow = 0.75*FRow + 0.25*MRow Give GRow Let Row = Row + 1 Finish Loop Option Explicit 'Written By T. James 'Example of Get and Give from Excel Sub FindGrade() Dim M as Single Dim F as Single Dim G as Single Dim Row as Integer Const FirstRow = 3 Const LastRow = 17 Worksheets("CSI1301").Activate Row = FirstRow Do While (Row <= LastRow) M = Cells(Row, 2) 'Get Col B F = Cells(Row, 3) 'Get Col C G = 0.75*F + 0.25*M Cells(Row, 4) = G 'Give Col D Row = Row + 1 Loop End Sub

  33. Translation 16 • That will work for a definite loop, but "any number of students" is indefinite • KEY FIELD • We will learn more about Key Fields when we look at databases • Key Field Must be present • A student may not have a midterm, or final, but must have a Name N • Name is a keyfield • From the Translation of 4.7 • We can test for a blank cell • Loop Until Name is blank • Do Until there is no more names • Do Until (IsEmpty(Cells(Row,???)))

  34. Name: FINDGRADE Given: M, F Change: None Result: G Intermediate: Row FirstRow, LastRow Definition: G := FINDGRADE(M,F) Method Go to Worksheet "CSI1234" Let Row = FirstRow Loop For each Row Get FRow, MRow Let GRow = 0.75*FRow + 0.25*MRow Give Grow Let Row = Row + 1 Finish Loop Option Explicit 'Written By T. James 'Example of Get and Give from Excel Sub FindGrade() Dim M as Single Dim F as Single Dim G as Single Dim Row as Integer Const FirstRow = 3 Const LastRow = 17 Worksheets("CSI1301").Activate Row = FirstRow Do Until (IsEmpty(Cells(Row,1))) M = Cells(Row, 2) 'Get Col B F = Cells(Row, 3) 'Get Col C G = 0.75*F + 0.25*M Cells(Row, 4) = G 'Give Col D Row = Row + 1 Loop End Sub

  35. Translate 17 • Using the same information as Translation 16, translate Algorithm 4.10 into Visual Basic. • See 15.vb_4b.xls

  36. Option Explicit 'Written By T. James Sub Worst() Dim G as Single Dim Lname as String Dim Loc as Integer Dim Min as Single Dim Row as Integer Const FirstRow = 3 Worksheets("CSI1234").Activate Min = 101 Row = FirstRow Do Until (IsEmpty(Cells(Row,1))) G = Cells(Row, 4) If (G < Min) Then Min = G Loc = Row End If Row = Row + 1 Loop Lname = Cells(Loc, 1) MsgBox(Lname & " Had Worst") End Sub • Name: WORST • Given: Name,G • Change: None • Result: LName • Intermediate: • Loc, Min,Row, FirstRow • Definition • Lname :=WORST(Name,G) Method Goto Worksheet CSI1234 Min = 101 Row = FirstRow Loop Until Empty row Get GRow If (GRow < Min) Min = GRow Loc = Row Row = Row +1 Finish Loop LName = NameLoc Give LName

  37. Additional Material

  38. 2. More Algorithms

  39. Algorithm 4.11 • Write an algorithm that transposes the digits of a two digit number (e.g. 21 to 12) • Name: FLIP • Given: N • Change: None • Results: M • Intermediate: T, O • Definition • M := FLIP (N) Method Get N Let T = N div 10 Let O = N mod 10 Let M = O * 10 + T Give M

  40. M:=Flip(N)

  41. Trace 4.11 • Trace algorithm 4.11 with the value 85 Method (1) Get N (2) Let T = N \ 10 (3) Let O = N mod 10 (4) Let M = O * 10 + T (5) Give M LN N M T O 1 85 2 8 3 5 4 58 5 Output 58

  42. Algorithm 4.12 • Using a loop, write an algorithm to count the number of occurrences of the maximum value in a list L of size N • Name: CMAX • Given: L, N • Change: None • Results: NMax • Intermediate: I, Max • Definition • Nmax := CMAX(L,N) Method Get L, N Let Max = - 9999 Let I = 1 Loop When (I <= N) If (LI > Max) Let Max = LI Let Nmax = 1 Else If (LI = Max) Let Nmax = Nmax + 1 Let I = I + 1 Finish Loop Give Nmax

  43. Nmax := CMax(L,N) See 15.vb_4b.xls

  44. Trace 4.12 LN L N Max I Nmax Test 1 (2,8,3,8) 4 2 -9999 3 1 4 (1<=4) 5 (2>-9999) 6 2 7 1 10 2 4 (2<=4) 5 (8>2) 6 8 7 1 10 3 4 (3<=4) 5 (3>8) 8 (3=8) 10 4 4 (4<=4) 5 (8>8) 8 (8=8) 9 2 10 5 4 (5<=4) 12 Output 2 Trace algorithm 4.12 with the list (2, 8, 3, 8) Method (1) Get L, N (2) Let Max = - 9999 (3) Let I = 1 (4) Loop When (I <= N) (5) If (LI > Max) (6) Let Max = LI (7) Let Nmax = 1 (8) Else If (LI = Max) (9) Let Nmax = Nmax + 1 (10) Let I = I + 1 (11) Finish Loop (12) Give Nmax

  45. DEBUGGING PROGRAMS Repeat

  46. Visual Basic Programming • Programming the Solution to a Problem • Code the Program • Translate the instructions in the algorithm to Visual Basic instructions • Desk check the program • Enter the Code into the Computer • Test the Program • Using the computer • Maintain the Program • Fix bugs as they arise • Add enhancements

  47. Debugging Programs • Step 1 • Check the translation from your algorithm to Visual Basic • Step 2 • Recheck the logic in your algorithm • Step 3 • Use the debug facilities of Visual Basic

  48. Debugging Programs • To debug a program is to identify and correct errors in your program • Usually, these are semantic (logic) or runtime errors. You will already have corrected most syntax errors when entering the code • Add Watch capability • Shows the current value of variables and expressions as the program executes

  49. Debugging Programs • Step Into your code • Runs the next executable line of code. If the code calls another program, your view of the code shifts to the called program until it ends • Step Over also runs the next executable line of code. However, if the code calls another program, the entire called program is run so that your view of the code is never shifted from the calling program • Using the watch and step into debug capabilities is analogous to tracing

  50. Debugging Programs • For programs with many lines of code, where you might not want to step through each line of executable code, you can set breakpoints • Toggle Breakpoint • Creates or removes a breakpoint, a location in the code where Visual Basic halts execution • Quick Watch • During break mode, lets you check the value of a variable or expression for which you have not defined a watch

More Related