Class 13'1

1
Class 13.1

• Programming in Visual Basic

2
Class Objectives

• Recognize when programming is required to solve a
  problem
• Understand basic structure of a program
• Understand function vs. subroutine
• Know how to create a program
• Know rules for naming variables
• Know precedence of arithmetic and comparison
  operators
• Understand integer vs real number

3
Class Objectives (cont)

• Understand comments
• Know what to do if a line is too long
• Know how to protect against spelling errors
• Selection structures If, If-Else, If-ElseIf
• Repetition structures Do Until, For

4
What is a program?
Program An algorithm implemented within a
computer.
5
When do you use a program?

• Spreadsheets can perform about 95 of the
  computing tasks however, they are not suitable
  for the following
• Highly repetitive tasks
• Decision making

6
What are some programming languages?

• Basic
• Cobal
• Fortran
• Pascal
• Ada
• Java
• C
• LISP

We will use Visual Basic for Applications (VBA).
7
What is Visual Basic for Applications (VBA)?
It is a version of Visual Basic that is embedded
within Excel.
8
Why are we learning VBA, rather than another
programming language?

• Simple, but powerful
• Easy to learn
• Quickly learned
• Capitalizes on your knowledge of Excel

9
VBA Interaction with Excel
Excel
0, 1, 2, 3 numbers
1, 2, 3 numbers
VBA Subroutine
Our first focus
10
Sample Function
Function name
Identifies program as a function
Numbers passed into function through arguments
Function my_adder(my_a, my_b) my_adder my_a
my_b End Function
Action taken by the function
Identifies the end of the function
The single number is returned through the
function name
11
Creating a VBA program
12
Step 1 Open Excel
13
Step 2 Save as
Save as Example_1.xls
14
Step 3 Open VBA Editor
Press Alt F11
15
Step 4 Identify where to file your program
Left-click here
16
Step 5 Insert module
Left click Insert
Left click Module
17
A blank form will appear
This is where the program goes
18
Step 6 Type in program
Here is the program
19
Use your program
20
Step 1 Go to Excel spreadsheet
Left click Excel button
21
Step 2 Enter numbers into cells
Enter numbers 4 8
22
Step 3 Call the function
my_adder(A1,B1)
23
What happened?
my_adder(A1,B1)
Function my_adder(my_a, my_b) my_adder my_a
my_b End Function
24
Rules for naming variables

• Must begin with letter
• Use only letters, numbers, and underscores
• Do not use space, period (.), exclamation mark
  (!), or the characters _at_, , ,
• Do not exceed 255 characters in length
• Avoid names already used by Excel and VBA
• Start each variable with my_

25
Operator Precedence in Formulas

• Parenthesis
• Exponentiation ()
• 3. Negation ()
• 4. Multiplication and division (, /)
• 5. Addition and subtraction (, )

My_answer (5 723)/4 1
Evaluates as
My_answer (5 493)/4 1 My_answer (5
147)/4 1 My_answer (152)/4 1 My_answer 38
1 My_answer 37
26
Odd features of computer formulas
In algebra, either of the following two formulas
has meaning y x 3 or x 3
y In computers, only the first formula is valid.
27
Integers and Real Numbers
Integer Any positive or negative counting
number, or zero. Examples 4, 7, 0 Real
Number Any rational or irrational
number Examples 0.75, 3.14159, 0.5
Note Rational number can be expressed as m/n
where m and n are integers and n is not zero
Irrational number cannot be expressed as m/n
where m and n are integers and n is not zero
28
Declaring Variables
When introducing a new variable into a computer
program, you should tell the computer what kind
it is. Some common options
Increasing memory requirements

• Integer
• Single single-precision real number
• Double double-precision real number

