Transcript EIN 4905/ESI 6912 Decision Support Systems Excel

Spreadsheet-Based Decision Support Systems
Chapter 14: Variables
Prof. Name
Position
University Name
[email protected]
(123) 456-7890
Overview







14.1 Introduction
14.2 Variable Declarations and Data Types
14.3 Variable Scope
14.4 Variables in User Interface
14.5 VBA Math Functions
14.6 Applications
14.7 Summary
2
Introduction

Declaring variables of different data types in VBA.

Private and public variables.

Using variables in the Message Box and Input Box user interfaces.

Mathematical, trigonometric, and derived math functions.

Conversion and string functions.

Two detailed examples of a simple DSS application which uses basic
Excel object manipulation, variables, and a simple user interface.
3
Variable Declarations and Data Types

Integer and Double

String

Boolean

Constant

Range

Worksheets

Object
4
Data Types

A data type categorizes the values that can be assigned to a variable.

There are several different data types available in VBA; we will focus on
the following seven data types for now, which we feel will be used most
often.
–
–
–
–
–
–
–
Integer
Double
String
Boolean
Range
Worksheets
Object
5
Defining a Variable

To declare a variable in VBA, we use the Dim statement.
– Dim varaible As DataType

Remember that VBA for Excel is case-sensitive.

A constant variable is a variable whose value will never change.
– To declare a constant, we use Const
– Const variable = value

We will also see public and private declarations later.
6
Integers and Doubles

Both integers and doubles are numerical values.

Integers are non-decimal numbers ranging in value from -32,768 to
32,767.
– Integers will be used for simple numerical values, counting in loops, and
enumerating arrays.

Doubles have values from -1.79769E308 to -4.94065E-324 for negative
values and from 4.94065E-324 to 1.79769E308 for positive values.
– Doubles will be used for any data or calculations.
7
String

A string is a segment of text.

This text can include upper and lower-case letters, punctuation marks,
and numbers; however these numbers can not be used in calculations as
they will be viewed as text.
– We will use strings to name objects, label objects, and label data in
spreadsheets.
8
Boolean

A Boolean is a variable whose value is either True or False.
– We will use Boolean variables often in logic statements, If, Then statements,
and loops.
9
Range

A range is a variable which can be used with all of the properties and
methods of the Range object.

We must also use the Set declaration to initialize the value of a range
variable.
– For example, to set MyRange equal to cell A1 we would type the following:
Set MyRange = Range(“A1”)
10
Using Ranges

We will use Ranges often to increase the efficiency of our code.
– We can use ranges to define what we will call a StartCell.
– We set the value of this range variable at the beginning of our code and
reference this StartCell variable name throughout the remainder of the code.

We may also use Ranges to make our code more dynamic.
– We can use an Input Box to capture where the user wants to start a table.
Dim text As String, StartCell As Range
text = InputBox(“Enter starting cell for table:”)
Set StartCell = Range(text)
11
Worksheets

The worksheets data type defines a Worksheets object.

This variable can be assigned any of the properties or methods used with
the Worksheets object.
– We will use this object when creating loops and/or functions to perform
repeated methods or set repeated properties to multiple worksheets.
12
Object

The object variable can be used to define any object in Excel.

This can be considered a drawing object, a range, a worksheet, or any
other object.

Any of the properties or methods associated with any object in Excel can
be used with the object variable
13
Variable Scope

Private and Public Variable Declarations

Keeping Track of Variable Values
14
Variable Scope

It is important to understand the scope of each variable you declare.

You must check your code to see if a variable will be used in only one
sub procedure, or function procedure, or if it will be used in several
different procedures.

There are two types of variable declarations which determine the scope
of each variable.
– Public
– Private
15
Private Variables

A Private variable is declared using the Dim statement.

This variable can be private on two levels
– Procedure level = only used in the particular sub procedure in which it was
declared.
– Module level = used in any procedure in the module, but only in the particular
module in which it was declared.
16
Procedure Level vs Module Level

Procedure level private declaration
Sub Sub1()
Dim i As Integer
.....
End Sub
---------------------------------Sub Sub2()
Dim i As Integer
....
End Sub

Module level private declaration
Dim i As Integer
------------------------Sub Sub1()
.......
End Sub
-----------------------Sub Sub2()
........
End Sub
17
Public Variables

