VBA is unusual in that it provides two division operators, one for decimal division and one for integer division.
Decimal Division Operator (/)
Copy from VBA > Syntax > Operators
The forward slash operator performs decimal division
10 / 3 = 3.3333
Integer Division Operator (\)
The back slash operator performs integer division
This is a great way of dividing and rounding down in one simple operation
The datatype returned is a numeric type appropriate for the datatypes used
Before performing the division any floating-point values are converted to Long
10 \ 3 = 3
Mod Operator (mod)
You can find the remainder in Integer division by using the modulus operator (mod)
The Mod operator returns the remainder after division
8 Mod 3 = 2
This is extremely useful when you have a loop and you want to perform an action every nth times through a loop.
For icounter = 1 To 100
If icounter Mod 10 Then
'every tenth value
This is similar to the integer division except it only returns the remainder
10 mod 3 = 1
This is actually equivalent to the following maths formula
a - (b * (a \ b))
a - (b * Fix(a / b))
You can also get overflow errors in VBA using the Mod operator with very large numbers. For example,
Public Sub Mod_Error()
Dim Number As Double
Dim Divisor As Double
Dim Result As Double
Number = 2 ^ 31
Divisor = 7
Result = Number Mod Divisor ' Overflow error here.
Any time you use the floating-point division operator (/), you're performing floating-point division, and your return value will be of type Double.
This is true whether your dividend and divisor are integer, floating-point, or fixed-point values. It's true whether or not your result has a decimal portion.
For example, running the following code from the Immediate window prints "Double":
So does this code, even though the result is an integer:
Since all floating-point division returns a floating-point value, you can't ever be certain that your result is accurate to every decimal place, even if you're performing division on Decimal or Currency values. There will always be an inherent possibility of rounding errors, although they're likely to be small.
If you're dividing integers, or if you don't care about the decimal portion of the result, you can use the integer division operator (\).
Integer division is faster than floating-point division, and the result is always an Integer or Long value, either of which requires less memory than a Double value.
For example, running this code from the Immediate window prints "Integer":
The division operator here is taking an integer value and dividing by a decimal.
Dim n as double
n = 19.333333
MsgBox CInt( n * 4 ) / 4
The forward slash (/) returns a floating point answer
12 / 5 = 2.4
The answer is returned as a Double.
If you are assigning the result to an Integer, the decimal part will be lost.
This must be explicitely cast if Option Strict is turned on since this is a narrowing cast.
This operator divides one number by another and the datatype that is returned depends on the types of values.
This is an explicit widening conversion.
10 / 3 will be a single
Division (\) with Integer Result
The back slash (\) peforms integer division.
12 / 5 = 2
The answer is returned as an Integer.
When you convert a nonintegral value to an integral type, the integer conversion functions (CByte, CInt, CLng, CSByte, CShort, CUInt, CULng, and CUShort) remove the fractional part and round the value to the closest integer.
If the fractional part is exactly 0.5, the integer conversion functions round it to the nearest even integer. For example, 0.5 rounds to 0, and 1.5 and 2.5 both round to 2.
This is sometimes called banker's rounding, and its purpose is to compensate for a bias that could accumulate when adding many such numbers together.
CInt and CLng differ from the Int and Fix functions, which truncate, rather than round, the fractional part of a number.
Also, Fix and Int always return a value of the same data type as you pass in.
When you work with floating-point numbers, remember that they do not always have a precise representation in memory. This could lead to unexpected results from certain operations, such as value comparison and the Mod operator.
© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited