1 / 22

Class 3

Class 3. Programming in Visual Basic. Class Objectives. Learn about input/output Learn about strings Learn about subroutines Learn about arrays Learn about Boolean operations. A B C D 1 2 45 3 4.

reia
Download Presentation

Class 3

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. Class 3 Programming in Visual Basic

  2. Class Objectives • Learn about input/output • Learn about strings • Learn about subroutines • Learn about arrays • Learn about Boolean operations

  3. A B C D 1 2 45 3 4 Addressing Spreadsheet Cells Columns A1 Reference Style Rows Cell C2

  4. A B C D 1 2 45 3 4 Addressing Spreadsheet Cells Columns R1C1 Reference Style Rows row, column Cell (2, 3)

  5. Excel 0, 1, 2, 3 … numbers 1 number VBA Function VBA Interaction with Excel Excel 0, 1, 2, 3 … numbers 1, 2, 3 … numbers VBA Subroutine Our focus today

  6. Using the VBA editor, enter the following program… Name Subroutine Sub string_demo() Cells(10, 1) = "First" Cells(10, 2) = "Second" Cells(10, 3) = "Third"End Sub While in VBA editor, press F5 to run Strings No arguments passed Strings in double quotes

  7. Results

  8. Sample Subroutine with Calculation Subroutine name Comment Declare variables Initialize variables Start loop Input and calculate End loop Output End Sub sum_and_product_of_five_numbers() 'This program gives the sum and product of 'five numbers located in Cells B1 to B5 Dim row As Integer Dim sum, product As Double sum = 0 product = 1 For row = 1 To 5 sum = sum + Cells( row, 2) product = product * Cells( row, 2) Next row Cells(6, 1) = "sum" Cells(7, 1) = "product" Cells(6, 2) = sum Cells(7, 2) = product End Sub In VBA editor, enter this program.

  9. Example (con’t) Enter five numbers in cells B1 to B5

  10. Example (con’t) • Press Alt+F8 • Select • Press Run

  11. Result

  12. An easier way to execute a subroutine • You can link a subroutine to a keyboard control character. • Assignment is made under Options in the Macro dialog box.

  13. Using Macro Options • Under Options, fill in a letter next to “ctrl+” and the selected macro can be executed by pressing that key combination. • Be careful. If you choose a character that is a standard Windows function (e.g., ctrl+c), VBA will overwrite the Windows function with your macro.

  14. Arrays Example 1 Default: Index starts with zero Dim list (5) as integer 0 4 1 7 2 89 3 8 4 45 5 -3 Numbers stored in array Indices

  15. Arrays Example 2 Override: Index starts with one Dim list (1 to 5) as integer 1 7 2 89 3 8 4 45 5 -3 Numbers stored in array Indices

  16. Arrays Example 3 Column Indices Dim list (1 to 5, 1 to 2) as integer 1 2 1 7 32 2 89 9 3 8 46 4 45 -8 5 -3 8 Row Indices This is a two-dimensional array. Up to 60 dimensions are allowed.

  17. Sample Program Sub array_demo() Dim list(1 To 5) As Integer Dim i As Integer Dim sum As Integer sum = 0 i = 1 list(1) = 1 list(2) = 4 list(3) = 9 list(4) = 16 list(5) = 25 Do While sum < 16 sum = sum + list( i) i = i + 1 Loop Cells(15, 1) = "sum =" Cells(15, 2) = sum End Sub Title Declare variables Initialize variables Start loop Calculate End loop Output End Enter and run this program.

  18. Results Sub array_demo() Dim list(1 To 5) As Integer Dim i As Integer Dim sum As Integer sum = 0 i = 1 list(1) = 1 list(2) = 4 list(3) = 9 list(4) = 16 list(5) = 25 Do While sum < 16 sum = sum + list( i) i = i + 1 Loop Cells(15, 1) = "sum =" Cells(15, 2) = sum End Sub

  19. A Y 0 1 1 0 Not A B Y 0 0 0 0 1 0 • 0 0 1 1 1 A B Y 0 0 0 0 1 1 • 0 1 1 1 1 And Or Truth Tables Boolean Operations 0 = false 1 = true Y A B result=expression1Orexpression2 result=expression1Andexpression2 result=Notexpression

  20. Example Program Option Explicit Sub Boolean_test() Dim truth As Boolean Dim a, b As Integer a = 1 b = 2 truth = False If a = 1 And b = 2 Then truth = True End If Cells(1, 1) = truth End Sub Title Declare variables Initialize Boolean operation Assign Output End Enter and run this program. To see what happens, play with various Boolean operators, and different initial values of a and b.

  21. Function Exercise • The value of p (the ratio of a circle’s circumference to its diameter) has been estimated for about 4000 years. • Several methods for calculating p are presented in the Math Supplement (pages 8-10). • Excel’s built in function for p is only accurate to 14 decimal places (van Rooman did better in 1593). • As a team, pick ~4 (1 per team member) of the various methods used to estimate p using infinite series (avoid Methods a, c, e and k). • Program your function to calculate the first 50 terms, or stop when the value does not change by more than 10-6.

  22. Subroutine Exercise • Convert your function into a subroutine. • The subroutine should title Column A as p and Column B as D, that is, the change in p from the previous iteration. • Then beginning in Cells A2 and B2 and going down, output the successive terms of p. • Stop the iteration after the first 200 terms, or if an additional term does not change the value of p by more than 10-10. • For each method selected by your teammates, compare the rate that they converge to an accurate value of p.

More Related