A Public variable on the other hand can be used in any sub procedure in
any module.

To declare a public variable, we use the Public statement.
– Public i As Integer
18
Keeping Track of Variable Values

There are two main ways to keep track of variable values while running
your program.
– Use the Watch Window
 Click on View > Watch Window from the menu
 Highlight any variable from the Code Window and drag it to the Watch Window
– Hold your cursor over a variable name in the Code Window to see a small
display of its value
19
Variables in User Interface

Message Box

Input Box
20
Message Box and Input Box

Message Boxes allow you to print something for the user to see in a
small dialog box.

Input Boxes allow you to prompt the user to enter some value in a small
dialog box.
21
Message Box

You can print the following with a Message Box
– Text
– Variable values
– Both using concatenation with & sign
MsgBox “This is a string of text.”
MsgBox "The height is " & h & vbCrLf &
" and the width is " & w & "."
22
MsgBox Function

You can also use the MsgBox function to specify button types and
response types
– MsgBox (prompt, [buttons], [title], [helpfile, context])

The prompt is either the text or variable (or concatenation of both)
which will be displayed in the dialog box.

The title argument allows you to enter text a title for the dialog box.

The helpfile and context arguments allow you to give the user help
options.
23
MsgBox Function (cont’d)

The buttons argument takes a VB Constant value to determine the
number and style of buttons available to the user.
–
–
–
–

vbOKOnly: Displays an OK button only
vbOKCancel: Displays OK and Cancel buttons
vbYesNoCancel: Displays Yes, No, and Cancel buttons
vbYesNo: Displays Yes and No buttons
You can also capture the user’s response to your Message Box with a
VBA variable called response.
–
–
–
–
–
–
–
vbOK: If response was OK
vbCancel: If response was Cancel
vbAbort: If response was Abort
vbRetry: If response was Retry
vbIgnore: If response was Ignore
vbYes: If response was Yes
vbNo: If response was No
24
InputBox Function

The InputBox function is always assigned to a variable.

This function takes the following general form:
– InputBox (prompt, [title], [default], [xpos], [ypos], [helpfile, context])

The prompt and title arguments of the InputBox function are the same as
those of the MsgBox function.

The default argument allows you enter a default value to display in the
Input Box.

The xpos and ypos arguments allow you to position the Input Box
relative the left and top edges of the screen.

The helpfile and context arguments are the same as those of the
MsgBox function.
25
InputBox Function (cont’d)

For example, you may prompt the user to enter a number and store the
user value in the variable UserNumber.
UserNumber = InputBox (prompt, [title], [default], [xpos], [ypos], [helpfile,
context])
26
VBA Math Functions

Typical Math Functions

Trigonometric Functions

Derived Math Functions

Conversion Functions

String Functions
27
VBA Math Functions

We can use typical math functions in VBA without needing to create a
formula in the spreadsheet.

There are
– Typical Math Functions
– Trigonometric Functions
– Derived Math Functions
28
Typical Math Functions

We will describe seven basic math functions
–
–
–
–
–
–
–
Abs
Sqr
Mod
Int
Rnd
Exp
Log
29
Abs Function

The Abs function calculates the absolute value of a variable.

This function can be used with both integer and double data types (as
well as with other numerical variables).

It will return the same data type which is used in the function.
– Abs(-10) = 10
30
Sqr Function

The Sqr function calculates the square root of a number.

It can also be used with any numerical data type (must be greater than
0).

It will always return a double data type.
– Sqr(100) = 10
31
Mod Function

The Mod function calculates the modular value of a number, given its
divisor (that is, it gives the remainder when a number is divided by the
divisor).

The format of this function is divisor Mod number.
Sub ModularFunction()
Dim x As Integer, y As Integer
x=2
y=4
MsgBox x Mod y
‘answer is 2
x=4
y=2
MsgBox x Mod y
‘answer is 0
End Sub
32
Int Function

The Int function removes the decimal part of a double variable and
returns the integer part.

The result will be an integer data type.

For positive numbers, the Int function always rounds down.

For negative numbers, the Int function will return the first negative integer
less than or equal to the original variable value.
– Int(5.6) = 5
– Int(-4.3) = -5
33
Rnd Function

The Rnd function will generate a random number.

You can either enter a seed as a parameter for the function, or leave the
seed value blank.

