SpreadsheetBased Decision Support Systems Chapter 14 Variables Prof

Spreadsheet-Based Decision Support Systems Chapter 14: Variables Prof. Name Position University Name name@email. com (123) 456 -7890

Overview v v v v 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 v Declaring variables of different data types in VBA. v Private and public variables. v Using variables in the Message Box and Input Box user interfaces. v Mathematical, trigonometric, and derived math functions. v Conversion and string functions. v 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 v Integer and Double v String v Boolean v Constant v Range v Worksheets v Object 4

Data Types v A data type categorizes the values that can be assigned to a variable. v 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 v To declare a variable in VBA, we use the Dim statement. – Dim varaible As Data. Type v Remember that VBA for Excel is case-sensitive. v A constant variable is a variable whose value will never change. – To declare a constant, we use Const – Const variable = value v We will also see public and private declarations later. 6

Integers and Doubles v Both integers and doubles are numerical values. v 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. v Doubles have values from -1. 79769 E 308 to -4. 94065 E-324 for negative values and from 4. 94065 E-324 to 1. 79769 E 308 for positive values. – Doubles will be used for any data or calculations. 7

String v A string is a segment of text. v 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 v 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 v A range is a variable which can be used with all of the properties and methods of the Range object. v We must also use the Set declaration to initialize the value of a range variable. – For example, to set My. Range equal to cell A 1 we would type the following: Set My. Range = Range(“A 1”) 10

Using Ranges v We will use Ranges often to increase the efficiency of our code. – We can use ranges to define what we will call a Start. Cell. – We set the value of this range variable at the beginning of our code and reference this Start. Cell variable name throughout the remainder of the code. v 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, Start. Cell As Range text = Input. Box(“Enter starting cell for table: ”) Set Start. Cell = Range(text) 11

Worksheets v The worksheets data type defines a Worksheets object. v 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 v The object variable can be used to define any object in Excel. v This can be considered a drawing object, a range, a worksheet, or any other object. v Any of the properties or methods associated with any object in Excel can be used with the object variable 13

Variable Scope v Private and Public Variable Declarations v Keeping Track of Variable Values 14

Variable Scope v It is important to understand the scope of each variable you declare. v 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. v There are two types of variable declarations which determine the scope of each variable. – Public – Private 15

Private Variables v A Private variable is declared using the Dim statement. v 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 v Procedure level private declaration Sub 1() Dim i As Integer. . . End Sub -----------------Sub 2() Dim i As Integer. . End Sub v Module level private declaration Dim i As Integer ------------Sub 1(). . . . End Sub ------------Sub 2(). . . . End Sub 17

Public Variables v A Public variable on the other hand can be used in any sub procedure in any module. v To declare a public variable, we use the Public statement. – Public i As Integer 18

Keeping Track of Variable Values v 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 v Message Box v Input Box 20

Message Box and Input Box v Message Boxes allow you to print something for the user to see in a small dialog box. v Input Boxes allow you to prompt the user to enter some value in a small dialog box. 21

Message Box v You can print the following with a Message Box – Text – Variable values – Both using concatenation with & sign Msg. Box “This is a string of text. ” Msg. Box "The height is " & h & vb. Cr. Lf & " and the width is " & w & ". " 22

Msg. Box Function v You can also use the Msg. Box function to specify button types and response types – Msg. Box (prompt, [buttons], [title], [helpfile, context]) v The prompt is either the text or variable (or concatenation of both) which will be displayed in the dialog box. v The title argument allows you to enter text a title for the dialog box. v The helpfile and context arguments allow you to give the user help options. 23

Msg. Box Function (cont’d) v The buttons argument takes a VB Constant value to determine the number and style of buttons available to the user. – – v vb. OKOnly: Displays an OK button only vb. OKCancel: Displays OK and Cancel buttons vb. Yes. No. Cancel: Displays Yes, No, and Cancel buttons vb. Yes. No: Displays Yes and No buttons You can also capture the user’s response to your Message Box with a VBA variable called response. – – – – vb. OK: If response was OK vb. Cancel: If response was Cancel vb. Abort: If response was Abort vb. Retry: If response was Retry vb. Ignore: If response was Ignore vb. Yes: If response was Yes vb. No: If response was No 24

Input. Box Function v The Input. Box function is always assigned to a variable. v This function takes the following general form: – Input. Box (prompt, [title], [default], [xpos], [ypos], [helpfile, context]) v The prompt and title arguments of the Input. Box function are the same as those of the Msg. Box function. v The default argument allows you enter a default value to display in the Input Box. v The xpos and ypos arguments allow you to position the Input Box relative the left and top edges of the screen. v The helpfile and context arguments are the same as those of the Msg. Box function. 25

Input. Box Function (cont’d) v For example, you may prompt the user to enter a number and store the user value in the variable User. Number = Input. Box (prompt, [title], [default], [xpos], [ypos], [helpfile, context]) 26

