VBA Number Types: Double

General information about "Double"

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.

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.

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.

Fractional values

A number of the type Double can contain fractional values, with up to 15 decimal digits at most.


On declaration, by default, VBA assigns the value 0 to the number of the type Double.

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.

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.


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.

Number of bits reserved for significandn2352
Number of bits reserved for exponentm811

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 1Sign10 means +, 1 means -
Bit 2Significand0 × 2-1=0
Bit 3Significand1 × 2-2=0.25
Bit 4Significand1 × 2-3=0.125
Bit 5Significand1 × 2-4=0.0625
Bit 6Significand1 × 2-5=0.03125
Bit 7Significand0 × 2-6=0
Bit 8Significand0 × 2-7=0
Bit 9Significand0 × 2-8=0
Bit 10Significand0 × 2-9=0
Bit 11Significand0 × 2-10=0
Bit 12Significand0 × 2-11=0
Bit 13Significand0 × 2-12=0
Bit 14Significand0 × 2-13=0
Bit 15Significand0 × 2-14=0
Bit 16Significand0 × 2-15=0
Bit 17Significand0 × 2-16=0
Bit 18Significand0 × 2-17=0
Bit 19Significand0 × 2-18=0
Bit 20Significand1 × 2-19=0.0000019073486328125
Bit 21Significand0 × 2-20=0
Bit 22Significand0 × 2-21=0
Bit 23Significand0 × 2-22=0
Bit 24Significand0 × 2-23=0
Bit 25Significand0 × 2-24=0
Bit 26Significand1 × 2-25=0.0000000298023223876953125
Bit 27Significand0 × 2-26=0
Bit 28Significand0 × 2-27=0
Bit 29Significand0 × 2-28=0
Bit 30Significand0 × 2-29=0
Bit 31Significand0 × 2-30=0
Bit 32Significand0 × 2-31=0
Bit 33Significand0 × 2-32=0
Bit 34Significand0 × 2-33=0
Bit 35Significand0 × 2-34=0
Bit 36Significand0 × 2-35=0
Bit 37Significand0 × 2-36=0
Bit 38Significand0 × 2-37=0
Bit 39Significand0 × 2-38=0
Bit 40Significand0 × 2-39=0
Bit 41Significand0 × 2-40=0
Bit 42Significand0 × 2-41=0
Bit 43Significand0 × 2-42=0
Bit 44Significand0 × 2-43=0
Bit 45Significand0 × 2-44=0
Bit 46Significand0 × 2-45=0
Bit 47Significand0 × 2-46=0
Bit 48Significand0 × 2-47=0
Bit 49Significand1 × 2-48=0.000000000000003552713678800500929355621337890625
Bit 50Significand0 × 2-49=0
Bit 51Significand0 × 2-50=0
Bit 52Significand0 × 2-51=0
Bit 53Significand0 × 2-52=0
Bit 54Exponent0 × 20=0
Bit 55Exponent0 × 21=0
Bit 56Exponent0 × 22=0
Bit 57Exponent1 × 23=4
Bit 58Exponent1 × 24=8
Bit 59Exponent0 × 25=0
Bit 60Exponent1 × 26=32
Bit 61Exponent1 × 27=64
Bit 62Exponent0 × 28=0
Bit 63Exponent0 × 29=0
Bit 64Exponent1 × 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


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.

This VBA code will crash because the result is larger than 3.402823e38.

Or, more subtle. This VBA code works fine, but the result is not exact. The correct result is 2432902008176640000.

Of course, division by zero is forbidden. This VBA code will also crash.

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.