This function will return a double data type between 0 and 1.

To create random integers in a specific range, use the formula
– Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
34
Exp Function

The Exp function raises the constant e to some power given in the
function statement.

The value returned will be a double data type.
– Exp(2) = e2 = 7.389056099
35
Log Function

The Log function calculates the natural log, that is the logarithm with
base e, of a given number.

The result is a double data type.

You can calculate logarithms with base n for any number by dividing the
natural logarithm of that number by the natural logarithm of n.

For example, to calculate the log of 15 with base 10, you would type.
– Log(15) / Log(10) = 1.176091259
36
Trigonometric Functions

There are four basic trig functions that we will describe
–
–
–
–

Sin
Cos
Tan
Atn
These functions all take angles as a parameter, and this angle value
should be entered in radians.
– To convert degrees to radians, multiply degrees by pi/180
– To use the variable pi, we have to define a constant
Const pi = 3.14159
37
Sin, Cos, and Tan Functions

The Sin, Cos, and Tan functions take an angle and return the ratio of
two sides of a right triangle.

The value returned is a double data type.

The result of the Sin and Cos functions will be between -1 and 1.
Sin(pi/4) = 0.7071067812
Cos(pi/3) = 0.5
Tan(pi/6) = 0.5773502692
38
Atn Function

The Atn function calculates the arctangent of a given ratio.

The result is an angle in radians, which will be a double data type.

The result of the Atn function will be between -pi/2 to pi/2 radians.
– To convert radians to degrees, multiply radians by 180/pi.
– Atn(0.5773502692) = pi/6
39
Derived Math Functions

Using the above functions, we can derive other functions.

There is a long list of examples of derived functions in Microsoft Visual
Basic Help; we will demonstrate two:
– Arcsin
– Sec

We will use function procedures to create these and other derived math
functions.
40
Arcsin Function

We can derive the arcsine or inverse sine function using the Atn and Sqr
functions.

We can name this the Arcsin function.

This function would be defined as follows
– Arcsin(X) = Atn(X / Sqr(-X * X + 1))
41
Sec Function

We can derive the secant function using the Cos function.

We can name this function Sec.

It is defined as follows:
– Sec(X) = 1 / Cos(X)
42
Conversion Functions

There are several functions in VBA that can be used to convert one data
type to another.

Two main conversion functions used with numerical values are CInt and
CDbl.
– CInt converts a variable to an Integer data type:
 CInt(variable or expression)
– CDbl converts a variable to a Double data type:
 CDbl(variable or expression)

The Val function also extracts a numerical value from a string. The input
for this function is an expression.
– Val(expression)
43
Conversion Functions (cont’d)

Any numeric expression can also be converted to a string using the CStr
function. The input for this function is again any variable or expression:
– CStr(variable or expression)

The CDate function converts values into the Date data type. This data
type is necessary to work with dates in your code for comparisons or
calculations.
– CDate(variable or expression)

Two other useful conversion functions are Asc and Chr. These
functions, which use the ASCII list of numbers and characters, can be
used to convert letters to numbers and numbers to letters, respectively:
– Asc(character): Asc(“A”) = 65
– Chr(number): Chr(65) = “A”
44
String Functions

There are several string functions available in VBA.

Two useful string functions are UCase and LCase, which can be used to
convert a string into all upper case or all lower case, respectively.
– UCase(string variable or string expression)
– LCase(string variable or string expression)

To extract a segment from a string value, you can use the Mid function.
– Mid(string variable or string expression, starting_position, length)
 MidName = Mid(“JohnHenrySmith”, 5, 5)

Another useful string function is Len. This function determines the length
of a string variable value or string expression:
– Len(string variable or string expression)
45
Applications

Creating and Modifying Shapes

Making Calculations and Creating a Report
46
Applications (cont’d)

We consider two applications, or extended examples, in which we
implement some of the new techniques learned in this chapter.
– Creating and Modifying Shapes (Example 1)
 Message Boxes, Input Boxes, and some various data types
– Making Calculations and Creating Reports (Example 2)
 Several different functions and the range data type
47
Creating and Modifying Shapes

Let us create a program in which a user can:
–
–
–
–
–
create either a square or a circle
label their shape
position their shape on the spreadsheet
keep track of how many squares and circles have been created
delete a square or circle
48
Figure 14.1


