259 Lecture 9 Spring 2013

carenextSoftware and s/w Development

Nov 18, 2013 (3 years and 9 months ago)

94 views

259
Lecture 9
Spring 2013

Advanced Excel Topics


Visual Basic
for Applications (VBA)

VBA Topics


Developer Tab


Form Controls


User Defined Functions


VBA Editor


Comments


Variable Data Types


Local Variables


Variable Assignment

2

Form Controls


Form controls
consist of items seen in
windows programs and dialog boxes
such as buttons, scroll bars, spinners,
list boxes, check boxes, radio
buttons, …


In order to be able to add form
control items, the “developer tab”
MUST be added.

3

Adding the Developer Tab to
the Ribbon


Windows:

File,
Options,
Customize
Ribbon, Check
“Developer”


Mac:

Excel,
Preferences,
Ribbon, Check
“Developer”

4

Add a List Box Control


Example 1:

Create
a worksheet that will
allow the user to
select an “Employee
Name” from a “list
box” and return the
corresponding
“Employee Number”.
Also highlight in
ORANGE, the
selected “Employee
Name” and
“Employee Number”.

5


Add a List Box Control (cont.)


Step 1:
Create
an empty “List
Box” .


Developer, Insert
(Icon), Form
Controls, List Box,
then Drag a
Rectangle



6


Add a List Box Control (cont.)


Step 2:

Populate the
“List Box” with the input
items, and link the
output of the “List Box”
to a cell reference.


Right
-
Click, Format
Control, Control, …


Input range: A1:A5


Cell Link: C1


Test the bi
-
directional
link between the “List
Box” and cell C1. Notice
how manually changing
cell C1 also updates the
“List Box” .


7


Add a List Box Control (cont.)


Step 3:

Add a
formula to cell C2 to
get the “Employee
Number”


=INDEX(B1:B5,C1,1)


8


Add a List Box Control (cont.)


Step 4:

Add the
conditional formatting to
highlight in ORANGE, the
selected “Employee
Name” and “Employee
Number”.


Select cell A1


Home, Styles, Conditional
Formatting, Manage
Rules, New Rule,
Use a
formula to determine
which cells to format


=ROW()=$C$1


Use the “Format Painter”
to apply the Conditional
Format to the other cells.


9


Add a Scroll Bar Control


Example 2:

Create a worksheet that
will decode a one word “Caesar
Cipher”. Allow the user to select the
“shift” value by using a “Scroll Bar”.


10

Add a Scroll Bar Control (cont.)


Step 1:
Create a “Scroll Bar”


Developer, Insert (Icon), Form Controls,
Scroll Bar, then Drag a Rectangle


11

Add a Scroll Bar Control (cont.)


Step 2:

Set the
properties for the “Scroll
Bar”, and link the output
of “Scroll Bar” to a cell
reference.


Right
-
Click, Format
Control, Control, …


Minimum Value: 0 ;
Maximum Value: 25 ;
Incremental Value: 1 ;
Cell Link: O1


Test the bi
-
directional
link between the “Scroll
Bar” and cell O1. Notice
how manually changing
cell O1 also updates the
“Scroll Bar” .



12

Add a Spinner Control


Example 3:

Create a worksheet that
will decode a one word “Caesar
Cipher”. Allow the user to select the
“shift” value by using a “Spinner”.


13



Add a Spinner Control (cont.)


Step 1:
Create a
“Spinner”


Developer, Insert
(Icon), Form
Controls, Spin
Button, then
Drag a Rectangle


14


Add a Spinner Control (cont.)


Step 2:

Set the
properties for the
“Spinner”, and link the
output of “Spinner” to a
cell reference.


Right
-
Click, Format
Control, Control, …


Minimum Value: 0 ;
Maximum Value: 25 ;
Cell Link: O1


Test the bi
-
directional
link between the
“Spinner” and cell O1.
Notice how manually
changing cell O1 also
updates the “Spinner” .


15


Add a Spinner Control (cont.)


Example 4:

Create a worksheet that will
decode a one word “Caesar Cipher”.
Allow the user to select the “shift” value
by using a “Spinner”. The worksheet
should indicate that the user has
“LOCKED” in on a “real” word by
highlighting the characters of the
decoded word in BLUE.


