VBA Number Types: Single


General information about "Single"

From -3.402823e38 to 3.402823e38

In VBA, a number of the type Single is a number that can be written in the format defined by the binary32 IEEE Standard for Floating-Point Arithmetic. A number of the type Single can be formatted like ±1.x × 2y-127 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 3.402823e38 and the lowest is -3.402823e38. The following VBA code works.

When you assign a value larger than 3.402823E+38 or smaller than -3.402823E+38, the VBA code will crash. At run time, it will generate an overflow error.

This does not mean that a number of the type Single can take all values between -3.402823e38 and 3.402823e38. Only a subset of the numbers between -3.402823e38 to 3.402823e38 is a number of the type Single, i.e. the subset of numbers that match the format defined by the binary32 IEEE Standard for Floating-Point Arithmetic. If you put a number into a the type Single 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 Single can contain fractional values, with up to 7 decimal digits at most.

Default

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

Type declaration character

The type declaration character for Single is !. Single is never the default type declaration. Even if the number falls inside the range of Single, the type of the number is set to Double by default.

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.

SingleDouble
Number of bits reserved for significandn2352
Number of bits reserved for exponentm811
Biasb1271023

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.

Four memory bytes

A number of the type Single takes up four 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 232 possible bit representations. 23 bits are taken by the significand, 8 bits are taken by the exponent and 1 bit is taken by the sign. Each of these 4294967296 (4 billion 294 million 967 thousand 296) possible bit representations corresponds to one value of a Single.

In IEEE 754, a number of the type Single can be written as (-1)z × (1 + 0.x) × 2(y-127). 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 00011010 00110110.

Bit 1Sign00 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 21Significand1 × 2-20=0.00000095367431640625
Bit 22Significand0 × 2-21=0
Bit 23Significand1 × 2-22=0.0000002384185791015625
Bit 24Significand0 × 2-23=0
Bit 25Exponent0 × 20=0
Bit 26Exponent0 × 21=0
Bit 27Exponent1 × 22=4
Bit 28Exponent1 × 23=8
Bit 29Exponent0 × 24=0
Bit 30Exponent1 × 25=32
Bit 31Exponent1 × 26=64
Bit 32Exponent0 × 27=0

corresponds to

(-1)0 × (1 + 0.25 + 0.125 + 0.0625 + 0.03125 + 0.0000019073486328125 + 0.00000095367431640625 + 0.0000002384185791015625) × 2(4 + 8 + 32 + 64 - 127)
= 1 × 1.4687530994415283203125 × 2(-19)
= 0.00000280142421615892089903354644775390625

which is displayed as

2.8014242e-6.

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 Single

VBA has a conversion function CSng() that will try to convert anything into a number of the type Single.