Doing calculations in VBA
This page lists the elements that can be used in calculations in VBA. Primarily there are the arithmetic operators. In addition to those you may use functions available in VBA and possibly in the Office application - or write your own. The data, the operators operands or the function's argument, may be literal (a number), variables, constants or object properties of numeric type, or other expressions such as functions that return numbers.
- Arithmetic Operators
- Parentheses
- Division and rounding
- User defined functions
- Math functions
- Financial functions
- Using Excel WorksheetFunctions
- Using Code VBA IntelliSense for calculations
Arithmetic Operators
In the table below you see some of the mathematical operations such as addition, subtraction, multiplication, division, exponentiation. To write code for mathematical operations, we use various arithmetic operators. This operators are very similar to the normal arithmetic operators. The plus and minus operators are the same. Note that the multiplication operator uses the * symbol and the division operator uses the / symbol.
| Operator | Description | Example A=2, B=10 |
| + | Adds two operands | A + B will give 12 |
| - | Subtracts second operand from the first | A - B will give -8 |
| * | Multiply both operands | A * B will give 20 |
| / | Divide numerator by denumerator | B / A will give 5 |
| ^ | Exponentiation | B ^ A will give 100 |
Parentheses
It is important to define the order of operations with parentheses. With no order specified VBA handles the operations as in normal arithmatic. Examples:
6 + 4 * 7 - 3 equals 6 + 28 - 3 (since multiplication goes first) gives 31. (6 + 4) * (7 - 3) is 10 * 4 gives 40. ((6 + 4) * 7)) - 3 is 10 * 7 - 3 gives 67.
Division and rounding
Answer = 11 / 2 'gives 5.5
In this example Answer is set as Integer. If you try to set to a fraction, the number is rounded up or down to the nearest integer value. If the number is equally close to two integer values, the value is rounded to the nearest even integer. This behavior minimizes rounding errors that result from consistently rounding a midpoint value in a single direction. The following code shows examples of rounding.
Answer = 5.9 => 6
Answer = 5.5 => 6 'Visual Basic uses banker"s rounding (toward nearest even number).'
Answer = 4.5 => 4 ' If you want to round up, use half adjusting. Add 0.5 to the number to be rounded up and use the Round() function'
Answer = 9 + 0.5 / 2
The \ operator - backslash - does integer division.
Answer = 10 / 4 'gives 2.5'
Answer = 10 \ 4 'gives 2'
Answer = 5.423 / 1 'gives 5.423'
Answer = 5.423 \ 1 'gives 5'A related operator is the MOD operator. It returns the remainder. Examples:
6 Mod 4 = 2
12 Mod 4 = 0
User defined functions
Although VBA and Excel already provide many functions, it is common to write your own for your specific requirements. For example you may want to have a function that calculates SalesTax:
Function SalesTax(TotalInvoice As Double, TaxPercentage As Double)
SalesTax = TotalInvoice - TotalInvoice / (1 + TaxPercentage)
End Function
More info: Call a function
Math functions
The Abs function Absolute value
From the Code VBA toolbar, select VBA » Math » Abs »
dblAbs = Abs(50.3) ' Returns 50.3.'
dblAbs = Abs(-50.3) ' Returns 50.3.'
Using Excel WorksheetFunctions
In Excel there is a very large collection of built-in functions you may also want to use. As an example, WorksheetFunction.Min gives the minimum of the specified parameter:
WorksheetFunction.Min(Arg1:=4,arg2:=6) 'returns 4'
WorksheetFunction.Min(4.4,2.6)'returns 2.6'
The complete list of WorksheetFunctions can be found here.
Using Code VBA IntelliSense for calculations
You can speed up coding and use variables and built-in functions using Code VBA IntelliSense.
After the =, or a mathematical operator use Shift-Space to start a menu that gives access to the relevant numeric variables and the buit-in functions that return a numeric.