VBA Math Functions v Typical Math Functions v Trigonometric Functions v Derived Math Functions v Conversion Functions v String Functions 27

VBA Math Functions v We can use typical math functions in VBA without needing to create a formula in the spreadsheet. v There are – Typical Math Functions – Trigonometric Functions – Derived Math Functions 28

Typical Math Functions v We will describe seven basic math functions – – – – Abs Sqr Mod Int Rnd Exp Log 29

Abs Function v The Abs function calculates the absolute value of a variable. v This function can be used with both integer and double data types (as well as with other numerical variables). v It will return the same data type which is used in the function. – Abs(-10) = 10 30

Sqr Function v The Sqr function calculates the square root of a number. v It can also be used with any numerical data type (must be greater than 0). v It will always return a double data type. – Sqr(100) = 10 31

Mod Function v 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). v The format of this function is divisor Mod number. Sub Modular. Function() Dim x As Integer, y As Integer x=2 y=4 Msg. Box x Mod y ‘answer is 2 x=4 y=2 Msg. Box x Mod y ‘answer is 0 End Sub 32

Int Function v The Int function removes the decimal part of a double variable and returns the integer part. v The result will be an integer data type. v For positive numbers, the Int function always rounds down. v 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 v The Rnd function will generate a random number. v You can either enter a seed as a parameter for the function, or leave the seed value blank. v This function will return a double data type between 0 and 1. v To create random integers in a specific range, use the formula – Int((upperbound - lowerbound + 1) * Rnd + lowerbound) 34

Exp Function v The Exp function raises the constant e to some power given in the function statement. v The value returned will be a double data type. – Exp(2) = e 2 = 7. 389056099 35

Log Function v The Log function calculates the natural log, that is the logarithm with base e, of a given number. v The result is a double data type. v You can calculate logarithms with base n for any number by dividing the natural logarithm of that number by the natural logarithm of n. v For example, to calculate the log of 15 with base 10, you would type. – Log(15) / Log(10) = 1. 176091259 36

Trigonometric Functions v There are four basic trig functions that we will describe – – v 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 v The Sin, Cos, and Tan functions take an angle and return the ratio of two sides of a right triangle. v The value returned is a double data type. v 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 v The Atn function calculates the arctangent of a given ratio. v The result is an angle in radians, which will be a double data type. v 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 v Using the above functions, we can derive other functions. v There is a long list of examples of derived functions in Microsoft Visual Basic Help; we will demonstrate two: – Arcsin – Sec v We will use function procedures to create these and other derived math functions. 40

Arcsin Function v We can derive the arcsine or inverse sine function using the Atn and Sqr functions. v We can name this the Arcsin function. v This function would be defined as follows – Arcsin(X) = Atn(X / Sqr(-X * X + 1)) 41

Sec Function v We can derive the secant function using the Cos function. v We can name this function Sec. v It is defined as follows: – Sec(X) = 1 / Cos(X) 42

Conversion Functions v There are several functions in VBA that can be used to convert one data type to another. v 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) v 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) v 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) v 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) v 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 v There are several string functions available in VBA. v 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) v To extract a segment from a string value, you can use the Mid function. – Mid(string variable or string expression, starting_position, length) § Mid. Name = Mid(“John. Henry. Smith”, 5, 5) v 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 v Creating and Modifying Shapes v Making Calculations and Creating a Report 46

Applications (cont’d) v 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 v 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 v v We begin by preparing the following spreadsheet. We have a brief description of the program, and several buttons. 49

Creating a Square v 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. v We call these variables width and height – Both of these variables should be define as double data types Sub Create. Square() Dim width As Double, height As Double width = Input. Box("Please enter the width of your square: ", "Width", 50) height = Input. Box("Please enter the height of your square: ", "Height", 50) End Sub 50

Creating a Square (cont’d) v Now we use the Add. Shape method associated with the Active. Sheet and Shapes objects to create the square. – The shape type parameter should be mso. Shape. Rectangle. – The position parameters can be anything for now. – The width and height parameters will be set as our variable values. Active. Sheet. Shapes. Add. Shape(mso. Shape. Rectangle, 146. 25, 289. 5, width, height). Select v 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 v The final Create. Square() procedure: 52

Figure 14. 3 v The output is as follows: 53

Creating a Circle v v v This sub procedure will be very similar to creating a square. We only need to change the mso. Auto. Shape. Type to mso. Shape. Oval. However, we also decide to create a new variable, radius, to be used for both the width and height values in the Add. Shape method. 54

Figure 14. 5 v The Input Box for the radius variable value: 55

Counting the Shapes v We will first need two variables (which can both be integer data types): – Num. Squares – Num. Circles v We first need to initialize these variables to have value 0. – We cannot perform this initialization in either the Create. Square() or Create. Circle() 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 v The Public declaration of Num. Squares and Num. Circles. 57

