1 / 12

# IE 212: Computational Methods for Industrial Engineering - PowerPoint PPT Presentation

IE 212: Computational Methods for Industrial Engineering. Lecture Notes #4 Appendix: VBA Functions. Dr. J. David Porter Summer 2014. String Functions. There are several string functions available in VBA These functions are useful to inspect and manipulate strings

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' IE 212: Computational Methods for Industrial Engineering' - jaafar

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 - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

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

String Functions (cont.)

• 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

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

• The Val function also extracts a numerical value from a string

• The input for this function is an expression

• Val(expression)

Conversion Functions (cont.)

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

Conversion Functions (cont.)

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

• 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

Abs and Sqr Functions

• 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

Int and Rnd Functions

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

Exp and Log Function

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