We begin by preparing the following spreadsheet.
We have a brief description of the program, and several buttons.
49
Creating a Square

To do this, we will first declare variables that can be assigned to an Input
Boxes to find the desired width and height of the square.

We call these variables width and height
– Both of these variables should be define as double data types
Sub CreateSquare()
Dim width As Double, height As Double
width = InputBox("Please enter the width of your square:", "Width", 50)
height = InputBox("Please enter the height of your square:", "Height", 50)
End Sub
50
Creating a Square (cont’d)

Now we use the AddShape method associated with the ActiveSheet and
Shapes objects to create the square.
– The shape type parameter should be msoShapeRectangle.
– The position parameters can be anything for now.
– The width and height parameters will be set as our variable values.
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 146.25, 289.5, width,
height).Select

We then simply display a Message Box which tells the user that the
macro has run successfully and the square has been created.
51
Figure 14.2

The final CreateSquare() procedure:
52
Figure 14.3

The output is as follows:
53
Creating a Circle



This sub procedure will be very similar to creating a square.
We only need to change the msoAutoShapeType to msoShapeOval.
However, we also decide to create a new variable, radius, to be used for
both the width and height values in the AddShape method.
54
Figure 14.5

The Input Box for the radius variable value:
55
Counting the Shapes

We will first need two variables (which can both be integer data types):
– NumSquares
– NumCircles

We first need to initialize these variables to have value 0.
– We cannot perform this initialization in either the CreateSquare() or
CreateCircle() sub procedures since we want to increase these values each
time the macros are called.
– Therefore, we can either create a new sub procedure called Main() or
Initialize() and assign it to a new “Start” button, or we can use the
Workbook_Open() event procedure.
– For now we use the Workbook event procedure.
56
Figures 14.6

The Public declaration of NumSquares and NumCircles.
57
Workbook Event Procedure

We create a Workbook_Open() event procedure in the Workbook object
of our current VBA project.

In this procedure we initialize the NumSquares and NumCircles variables
to be 0.

Since we want to use these variables in the Workbook object and in our
main module, we must declare them as public variables with the Public
statement.

We also clear the range of cells where the shapes will most likely be
created and/or positioned in this event procedure.
– We assume this range is A12:K50. We specify that this range is in our first
worksheet and use the Delete method.
58
Figure 14.7

This event procedure initializes the variable values and clears previously
created shapes.

The code is as follows:
59
Figure 14.8

We can now create and count objects:
60
Incrementing Variable Values

To increment a variable value in VBA, use the structure
– variable = variable + 1

We create two procedures for deleting squares and circles:
61
Label Shape

To label our shape, we first prompt the user to see what text will be used
as the label.

