VBA and Excel for Engineers and Scientists – Part 2

Variable Expressions and Math

Ok, so now you know what kinds of variables you can have, how to name them and how to declare them. Now let’s do something with them! As an engineer or scientist, most of the time we’ll be interested in performing some math operations on these variables, but later I’ll also go over some ways to manipulate text as well. In VBA terms, we accomplish this using expressions.

According to Excel’s VBA Help, an “expression” is a combination of keywords, operators, variables, and constants that yields a string, number or object. An expression can perform a calculation, manipulate characters, or test data.

Math Operators

Any engineer or geologist worth their salt will want to take some numbers and do something to them. Here are the basics:

• + Addition operator, ie. result = expression1 + expression2
• – Subtraction operator…you get the picture
• / The division operator – not to be confused with the integer division operator. This one returns a floating point number.
• * Multiplication operator
• \ Integer division operator. Performs division and returns only an integer result (no remainder or decimal places, ie. 5.43\1 returns 5)
• Mod The modulo operator. Performs division on two numbers and returns only the remainder. If either of the two numbers is a floating-point number, it is rounded to an integer value prior to the modulo operation. Example 1: 6 MOD 4 = 2, Example 2: 12 MOD 4 = 0
• ^ The exponentiation operator. Raises a number to the power of the exponent. Example: result = number ^ exponent
• Warning: As a new VBA programmer, be very careful with the division operator and the integer division operator. Picking the integer division operator on accident can create a logic error that may not be very easy to track down.

Order of Operation

As you would expect, you can group various operations using parentheses. You will need to keep in mind the order of operation which is outlined below. I’ll get to Comparison and Logical operators in a bit.

• Math operators
  • ^ Exponentiation
  • * / Division and multiplication (no precedence between the two)
  • \ Integer division
  • Modulo arithmetic
  • + – Addition and subtraction
• Concatenation operators (not exactly sure what these are)
• Comparison operators (ie. >, <, <>, >=, <=, =)
• Logical operators
  • Not
  • And
  • Or
  • Some other ones you probably won’t ever need

If the same operator is used multiple times on a line, it evaluates from left to right.

Other Math Functions

Ok, now VBA has some built-in math functions that you’ll need. Here are a few of the most common ones:

• Trigonometric (Trig) Functions
  • Cos()
  • Sin()
  • Tan()
  • Atn() – Arctangent
  • Radians()
  • Degrees()
• Abs() – Absolute value
• Log() – NATURAL log
• For Log base 10, do: sngLog10 = Log(sngX) / Log(10)

Warning: Trig functions can be tricky. Remember that like Excel, VBA trig functions work in radians. Also worth noting is the ATan function. It only returns values between -Pi/2 and Pi/2 radians. Note that there are no ArcSin and ArcCos functions. You’ll have to write your own, or download my modMath.bas file. Did I mention that I always hated Trig?

Excel Worksheet Functions

Sometimes in your VBA coding, you may find that the math functions VBA has aren’t enough, and you need to do something that you know you can do with an Excel worksheet formula. You’re in luck, VBA has a method for allowing you to call those "Excel Worksheet Functions” in your code. Here are a couple of simple examples.

Listing 8: Excel Worksheet Function Examples

dblKurtosis  = Application.WorksheetFunction.Kurt(val1, val2, val3, val4) sngPresentValue = Application.WorksheetFunction.PV(rate,nper,pmt,fv,type)

You can see that all you need to do is put the function name and arguments after the last period. For now, you need to pick ones that will accept arguments that are values as opposed to functions that only work on cell references since we won’t be learning about Range objects until the third article.