1 / 78

Chapter 14: Variables

Spreadsheet-Based Decision Support Systems. Chapter 14: Variables. Prof. Name name@email.com Position (123) 456-7890 University Name. Overview. 14.1 Introduction 14.2 Variable Declarations and Data Types 14.3 Variable Scope

stasia
Download Presentation

Chapter 14: Variables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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

  3. 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.

  4. Variable Declarations and Data Types • Integer and Double • String • Boolean • Constant • Range • Worksheets • Object

  5. 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

  6. Defining a Variable • To declare a variable in VBA, we use the command Dim • 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 publicandprivatedeclarations later.

  7. 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.

  8. 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.

  9. 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.

  10. 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”)

  11. 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)

  12. 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.

  13. 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

  14. Variable Scope • Private and Public Variable Declarations • Keeping Track of Variable Values

  15. 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

  16. 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.

  17. 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

  18. 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

  19. 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

  20. Variables in User Interface • Message Box • Input Box

  21. 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.

  22. 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 & "."

  23. 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.

  24. MsgBox Function (cont’d) • The buttons argument takes a VB Constant value to determine the number and style of buttons available to the user. • vbOKOnlyDisplays an OK button only • vbOKCancelDisplays OK and Cancel buttons • vbYesNoCancelDisplays Yes, No, and Cancel buttons • vbYesNoDisplays Yes and No buttons • You can also capture the user’s response to your Message Box with a VBA variable called response. • vbOKIf response was OK • vbCancelIf response was Cancel • vbAbortIf response was Abort • vbRetryIf response was Retry • vbIgnoreIf response was Ignore • vbYesIf response was Yes • vbNoIf response was No

  25. 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 xposand 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.

  26. 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])

  27. VBA Math Functions • Typical Math Functions • Trigonometric Functions • Derived Math Functions • Conversion Functions • String Functions

  28. 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

  29. Typical Math Functions • We will describe six basic math functions • Abs • Sqr • Int • Rnd • Exp • Log

  30. 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

  31. 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

  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 (Log1015), 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.14

  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 Sinand 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 Doulbe 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) • 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.

More Related