## 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 × 2^{y-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.

1 2 3 4 5 | Dim Number As Single Number = -3.402823E+38 'Number = -3.402823E+38 Number = 3.402823E+38 'Number = 3.402823E+38 |

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.

1 2 3 4 | Number = -3.402824E+38 'run time error, overflow Number = 3.402824E+38 'run time error, overflow |

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.

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 `Single`

can contain fractional values, with up to 7 decimal digits at most.

1 2 3 4 | Number = 1 / 3 'Number = 0.3333333 Number = 3.1415926535 'Number = 3.141593 |

### Default

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

.

1 2 3 | Dim Number As Single 'Number = 0 |

### 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.

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 |

1 2 3 4 5 6 | Dim Number As Single Number = 1! MsgBox VarType(1!) 'shows 4, which means 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`

.

### 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 x_{1} × 2^{-1} + x_{2} × 2^{-2} + ... + x_{n} × 2^{-n} where n is the number of bits that is reserved for the significand and x_{1}, x_{2}, ... x_{n} 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 y_{1} × 2^{0} + y_{2} × 2^{1} + ... + y_{m} × 2^{m-1} where m is the number of bits reserved for the exponent and y_{1}, y_{2}, ... y_{m} 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.

### 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 2_{32} 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 1 | Sign | 0 | 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 | 1 × 2^{-20} | = | 0.00000095367431640625 |

Bit 22 | Significand | 0 × 2^{-21} | = | 0 |

Bit 23 | Significand | 1 × 2^{-22} | = | 0.0000002384185791015625 |

Bit 24 | Significand | 0 × 2^{-23} | = | 0 |

Bit 25 | Exponent | 0 × 2^{0} | = | 0 |

Bit 26 | Exponent | 0 × 2^{1} | = | 0 |

Bit 27 | Exponent | 1 × 2^{2} | = | 4 |

Bit 28 | Exponent | 1 × 2^{3} | = | 8 |

Bit 29 | Exponent | 0 × 2^{4} | = | 0 |

Bit 30 | Exponent | 1 × 2^{5} | = | 32 |

Bit 31 | Exponent | 1 × 2^{6} | = | 64 |

Bit 32 | Exponent | 0 × 2^{7} | = | 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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Dim Number1 As Single Dim Number2 As Single Dim Number3 As Single Number1 = 4.444444 Number2 = 3.333333 Number3 = Number1 + 1.111111 'Number3 = 5.555555 Number3 = Number2 - Number1 'Number3 = -1.111111 Number3 = Number1 * 2 'Number3 = 8.888888 Number3 = Number1 * Number2 'Number3 = 14.81481 Number3 = Number1 / 4 'Number3 = 1.111111 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 ^ 99 '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 Single Faculty = 1 For Index = 1 To 20 Faculty = Faculty * Index Next Index 'Faculty = 2,432902E+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 `Single`

VBA has a conversion function `CSng()`

that will try to convert anything into a number of the type `Single`

.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Dim Number As Single Number = CSng(1234567) 'Number = 1234567 Number = CSng(123456789) 'Number = 1,234568E+08 Number = CSng(1.2E+34) 'Number = 1,2E+34 Number = CSng(1.23E+45) 'run time error, overflow Number = CSng(-1.23456) 'Number = -1,23456 Number = CSng(-1.23456789) 'Number = -1,234568 Number = CSng("12345678,9") 'Number = 1,234568E+07 Number = CSng("123,456") 'Number = 123,456 Number = CSng("123.456") 'Number = 1234568 Number = CSng("VBA") 'run time error, type mismatch |