Transcript Class 13.1 - Texas A&M University

Class 2
Programming in
Visual Basic
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
Class Objectives (con’t)
Understand comments
 Know what to do if a line is too long
 Know how to protect against spelling
errors
 Selection structures: If, If-Else, IfElseIf
 Repetition structures: Do Until, For

What is a program?
Program – An algorithm implemented
within a computer.
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
What are some
programming languages?
•Basic
•Cobal
•Fortran
•Pascal
•Ada
•Java
•C
•LISP
We will use Visual
Basic for Applications
(VBA). MatLab?
What is Visual Basic for
Applications (VBA)?
It is a version of Visual Basic
that is embedded within
Excel.
Why are we learning VBA,
rather than another
programming language?
• Simple
• Easy to learn
• Quickly learned
• Capitalizes on your knowledge of Excel
VBA Interaction with Excel
Excel
0, 1, 2, 3 …
numbers
1
number
VBA Function
Excel
0, 1, 2, 3 …
numbers
1, 2, 3 …
numbers
VBA Subroutine
Our first focus
Sample Function
Identifies program as a
function
Function name
Function adder(
adder
=
End Function
a
a,
+
Numbers passed into
function through
arguments
b)
b
Action taken
by the function
Identifies the end of the function
The single number is returned through the function name
Creating a
VBA program
Step 1 – Open Excel
Step 2 – Save as…
Save as …
Example_1
Step 3 – Open VBA Editor
Press…
Alt F11
Step 4 – Identify where to file your
program
Left-click
here
Step 5 – Insert module
Left click
Insert
Left click
Module
A blank form will appear
This is where the program goes
Step 6 – Type in program
Here is the program
Use your
program
Step 1 – Go to Excel spreadsheet
Left click
Excel button
Step 2 – Enter numbers into cells
Enter numbers
4&8
Step 3 – Call the function
= adder(A1,B1)
What happened?
= adder(A1,B1)
1
A
B
C
4
8
12
2
Function
adder( a,
adder =
End Function
a
+
b)
b
Rules for naming
variables





Must begin with letter
Use only letters, numbers, and underscores
Do not use space, period (.), exclamation
mark (!), or the characters @, &, $, #
Do not exceed 255 characters in length
Avoid names already used by Excel and
VBA
Operator Precedence in
Formulas
1.
2.
3.
4.
5.
Parenthesis
Negation (–)
Exponentiation (^)
Multiplication and division (*, /)
Addition and subtraction (+, –)
answer = (5 + 7^2*3)/4 – 1
Evaluates as…
answer = (5 + 49*3)/4 – 1
answer = (5 + 147)/4 – 1
answer = (152)/4 – 1
answer = 38 – 1
answer = 37
Odd features of computer
formulas…
In algebra, the following formula has no solution…
A = A +3
In computers, the above formula means …
Take the current value of A, add 3 to it, and make the result
the new value of A.
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.
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: 4.0, 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
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: 4.0, 0.75, 3.14159, – 0.5
“Floating point” numbers
are harder to represent in
a computer
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
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
Modify you program as
follows…
Declared as integers
Function adder( a As Integer,
Dim c As Integer
c=5
adder = a + b + c
End Function
b As Integer)
What happens when you introduce real
numbers into the Excel cells?
Comments
Comments can be added to help humans understand the
program. Comments are ignored by the computer.
Function adder(a As Integer, b As Integer)
‘ This program adds two numbers together and adds 5
Dim c As Integer
c=5
adder = a + b + c
End Function
The apostrophe signals the computer to ignore what follows
Indentation is also ignored my computer, but helps humans to
understand.
What if a line is too long?
Example…
The underscore joins lines together.
var = 1 + 2 + 3 + 4 + _
5+6+7+8
Is evaluated as
var = 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8
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
How to protect against
spelling errors
This statement requires that all variables be declared
Option Explicit
Function adder(a As Integer, b As Integer)
'This program adds two numbers together and adds 5
Dim c As Integer
c=5
adder = a + b + c
End Function
Do the following:
1. Add Option Explicit to your program
2. Misspell c = 5 as d = 5
3. Go to the Excel spreadsheet and try to use adder
Your function failed to run
and you got an error
message.
How to recover…
1. Press OK in the error message
2. Correct the spelling
3. Press F5
It is good programming practice to use
Option Explicit so that spelling errors are
caught.
Comparison Operators
Evaluated in
this order of
precedence
1. Equality (=)
2. Inequality (<>)
3. Less than (<)
4. Greater than (>)
5. Less than or equal to (<=)
6. Greater than or equal to (>=)
Selection: If
?
Y
N
Calc A
Function comparison(a, b)
comparison = 0
If a > b Then
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.)
Selection: If-Else
?
Y
N
Calc B
Calc A
Function comparison(a, b)
If a > b Then
comparison = 1
Else
comparison = 2
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.)
Selection: If-ElseIf
?
Y
N
Calc A
?
Y
N
Calc C
Calc B
Function comparison(a, b)
If a > b Then
comparison = 1
ElseIf a = b Then
comparison = 2
Else
comparison = 3
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.)
Repetition: Do While
Title
Declare variables
Initialize variables
Calculate
Output
Input
Function do_while_loop(a As Integer)
Dim i As Integer
Dim j As Integer
i=1
j=1
Do While i < a
i=i+1
j=j*2
Loop
do_while_loop = j
End Function
Enter the above program and test it in Excel.
Repetition: Do While
As a flowchart…
i=1
j=1
i < a?
Y
i = i +1
j = j *2
N
Function do_while_loop(a As Integer)
Dim i As Integer
Dim j As Integer
i=1
j=1
Do While i < a
i=i+1
j=j*2
Loop
do_while_loop = j
End Function
Repetition: For
As a flowchart…
i=1
j=1
i > a?
Y
Function for_loop(a As Integer)
Dim i As Integer
Dim j As Integer
j=1
For i = 1 To a
j=j*2
Default:
Next i
Counts by +1
for_loop = j
End Function
N
i = i +1
j = j *2
Enter the above program and test it in
Excel.
Note: The code is shorter, but the
flowchart is the same.
Repetition: For
As a flowchart…
i=a
j=1
Y
i < 1?
N
i=i–1
j = j *2
Function for_loop(a As Integer)
Dim i As Integer
Dim j As Integer
j=1
For i = a To 1 Step -1
j=j*2
Next i
Overrides default:
for_loop = j
Counts by -1
End Function
Enter the above program and test it in
Excel.
Bring
Math Supplement
to next class.