1 / 12

IE 212: Computational Methods for Industrial Engineering

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

jaafar
Télécharger la présentation

IE 212: Computational Methods for Industrial Engineering

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. IE 212: Computational Methods for Industrial Engineering Lecture Notes #4 Appendix: VBA Functions Dr. J. David Porter Summer 2014

  2. String Functions • 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)

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

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

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

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

  7. VBA Math Functions • 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

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

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

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

  11. Trigonometric Functions • 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

  12. Sin, Cos, and Tan Functions • 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

More Related