29
Modify you program as follows
Declared as integers
Function my_adder(my_a As Integer, my_b As
Integer) Dim my_c As Integer my_c 5
my_adder my_a my_b my_c End Function
What happens when you introduce real numbers into
the Excel cells?
30
Comments
Comments can be added to help humans understand
the program. Comments are ignored by the
computer.
Indentation is also ignored my computer, but
helps humans to understand.
31
What if a line is too long?
Example My_var 1 2 3 4 _
5 6 7 8 Is evaluated as
My_var 1 2 3 4 5 6 7 8
The underscore joins lines together.
32
Types of Errors

• Syntax errors simple errors that do not follow
  the required format
• Compiler errors errors that occur when the
  computer program is converted to machine code
• Run-time errors a program may compile fine,
  but may fail during a run
• Logic errors a program may run fine, but have
  mistakes in logic that give incorrect answers
• Spelling error if you misspell a variable, the
  computer interprets it as an additional variable
  and likely will give incorrect answers

33
How to protect against spelling errors
This statement requires that all variables be
declared
Option Explicit Function my_adder(my_a As
Integer, my_b As Integer) 'This program adds two
numbers together and adds 5 Dim my_c As
Integer my_c 5 my_adder my_a my_b
my_c End Function

• Do the following
• Add Option Explicit to your program
• Misspell my_c 5 as my_d 5
• Go to the Excel spreadsheet and try to use
  my_adder

34
Your function failed to run and you got an error
message.

• How to recover
• Press OK in the error message
• Correct the spelling
• Press F5

It is good programming practice to use Option
Explicit so that spelling errors are caught.
35
Comparison Operators
Evaluated in this order of precedence
36
Selection If
Function my_comparison(my_a, my_b)
my_comparison 0 If my_a gt my_b Then
my_comparison 1 End If End Function
Enter the above program and test it in Excel.
(Note Option Explicit can appear only once on a
sheet, so it is omitted here.)
37
Selection If-Else
Function my_comparison(my_a, my_b) If my_a gt
my_b Then my_comparison 1 Else
my_comparison 2 End If End Function
Y
?
N
Calc B
Enter the above program and test it in Excel.
(Note Option Explicit can appear only once on a
sheet, so it is omitted here.)
38
Selection If-ElseIf
Y
?
Function my_comparison(my_a, my_b) If my_a gt
my_b Then my_comparison 1 ElseIf
my_a my_b Then my_comparison 2
Else my_comparison 3 End If End
Function
N
Y
?
N
Enter the above program and test it in Excel.
(Note Option Explicit can appear only once on a
sheet, so it is omitted here.)
39
Repetition Do Until
Input
Function my_do_until_loop(my_a As Integer)
Dim my_i As Integer Dim my_j As Integer
my_i 1 my_j 1 Do Until my_i my_a
my_i my_i 1 my_j my_j 2
Loop my_do_until_loop my_j End Function
Title Declare variables Initialize
variables Calculate Output
Enter the above program and test it in Excel.
40
Repetition Do Until
As a flowchart
Function my_do_until_loop(my_a As Integer)
Dim my_i As Integer Dim my_j As Integer
my_i 1 my_j 1 Do Until my_i my_a
my_i my_i 1 my_j my_j 2
Loop my_do_until_loop my_j End Function
Y
41
Repetition For
Function my_for_loop(my_a As Integer) Dim
my_i As Integer Dim my_j As Integer my_j
1 For my_i 1 To my_a my_j my_j
2 Next my_i my_for_loop my_j End
Function
As a flowchart
Default Counts by 1
Y
Enter the above program and test it in Excel.
Note The code is shorter, but the flowchart is
the same.
42
Repetition For
Function my_for_loop(my_a As Integer) Dim
my_i As Integer Dim my_j As Integer my_j
1 For my_i my_a To 1 Step -1 my_j
my_j 2 Next my_i my_for_loop
my_j End Function
As a flowchart
my_i my_a my_j 1
Overrides default Counts by -1
Y
N
Enter the above program and test it in Excel.
my_i my_i 1 my_j my_j 2