ie 212 computational methods for industrial engineering
Skip this Video
Download Presentation
IE 212: Computational Methods for Industrial Engineering

Loading in 2 Seconds...

play fullscreen
1 / 12

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

  • Uploaded on

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

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

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

IE 212: Computational Methods for Industrial Engineering

Lecture Notes #4 Appendix:

VBA Functions

Dr. J. David Porter

Summer 2014

string functions
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)
string functions cont
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
conversion functions
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)
conversion functions cont
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 cont1
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”
vba math functions
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
abs and sqr functions
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
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
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
trigonometric functions
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
sin cos and tan functions
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