Class 2

1
Class 2

• 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
Addressing Spreadsheet Cells
Columns
A1 Reference Style
Rows
Cell C2
4
Addressing Spreadsheet Cells
Columns
R1C1 Reference Style
Rows
row, column
Cell (2, 3)
5
VBA Interaction with Excel
Excel
0, 1, 2, 3 numbers
1, 2, 3 numbers
VBA Subroutine
Our focus today
6
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 my_row As
Integer Dim my_sum, my_product As Double
my_sum 0 my_product 1 For my_row 1 To
5 my_sum my_sum Cells(my_row, 2)
my_product my_product Cells(my_row, 2) Next
my_row Cells(6, 1) "sum" Cells(7, 1)
"product" Cells(6, 2) my_sum Cells(7, 2)
my_product End Sub
In VBA editor, enter this program.
9
Example (cont)
Enter five numbers in cells B1 to B5
10
Example (cont)

1. Press AltF8
2. Select
3. 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., ctrlc), VBA
  will overwrite the Windows function with your
  macro.

14
Arrays
Example 1
Default Index starts with zero
Dim my_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 my_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 my_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 my_list(1 To 5) As
Integer Dim my_i As Integer Dim my_sum As
Integer my_sum 0 my_i 1
my_list(1) 1 my_list(2) 4 my_list(3)
9 my_list(4) 16 my_list(5) 25
Do While my_sum lt 16 my_sum my_sum
my_list(my_i) my_i my_i 1 Loop
Cells(15, 1) "sum " Cells(15, 2)
my_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 my_list(1 To 5) As
Integer Dim my_i As Integer Dim my_sum As
Integer my_sum 0 my_i 1
my_list(1) 1 my_list(2) 4 my_list(3)
9 my_list(4) 16 my_list(5) 25
Do While my_sum lt 16 my_sum my_sum
my_list(my_i) my_i my_i 1 Loop
Cells(15, 1) "sum " Cells(15, 2)
my_sum End Sub
19
Boolean Operations
Truth Tables
0 false 1 true
Y A B
result expression1 Or expression2
result expression1 And expression2
result Not expression
20
Example Program
Option Explicit Sub Boolean_test() Dim
my_truth As Boolean Dim my_a, my_b As
Integer my_a 1 my_b 2 my_truth
False If my_a 1 And my_b 2 Then
my_truth True End If Cells(1, 1)
my_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 my_a and my_b.
21
Function Exercise

• The value of p (the ratio of a circles
  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).
• Excels 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.