Download Presentation

Loading in 3 Seconds

This presentation is the property of its rightful owner.

X

Sponsored Links

- 76 Views
- Uploaded on
- Presentation posted in: General

IE 212: Computational Methods for Industrial Engineering

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

IE 212: Computational Methods for Industrial Engineering

Lecture Notes #4 Appendix:

VBA Functions

Dr. J. David Porter

Summer 2014

- There are several string functions available in VBA
- These functions are useful to inspect and manipulate strings
- Resulting strings can be used later to create other lists or to concatenate them with other values (i.e., strings or numerical)

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

- A very useful string function is Len
- This function determines the length of a string variable value or string expression
- Len(string variable or string expression)

- String manipulation almost always start with determining the length of the string

- This function determines the length of a string variable value or string expression
- Other useful string manipulation functions include

- Left
- Right
- Mid

- Trim
- LTrim
- RTrim

- InStr
- InStrRev

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

- CInt converts a variable to an Integer data type
- The Val function also extracts a numerical value from a string
- The input for this function is an expression
- Val(expression)

- The input for this function is an expression

- 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 input for this function is again any 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)

- This data type is necessary to work with dates in your code for comparisons or calculations

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

- Asc(character)

- We can use typical math functions in VBA without needing to create a formula in the spreadsheet
- Typical Math Functions
- Trigonometric Functions
- Derived Math Functions

- We will describe six basic math functions
- Abs
- Sqr
- Int
- Rnd
- Exp
- Log

- 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

- 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

- It can also be used with any numerical data type

- 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

- 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 Doubledata type between 0 and 1
- To create random integers in a specific range, use the formula
- Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

- The Exp function raises the constant e to some power given in the function statement
- The value returned will be a Doubledata type
- Exp(2) = e2 = 7.389056099

- The value returned will be a Doubledata type
- The Log function calculates the natural log (i.e., the logarithm with base e) of a given number
- The result is a Doubledata 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

- There are four basic trig functions that we will describe
- Sin
- Cos
- Tan

- These functions all take angles (in radians) as a parameter
- To convert degrees to radians, multiply degrees by pi/180

- The number p is unavailable in VBA
- However, we know that tan(p/4) = 1
- Therefore, VBA can calculate the value of p by using the formula
- pi = 4 * Atn(1)
- If high precision is not required, you can use Const pi = 3.14159

- The Sin, Cos, and Tan functions take an angle (in radians) 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