Decode the following word: XDGOWJ


Decode the following word: ZOYZVO

16

Add a Spinner Control (cont.)

17

Moving, Resizing, Deleting
Form Controls


Right
-
Click on the
Form Control,
ESC

18


Computer Program


Def:

A
computer program or program

is a
sequence of instructions that a computer can
interpret and execute. (from dictionary.com)


Def:

A
computer program
is one or more
instructions that are intended for execution by a
computer. Without programs, computers would
not run. Moreover, a computer program does
nothing unless its instructions are executed by a
central processor. Computer programs are the
result of the compilation or interpretation of
programming languages, are embedded into
hardware, or may be manually inputted to the
central processor of a computer. (from
Wikipedia)

19

Computer Program (cont.)


Software such as Excel, Mathematica, or
MATLAB can be can be used to write
computer programs when more than
single step or an iterative process is
required for some application or problem
that needs to be solved.


There are many
programming languages
that can be used to write computer
programs, including
Visual Basic for
Applications.

20

Visual Basic for Applications (VBA)


BASIC


Beginners All Purpose Symbolic
Instruction Code (1960)


Visual Basic for Windows


Introduced by
Microsoft in 1991, GUI interface, OOP


Object
Oriented Programming


Visual Basic for Applications


Introduced by
Microsoft (Excel 5) as a common scripting
language for Word, Excel, Power Point, and
Access.


VBA is “BIG” and takes
years

to completely
master.


We are getting a “CRASH” course in VBA dealing
specifically with “User Defined Functions”.

21

Visual Basic (VB) Editor


Add in the
Developer Tab


File, Options,
Customize
Ribbon, Main
Tabs, Developer


Start the Visual
Basic Editor


Press ALT+F11 or
Developer Tab,
“Visual Basic”

22


User Defined Functions (UDF)


User defined functions (UDF)
, are
functions created by the user, but
behave just like built in Excel
functions.


User defined functions are written in
VBA code.

23

User Defined Functions (cont.)


Example 5
:

Create a “Hello World”
user defined function (UDF) named
Hello_World
() that accepts no
arguments and returns the phrase
“Hello World”.


For example, entering the formula
=
Hello_World
() into cell A1, will
produce “Hello World” in cell A1.


24

User Defined Functions (cont.)


VBA code:


Function
Hello_World
() As String




Hello_World

= “Hello World”


End Function


VBA code must be stored in a “Module”
which is a place for holding related VBA
functions and sub
-
routines.


In this example, the UDF
Hello_World
()
will be stored in a module named:

First_Program
”.

25

Steps to create the
Hello_World
()
user defined function

1.
Activate the VBA Editor.

2.
Insert, Module

3.
Enter the VBA code

4.
Insert “Option Explicit” at the
very top before any function or
subroutine declarations. MAKES
SURE THAT ALL VARIBLES ARE
DEFINED … GIVES ERROR IF
NOT ALL DEFINED

5.
Click on “Module1”, change
“(name)” property from
“Module1” to “
First_Program


6.
Save

7.
File, “Close and Return to
Microsoft Excel”

8.
Use the function
Hello_World
()
just like any other built
-
in Excel
function.

9.
Save the worksheet as a “Macro
-
Enabled Workbook”

26


Fahrenheit to Celsius UDF


Example 6
:

Create a user
defined function
F_To_C
()
that will convert a
temperature given and
degrees Fahrenheit and
return the equivalent
temperate in degrees
Celsius.


For example, =
F_To_C
(32)
should return 0 and
=
F_To_C
(212) should
return 100.


VBA code:

Function
F_To_C
(F As Double) As Double


F_To_C

= 5/9*(F
-
32)

End Function


27


Recalculating User Defined
Functions


The F9 key is used to
recalculate all
worksheets in all open
workbooks.


Pressing the F9 key will
not

cause “User Defined
Functions” to recalculate.


By default,
CTRL+ALT+F9 must be
pressed to “force”
all

formulas to be
recalculated.


It is important to press
CTRL+ALT+F9 after
making changes to the
VBA code of a UDF.


28


Line Continuation


Use the _ character for
line continuation
.