We declare a private variable called text and assign it to an Input Box as
follows
– text = InputBox("Please enter label for your shape:", "Shape Label", "Square
1")

Now we use the Selection object with the Characters property and Text
sub property and assign the value equal to our text variable.
– Selection.Characters.Text = text
62
Figures 14.10 and 14.11

The procedure and result:
63
Position Shape

To position a shape, instead of asking the user to provide two numerical
values for the left and top arguments of the AddShape method, we will
cut and paste the selected object to a specified range.

We first declare a private variable called position to capture the range
name where the user wants to move the selected object.

We then use a range variable called place to use this range value in our
Paste method.
64
Position Shape (cont’d)

The variable declarations, InputBox function, and range assignment
are written as follows
Dim position As String, place As Range
position = InputBox("Please enter cell name where you would like to
position your shape (below A12):", "Position", "A12")
Set place = Range(position)

We now use the Cut method on the Selection object, and then use the
ActiveSheet object and Paste method to paste the object to the desired
range.

We specify this range with the Destination argument as shown below.
– ActiveSheet.Paste Destination:= place
65
Figures 14.12 and 14.13

Positioning the shape in cell A12.
66
Making Calculations and Creating a Report

We will create a program which can make various calculations and
create reports.
– The user can generate a random numbers in a specified interval and
calculate its:





Absolute value
Integer value
Square root
Exponential power
Natural logarithm
– The user can also provide an angle to calculate its:
 Sine
 Cosine
 Tangent
– We also provide a “Clear All” button to clear current reports.
67
Figure 14.14

Our spreadsheet is
prepared with the
appropriate buttons and
table headings.

We only need three sub
procedures for this
program.
– CalcRandNum()
– CalcAngles()
– ClearAll()
68
Random Number Calculations

We will first ask the user to specify an interval in which they want the
random number to be generated.

We will use two variables and the InputBox function to achieve this.

We declare these variables privately in the CalcRandNum() sub
procedure and name them low and upp.
low = InputBox(“Please enter the lower bound of the interval in which you” & _
“would like to generate a random number: “, “Lower Bound”, –10)
upp = InputBox(“Please enter the upper bound of the interval in which you” & _
“would like to generate a random number: “, “Upper Bound”, 10)
69
Random Number Calculations (cont’d)

We now use the following function to generate a random number in this
interval
– x = (upp - low + 1) * Rnd() + low

We will define this x variable publicly as we will use it to perform
calculations in our CalcAngles() sub procedure as well.

We can insert this result into the first column of our random number
calculation table.

Since we will be inserting all other random number calculations into the
same table, we will use a range variable, similar to the StartCell example,
for this table and use it to reference all report values.
70
Random Number Calculations (cont’d)

We define this range variable publicly as we will use it in the ClearAll()
procedure as well.

We name this variable RandStart and declare it as follows.
– Public RandStart As Range

Now we should set this variable to a particular range in the worksheet.
We do this in the CalcRandNum() sub procedure by typing.
– Set RandStart = Worksheets(“Example 2”).Range(“A14”)

We can now use this variable as a Range object and apply the Offset
method to insert calculation results into our table. We insert the first
value, the random number generated in the specified interval, into the
first column.
– RandStart.Offset(1, 0).Value = x
71
Figures 14.15 and 14.16

We can now use the Value property of our range variable to insert all
other calculations directly.
72
Angle Calculation

For the CalcAngles() sub procedure we will begin by asking the user to
enter an angle in degrees.

We do this using the InputBox function again with the prompt, title, and
default value defined as shown below.
– x = InputBox("Enter an angle value in degrees:", "Angle value", 45)

Notice we used the same x value as before, which was declared as a
public variable.

We now need to convert this value into radians before using any
trigonometric calculations.

To do this, we will first need to declare the constant pi.
– Const pi = 3.14
73
Angle Calculation (cont’d)

We can now use the following function to make the angle conversion.
– x = x * pi / 180

We will use another range variable in this procedure to insert calculation
values into the angle calculation table.

We also define this variable publicly so that we can use it again in the
ClearAll() procedure.

We name this variable AngleStart and declare it as follows.
– Public AngleStart As Range
74
Figures 14.17 and 14.18

We can now use the Offset method again to insert the original degree
value of the angle, the converted radian value of the angle, and the Sin,
Cos, and Tan calculation results into the report.
75
Clear All Procedure

ClearAll() sub procedure will clear the values in both tables.

We will use the ClearContents method so as to keep the borders and
other formatting of our table.

We will only clear the second row of both tables.

To do this, we will use the RandStart and AngleStart variables to define
the ranges we want to clear.

To clear the second row of the random number calculation table:
– Range(RandStart.Offset(1, 0), RandStart.Offset(1, 5)).ClearContents

To clear the second row of the angle calculation table:
– Range(AngleStart.Offset(1, 0), AngleStart.Offset(1, 4)).ClearContents
76
Summary







To declare a variable in VBA, use the Dim statement. It is important to remember
that VBA for Excel is case-sensitive.
A data type categorizes the values that can be assigned to a variable.
There are several data types available for variables in VBA, including integer,
double, string, Booleans, range, worksheet, and object.
There are two types of variable declarations: Public and Private. We declare a
private variable using the Dim statement and a public variable using the Public
statement.
Message Boxes and Input Boxes allow us to communicate with the user using
VBA code. A Message Box prints information to a small dialog box, and an Input
Box prompts the user to enter information into a small dialog box.
There are several math functions in VBA that can be used with variables or
values. There are a few trigonometric functions in VBA that you can use with
variables or values. You can also derive other functions using the pre-defined
VBA functions. There are also several conversion and string functions in VBA.
To keep track of variable values while running our code, we can use the Watch
Window in order to observe several variable values at once or we can place the
cursor over a particular variable in the Code Window.
77
Additional Links

(place links here)
78