General information about "Long"
From -1.79769313486231e308 to 1.79769313486231e308
In VBA, a number of the type Double is a number that can be written in the format defined by the binary64 IEEE Standard for Floating-Point Arithmetic. A number of the type Double can be formatted like ±1.x × 2y-1023 where "1.x" is a value between 1 (included) and 2 (not included) and "y" is an integer value between 1 and 254. The highest possible number that can be written in this format is 1.79769313486231e308 and the lowest is -1.79769313486231e308. The following VBA code works.
1 2 3 4 5 | Dim Number As Double Number = -1.79769313486231E+308 'Number = -1.79769313486231E+308 Number = 1.79769313486231E+308 'Number = 1.79769313486231E+308 |
When you assign a value larger than 1.79769313486231e308 or smaller than -1.79769313486231e308, the VBA editor code will not compile and generate a syntax error.
1 2 3 4 | Number = -1.79769313486232E+308 'compile error, syntax Number = 1.79769313486232E+308 'compile error, syntax |
This does not mean that a number of the type Double can take all values between -1.79769313486231e+308 and 1.79769313486231e+308. Only a subset of the numbers between -1.79769313486231e+308 to 1.79769313486231e+308 is a number of the type Double, i.e. the subset of numbers that match the format defined by the binary64 IEEE Standard for Floating-Point Arithmetic. If you put a number into a the type Double that does not match the format, VBA will automatically transform it to a close number that does match the format.
1 2 3 4 | Number = 0.25 'Number is 0.25 (exactly) Number = 0.26 'Number is 0.2599999904632568359375 but displays as 0.26 |
Fractional values
A number of the type Double can contain fractional values, with up to 15 decimal digits at most.
1 2 3 | Number = 1 / 3 'Number = 0.333333333333333 |
Default
On declaration, by default, VBA assigns the value 0 to the number of the type Double.
1 2 3 | Dim Number As Double 'Number = 0 |
Type declaration character
The type declaration character for Long is #. If there is no type declaration character and the number is in the range of Double and the number is not in the range of the type Long, then, the type of the number is set to Double by default. This is true even if the number would fall in the range of Single.
1 2 3 4 5 6 7 | Dim Number As Double Number = 1# MsgBox VarType(1#) 'shows 5, which means Double MsgBox VarType(1.234) 'shows 5, which means Double |
IEEE 754
IEEE 754 defines a standard for floating point implementation. Its first version, IEEE 754-1985, dates back to 1985. It has been developped to standardize floating point implementations. Since then, it has been almost universally adopted. The current version of the standard is IEEE 754-2008. Also VBA uses the IEEE 754 standard for the number types Single and Double.
Format
Amongst other things (like mathematical operations, rounding, division by zero etc.), the IEEE 754 standard also defines the format of a floating point number. In IEEE 754, a floating point number is a number that can be written as (-1)z × (1 + 0.x) × 2(y-b). The value of z is 0 or 1. If z = 1, the number is negative. The factor (1 + 0.x) is called the normalized significand (or normalized mantissa). The value of x is equal to x1 × 2-1 + x2 × 2-2 + ... + xn × 2-n where n is the number of bits that is reserved for the significand and x1, x2, ... xn are 0 or 1. For Single, n is 23 and for Double, n is 52. The normalized significand is a number between 1 (included) and 2 (not included). The larger n, the more precise that the significand can be. The power (y-b) is called the biased exponent. The value of y is equal to y1 × 20 + y2 × 21 + ... + ym × 2m-1 where m is the number of bits reserved for the exponent and y1, y2, ... ym are 0 or 1. For Single, m is 8 and for Double, m is 11. The number b is called the bias. For Single, b is 127 and for Double, b is 1023.
Single | Double | ||
|---|---|---|---|
| Number of bits reserved for significand | n | 23 | 52 |
| Number of bits reserved for exponent | m | 8 | 11 |
| Bias | b | 127 | 1023 |
Format, special cases
The IEEE 754 standard has foreseen some special cases. Cases where all the bits of the exponent are 0 or all the bits of the exponent are 1 are reserved for special values like +infinity or -infinity" or +0 and -0 and NaN which is short for "not a number". This implies that, for Single, the smallest possible value for the exponent is -126 (= 1 - 127) and the largest 127 (= 254 - 127). For Double, the smallest possible value for the exponent is -1022 (= 1 - 1023) and the largest 1023 (= 2046 - 1023).
Try it yourself
An excellent website to experiment with the IEEE 754 standard can be found here. As an interesting example, enter the value 0.25. You will see that the floating point representation exactly equals 0.25. Now try 0.3. The floating point representation equals 0.300000011920928955078125 which is close, but not exactly equal to 0.3. Not all numbers have an exact floating point representation. This is why mathematical operations using Single and Double are not always exact and you need to watch out for rounding errors.
Eight memory bytes
A number of the type Double takes up eight bytes in the memory. In a byte, there are 8 bits, each of which can take the value 0 or 1. Hence, four bytes can have 264 possible bit representations. 52 bits are taken by the significand, 11 bits are taken by the exponent and 1 bit is taken by the sign. Each of these 18446744073709551616 (18 quintillion 446 quadrillion 744 trillion 73 billion 709 million 551 thousand 616) possible bit representations corresponds to one value of a Double.
In IEEE 754, a number of the type Double can be written as (-1)z × (1 + 0.x) × 2(y-1023). To illustrate, one possible bit representation could be z - x - y, or, in words, sign bit - significand bits - exponent bits. The VBA bit representation might be slightly different, but the logic is the same. Here is how to read a bit representation. For instance, let's take the bit representation 00111100 00000000 00010000 10000000 00000000 00000001 00000000 00110110.
| Bit 1 | Sign | 1 | 0 means +, 1 means - | |
| Bit 2 | Significand | 0 × 2-1 | = | 0 |
| Bit 3 | Significand | 1 × 2-2 | = | 0.25 |
| Bit 4 | Significand | 1 × 2-3 | = | 0.125 |
| Bit 5 | Significand | 1 × 2-4 | = | 0.0625 |
| Bit 6 | Significand | 1 × 2-5 | = | 0.03125 |
| Bit 7 | Significand | 0 × 2-6 | = | 0 |
| Bit 8 | Significand | 0 × 2-7 | = | 0 |
| Bit 9 | Significand | 0 × 2-8 | = | 0 |
| Bit 10 | Significand | 0 × 2-9 | = | 0 |
| Bit 11 | Significand | 0 × 2-10 | = | 0 |
| Bit 12 | Significand | 0 × 2-11 | = | 0 |
| Bit 13 | Significand | 0 × 2-12 | = | 0 |
| Bit 14 | Significand | 0 × 2-13 | = | 0 |
| Bit 15 | Significand | 0 × 2-14 | = | 0 |
| Bit 16 | Significand | 0 × 2-15 | = | 0 |
| Bit 17 | Significand | 0 × 2-16 | = | 0 |
| Bit 18 | Significand | 0 × 2-17 | = | 0 |
| Bit 19 | Significand | 0 × 2-18 | = | 0 |
| Bit 20 | Significand | 1 × 2-19 | = | 0.0000019073486328125 |
| Bit 21 | Significand | 0 × 2-20 | = | 0 |
| Bit 22 | Significand | 0 × 2-21 | = | 0 |
| Bit 23 | Significand | 0 × 2-22 | = | 0 |
| Bit 24 | Significand | 0 × 2-23 | = | 0 |
| Bit 25 | Significand | 0 × 2-24 | = | 0 |
| Bit 26 | Significand | 1 × 2-25 | = | 0.0000000298023223876953125 |
| Bit 27 | Significand | 0 × 2-26 | = | 0 |
| Bit 28 | Significand | 0 × 2-27 | = | 0 |
| Bit 29 | Significand | 0 × 2-28 | = | 0 |
| Bit 30 | Significand | 0 × 2-29 | = | 0 |
| Bit 31 | Significand | 0 × 2-30 | = | 0 |
| Bit 32 | Significand | 0 × 2-31 | = | 0 |
| Bit 33 | Significand | 0 × 2-32 | = | 0 |
| Bit 34 | Significand | 0 × 2-33 | = | 0 |
| Bit 35 | Significand | 0 × 2-34 | = | 0 |
| Bit 36 | Significand | 0 × 2-35 | = | 0 |
| Bit 37 | Significand | 0 × 2-36 | = | 0 |
| Bit 38 | Significand | 0 × 2-37 | = | 0 |
| Bit 39 | Significand | 0 × 2-38 | = | 0 |
| Bit 40 | Significand | 0 × 2-39 | = | 0 |
| Bit 41 | Significand | 0 × 2-40 | = | 0 |
| Bit 42 | Significand | 0 × 2-41 | = | 0 |
| Bit 43 | Significand | 0 × 2-42 | = | 0 |
| Bit 44 | Significand | 0 × 2-43 | = | 0 |
| Bit 45 | Significand | 0 × 2-44 | = | 0 |
| Bit 46 | Significand | 0 × 2-45 | = | 0 |
| Bit 47 | Significand | 0 × 2-46 | = | 0 |
| Bit 48 | Significand | 0 × 2-47 | = | 0 |
| Bit 49 | Significand | 1 × 2-48 | = | 0.000000000000003552713678800500929355621337890625 |
| Bit 50 | Significand | 0 × 2-49 | = | 0 |
| Bit 51 | Significand | 0 × 2-50 | = | 0 |
| Bit 52 | Significand | 0 × 2-51 | = | 0 |
| Bit 53 | Significand | 0 × 2-52 | = | 0 |
| Bit 54 | Exponent | 0 × 20 | = | 0 |
| Bit 55 | Exponent | 0 × 21 | = | 0 |
| Bit 56 | Exponent | 0 × 22 | = | 0 |
| Bit 57 | Exponent | 1 × 23 | = | 4 |
| Bit 58 | Exponent | 1 × 24 | = | 8 |
| Bit 59 | Exponent | 0 × 25 | = | 0 |
| Bit 60 | Exponent | 1 × 26 | = | 32 |
| Bit 61 | Exponent | 1 × 27 | = | 64 |
| Bit 62 | Exponent | 0 × 28 | = | 0 |
| Bit 63 | Exponent | 0 × 29 | = | 0 |
| Bit 64 | Exponent | 1 × 210 | = | 0 |
corresponds to
(-1)1 × (1 + 0.25 + 0.125 + 0.0625 + 0.03125 + 0.0000019073486328125 + 0.000000000000003552713678800500929355621337890625) × 2(4 + 8 + 32 + 64 + 1024 - 1023)
= -1 × 1.468751907348636365213678800500929355621337890625 × 2(109)
= -953274489311670101148804923260928
which is displayed as
-9.5327448931167e+32.
Doing +,-,/ etc.
The IEEE 754 standard also explains how to do the floating point calculations. Floating point calculations may suffer from rounding errors. The main cause of these rounding errors is that the calculations are made using the number that exactly matches the bit representation. And, as illustrated above, for floating point numbers, the bit representation does not always exactly match the number that is assigned or displayed.
In practice, don't worry too much about this. The IEEE 754 standard is excellent and, in general, the floating point calculations will yield the correct result. Now and then however, you may come across unexpected behavior in your VBA code due to a rounding error. But, in our experience, it is quite exceptional.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Dim Number1 As Double Dim Number2 As Double Dim Number3 As Double Number1 = 4.44444444444 Number2 = 3.33333333333 Number3 = Number1 + 1.11111111111 'Number3 = 5.55555555555 Number3 = Number2 - Number1 'Number3 = -1.11111111111 Number3 = Number1 * 2 'Number3 = 8.88888888888 Number3 = Number1 * Number2 'Number3 = 14.8148148147852 Number3 = Number1 / 4 'Number3 = 1.11111111111 Number3 = Number1 \ 2 'Number3 = 2 Number3 = Number1 Mod 100 'Number3 = 4 |
This VBA code will crash because the result is larger than 3.402823e38.
1 2 3 | Number3 = Number1 ^ 999 'run time error, overflow |
Or, more subtle. This VBA code works fine, but the result is not exact. The correct result is 2432902008176640000.
1 2 3 4 5 6 7 8 9 | 'calculate n! Dim Index As Long Dim Faculty As Double Faculty = 1 For Index = 1 To 20 Faculty = Faculty * Index Next Index 'Faculty = 2,43290200817664E+18 |
Of course, division by zero is forbidden. This VBA code will also crash.
1 2 3 | Number3 = Number1 / 0 'run time error, division by zero |
If you want to know more about floating point arithmetic, the paper What Every Computer Scientist Should Know About Floating-Point Arithmetic by David Goldberg is an excellent starting point.
Conversion to Double
VBA has a conversion function CDbl() that will try to convert anything into a number of the type Double.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Dim Number As Double Number = CDbl(1234567) 'Number = 1234567 Number = CDbl(123456789) 'Number = 1,234568E+08 Number = CDbl(1.2E+34) 'Number = 1,2E+34 Number = CDbl(1.23E+45) 'run time error, overflow Number = CDbl(-1.23456) 'Number = -1,23456 Number = CDbl(-1.23456789) 'Number = -1,234568 Number = CDbl("12345678,9") 'Number = 1,234568E+07 Number = CDbl("123,456") 'Number = 123,456 Number = CDbl("123.456") 'Number = 1234568 Number = CDbl("VBA") 'run time error, type mismatch |