As an example, the following code is “too
long” for a single line:


Function
PerpBisectorIntercept
(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double


To make code more “
readible
”, use multiple
lines for VBA code, via the _ character.



Function
PerpBisectorIntercept
(x1 As Double, _





y1 As Double, _





x2 As Double, _





y2 As Double) As Double


29

Inserting Comments


Comments

are ignored by VBA.


They are notes intended to be read by humans.


Comments begin with a
single quote
character ‘ or
rem
.


Comments are often used for the following purposes:


Use comments to describe briefly the purpose of the user
defined function.


Use comments to describe changes made to the user
defined function.


Use comments to indicate that you are using functions or
code in an unusual or non
-
standard manner.


Use comments to describe the purpose of variables so that
you and other people can decipher otherwise cryptic
names.


Use comments to “temporarily” disable lines of VBA code.

30

Inserting Comments (cont.)


Example 7
: Add comments to the
following user defined function to help
make the VBA code more “readable” :


Function
Perp_Bisector_Slope
(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double


'Returns the slope of the line that is the "perpendicular bisector" of the line segment


'determined by points (x1,y1) and (x2,y2)


'The function does NOT work for vertical lines


Dim m1 As Double, m2 As Double


m1 = (y2
-

y1) / (x2
-

x1)


'negative reciprocal of m1


m2 =
-
1 / m1


Perp_Bisector_Slope

= m2

End Function

31

Variable Data Types


A variable’s
data type
determines
both the
type

of information that can
be stored, and also the
amount

of
computer memory taken.


The next slide contains a “condensed”
list of the data types available in
VBA:


32

Variable Data Types (cont.)

Type

What the Variable
Can Hold

What the Variable
Can’t Hold

A浯畮琠o映䵥浯M礠
瑨攠V慲楡b汥lT慫敳

Boolean

True or False

12,
-
12,
-
12.2, 12.2,
“Toad”
Ⱐ,潢橥捴o

2 Byt敳

䑯畢le

-
12.42424Ⱐ3.156788Ⱐ
-
12Ⱐ12

“Toad”
Ⱐ,r略ⰠF慬獥s
㱯扪散t>

8 Byt敳

Integer

Integers in the
interval

[
-
32768, 32767]

12.2,
-
12.2,
“Toad”,
True, <object>

2 Bytes

String

“Toad”
, “12” , “
-
12” ,
“12.2” , “True”

12Ⱐ
-
12ⰠTr略Ⱐ12.2Ⱐ
㱯扪散t>

⡌敮(t栠潦⁴桥h却物S朩g
i渠Byt敳

V慲楡nt

Tr略Ⱐ
“Toad”,

-
12.24252,
-
12,
<object>

(16+) Bytes

33

Why not use the “Variant” data
type all of the time?

1.
People reading VBA code may consider the
unnecessary use of “variant” as “sloppy”
programming.

2.
The “Variant” data type is a memory “hog”
that takes away from system resources.

3.
The “Variant” data type “slows” down
program execution speed. This is
important when VBA code is doing “many”
iterations.

4.
Use the “Variant” data type only when it is
“needed”.

34

Arguments to a User Defined
Function


The
arguments

to a user defined
function are the
variable(s)

located
inside of the parenthesis.


Arguments can be thought of as a
user defined function’s “input(s)”.


To specify the
data type
for an
argument, place the word “As”
immediately after the variable name
followed by the data type.

35

Arguments to a User Defined
Function (cont.)


Examples
:

Function
F_To_C
(
F As Double
) As Double



Function
Perp_Bisector_Slope
(
x1 As Double, y1 As Double, x2 As Double, y2 As Double
) As Double



Function Quadratic_1(
A As Double, B As Double, C As Double
) As Double



Function
Letter_Gap
(
A As String
) As Double



Function
Middle_Digits
(
A As String
) As String

36

Result of a User Defined
Function


To specify the data type for the
result

of a user defined function, place the
word “As” immediately after the last
closing parenthesis in the function
argument declaration followed by the
data type.


The result of a user defined function
can be thought of as what the
function “outputs”.

37

Result of a User Defined
Function (cont.)


Examples
:

Function
F_To_C
(F As Double)
As Double



Function
Perp_Bisector_Slope
(x1 As Double, y1 As Double, x2 As Double, y2 As Double)
As Double



Function Quadratic_1(A As Double, B As Double, C As Double)
As Double



Function
Letter_Gap
(A As String)
As Double



Function
Middle_Digits
(A As String)
As String

38

Local Variables


A
local variable

is a variable declared within
a user defined function or sub
-
routine.


Local variables can only be used in the user
defined function in which they are declared.


When a user defined function or sub
-
routine ends, the local variable no longer
exists, and Excel frees up the memory that
the local variable used.


Local variables are often used to store
intermediate results and to do other
“chores”.

39

Local Variables (cont.)


Example
:


Function
Perp_Bisector_Intercept
(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double


'Returns the y
-
intercept of the line that is the "perpendicular bisector" of the line segment


'determined by points (x1,y1) and (x2,y2)


'The function does NOT work for vertical lines


Dim m As Double


Dim
Midpoint_X

As Double,
Midpoint_Y

As Double


‘blah


‘blah


‘blah

End

Function


40

Forcing Programmers To
Declare Variable Types


It is considered good programming
practice to declare all variables.


To enforce this, use the VBA
“Option
Explicit”

directive at the TOP of the
module before any function or sub
-
routine declarations.

41

Variable Assignment


When dealing with assignment statements, VBA
always evaluates the expression on the right
-
hand side of the equals sign and stores the result
in the variable on the left
-
hand side of the equals
sign.


It is sometimes helpful for “beginning
programmers” to think of the equals sign as an
arrow pointing to the left.


Some programming languages automatically
initialize variables to a
default value
(0, or the
null
-
string) while others do not.


It is considered “good” programming practice to
ALWAYS initialize variables regardless of the
programming language used.


42

Variable Assignment (cont.)


Example 8
:
What are the
values of J and K
before and after
each line of the
following VBA
code:


“VBA”

Value of J and
K

K = 4

K

4

K = K + 1

K

K+1

J = K

J

K

J = K+1

J

K+1

K = J

K

J

K = J+J

K

J+J

43

Variable Assignment (cont.)

Value of J
Before

Value of K
Before

Line

Value of J
After

Value of K
After

?

?

K = 4

?

4

?

4

K = K+1

?

5

?

5

J = K

5

5

5

5

J = K+1

6

5

6

5

K = J

6

6

6

6

K = J+J

6

12

44

How to “Return” the Result of a
User Defined Function


Example
:


Function
Hello_World
() As String


‘Return “Hello World!!!!”


Hello_World

= “Hello World!!!!”

End Function


Example
:


Function
F_To_C
(F As Double) As Double


‘Return 5/9*(F
-
32)


F_To_C

= 5/9*(F
-
32)

End Function

45

How to “Return” the Result of a
User Defined Function (cont.)


Example
:

Function Slope(x1 As Double, y1 As Double, x2 As
Double, y2 As Double) As Double


‘Returns the slope of a line defined by the points
(x1,y1) and (x2,y2)


‘The function does NOT work for vertical lines


Dim m As Double


m = (y2
-
y1)/(x2
-
x1)


‘Return the value of m


Slope

= m

End Function

46

47

References


VBA Excel Notes


John Albers


Form Controls


Overview:
http://office.microsoft.com/en
-
us/excel
-
help/overview
-
of
-
forms
-
form
-
controls
-
and
-
activex
-
controls
-
on
-
a
-
worksheet
-
HA010237663.aspx?CTT=3


List Box or Combo Box
http://office.microsoft.com/en
-
us/excel
-
help/add
-
a
-
list
-
box
-
or
-
combo
-
box
-
to
-
a
-
worksheet
-
HP010236681.aspx#BM1



Scroll Bar or
Spin Button
http://office.microsoft.com/en
-
us/excel
-
help/add
-
a
-
scroll
-
bar
-
or
-
spin
-
button
-
to
-
a
-
worksheet
-
HP010236682.aspx?CTT=1



Shortcut Keys


http://office.microsoft.com/en
-
us/excel
-
help/excel
-
shortcut
-
and
-
function
-
keys
-
HP001111659.aspx