Workbook Event Procedure v We create a Workbook_Open() event procedure in the Workbook object of our current VBA project. v In this procedure we initialize the Num. Squares and Num. Circles variables to be 0. v 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. v 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 A 12: K 50. We specify that this range is in our first worksheet and use the Delete method. 58

Figure 14. 7 v This event procedure initializes the variable values and clears previously created shapes. v The code is as follows: 59

Figure 14. 8 v We can now create and count objects: 60

Incrementing Variable Values v To increment a variable value in VBA, use the structure – variable = variable + 1 v We create two procedures for deleting squares and circles: 61

Label Shape v To label our shape, we first prompt the user to see what text will be used as the label. v We declare a private variable called text and assign it to an Input Box as follows – text = Input. Box("Please enter label for your shape: ", "Shape Label", "Square 1") v 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 v The procedure and result: 63

Position Shape v To position a shape, instead of asking the user to provide two numerical values for the left and top arguments of the Add. Shape method, we will cut and paste the selected object to a specified range. v We first declare a private variable called position to capture the range name where the user wants to move the selected object. v We then use a range variable called place to use this range value in our Paste method. 64

Position Shape (cont’d) v The variable declarations, Input. Box function, and range assignment are written as follows Dim position As String, place As Range position = Input. Box("Please enter cell name where you would like to position your shape (below A 12): ", "Position", "A 12") Set place = Range(position) v We now use the Cut method on the Selection object, and then use the Active. Sheet object and Paste method to paste the object to the desired range. v We specify this range with the Destination argument as shown below. – Active. Sheet. Paste Destination: = place 65

Figures 14. 12 and 14. 13 v Positioning the shape in cell A 12. 66

Making Calculations and Creating a Report v 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 v Our spreadsheet is prepared with the appropriate buttons and table headings. v We only need three sub procedures for this program. – Calc. Rand. Num() – Calc. Angles() – Clear. All() 68

Random Number Calculations v We will first ask the user to specify an interval in which they want the random number to be generated. v We will use two variables and the Input. Box function to achieve this. v We declare these variables privately in the Calc. Rand. Num() sub procedure and name them low and upp. low = Input. Box(“Please enter the lower bound of the interval in which you” & _ “would like to generate a random number: “, “Lower Bound”, – 10) upp = Input. Box(“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) v We now use the following function to generate a random number in this interval – x = (upp - low + 1) * Rnd() + low v We will define this x variable publicly as we will use it to perform calculations in our Calc. Angles() sub procedure as well. v We can insert this result into the first column of our random number calculation table. v Since we will be inserting all other random number calculations into the same table, we will use a range variable, similar to the Start. Cell example, for this table and use it to reference all report values. 70

Random Number Calculations (cont’d) v We define this range variable publicly as we will use it in the Clear. All() procedure as well. v We name this variable Rand. Start and declare it as follows. v Now we should set this variable to a particular range in the worksheet. We do this in the Calc. Rand. Num() sub procedure by typing. – Public Rand. Start As Range – Set Rand. Start = Worksheets(“Example 2”). Range(“A 14”) v 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. – Rand. Start. Offset(1, 0). Value = x 71

Figures 14. 15 and 14. 16 v We can now use the Value property of our range variable to insert all other calculations directly. 72

Angle Calculation v For the Calc. Angles() sub procedure we will begin by asking the user to enter an angle in degrees. v We do this using the Input. Box function again with the prompt, title, and default value defined as shown below. – x = Input. Box("Enter an angle value in degrees: ", "Angle value", 45) v Notice we used the same x value as before, which was declared as a public variable. v We now need to convert this value into radians before using any trigonometric calculations. v To do this, we will first need to declare the constant pi. – Const pi = 3. 14 73

Angle Calculation (cont’d) v We can now use the following function to make the angle conversion. – x = x * pi / 180 v We will use another range variable in this procedure to insert calculation values into the angle calculation table. v We also define this variable publicly so that we can use it again in the Clear. All() procedure. v We name this variable Angle. Start and declare it as follows. – Public Angle. Start As Range 74

Figures 14. 17 and 14. 18 v 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 v Clear. All() sub procedure will clear the values in both tables. v We will use the Clear. Contents method so as to keep the borders and other formatting of our table. v We will only clear the second row of both tables. v To do this, we will use the Rand. Start and Angle. Start variables to define the ranges we want to clear. v To clear the second row of the random number calculation table: – Range(Rand. Start. Offset(1, 0), Rand. Start. Offset(1, 5)). Clear. Contents v To clear the second row of the angle calculation table: – Range(Angle. Start. Offset(1, 0), Angle. Start. Offset(1, 4)). Clear. Contents 76

Summary v v v v 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 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 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 v (place links here) 78