## General information about the use of numbers in VBA

### Types of numbers in VBA

In VBA, every number has a type. VBA offers a range of number types to choose from.

Number type |
---|

`Byte` |

`Integer` |

`Long` |

`Single` |

`Double` |

`Currency` |

`Decimal` |

When you need a number, you need to choose the type first. This choice is made in the declaration.

1 2 3 4 5 6 7 8 9 | Dim Number1 as Byte Dim Number2 as Integer Dim Number3 as Long Dim Number4 as Single Dim Number5 as Double Dim Number6 as Currency Dim Number7 as Variant 'Dim Number7 as Decimal doesn't work. Decimal is special. |

### Range of values in VBA

The type of the number defines the values that the number can have.

Number type | |
---|---|

`Byte` | {0,1,...,255} |

`Integer` | {-32768, ..., -1, 0, 1, ..., 32767} |

`Long` | {-2147483648, ..., -1, 0, 1, ..., 2147483647} |

`Single` | {-3.402823e38, not all values in between are possible, 3.402823e38} |

`Double` | {-1.79769313486231e308, not all values in between are possible, 1.79769313486231e308} |

`Currency` | {-922337203685477.5808, ..., -0.0001, 0, 0.0001, ..., 922337203685477.5807} |

`Decimal` | {-79228162514264337593543950335, not all values in between are possible, 79228162514264337593543950335} |

If you try to assign a value to a number variable outside of the correct range, the number variable is said to *overflow*. In general, this will generate an error message.

The values that a number type can have are determined by the number of bytes occupied by the number type and the bit representation that is used.

### The number of bytes in VBA

For each number type, a number of bytes will be reserved in the memory, to store the value. In general, the more bytes that are reserved, the wider the range of values that the number type can have. Here is the list valid for VBA.

Number type | Number of bytes |
---|---|

`Byte` | 1 byte |

`Integer` | 2 bytes |

`Long` | 4 bytes |

`Single` | 4 bytes |

`Double` | 8 bytes |

`Currency` | 8 bytes |

`Decimal` | Probably 16 bytes |

Similar number types might exist in other programming languages. For example, the type `Integer`

exists in almost all programming languages. However, this does not imply that the number of bytes that is reserved for it needs to be the same. For instance, in VBA, the number type `Integer`

takes up 2 bytes while in VB.net `Integer`

occupies 4 bytes. Watch out for this. It can be annoying, for instance when working with APIs written in another programming language.

In VBA, the computer will *always* reserve the same number of bytes for a number type, independent of the value of the number. For example, if you declare a number of the type `Long`

, 4 bytes will be reserved, whether you put 0, 1, -12345 or 253729102 into it.

In VBA, there is no standard number type that uses a *variable* number of bytes. Suppose you need to add two numbers. The first number consists of one million fours (444... one million times) and the second number consists of one million fives (555... one million times). The result is the number that consists of one million nines (999... one million times). In VBA, with the above number types, it is *not* possible to make this addition. The numbers are simply too large. They don't fit into the standard number types. However, it *can* be done, also in VBA. In order to make the addition, you will first need to develop (or buy) VBA code to work with so-called arbitrarily large numbers. In contrast to the standard number types, the arbitrarily large numbers typically use a variable number of bytes. Or, they use a clever bit representation.

### The bit representation in VBA

The bit representation refers to the encoding of the value. The number of reserved bytes determines the number of available bits. Clever people have of thought of clever ways to use this limited amount of bits to cover as much values as possible. Hence, even though the number of bytes of two number types might be the same, due to a different bit representation, the two number types might cover a different range of values. For example, both the types `Long`

or `Single`

use 4 bytes, but the bit representation is very different. The table below shows the bit representation of the number 1.

Byte 1 | Byte 2 | Byte 3 | Byte 4 | |
---|---|---|---|---|

`Long` | `00000001` | `00000000` | `00000000` | `00000000` |

`Single` | `00000000` | `00000000` | `10000000` | `00111111` |

The bit presentation also affects the numercial properties.

### Numercial properties of the number types in VBA

Can have fractional part? | Are mathematical operations exact? | |
---|---|---|

`Byte` | No | Yes |

`Integer` | No | Yes |

`Long` | No | Yes |

`Single` | Yes, up to 7 decimal places | Not always |

`Double` | Yes, up to 15 decimal places | Not always |

`Currency` | Yes, always 4 decimal places | Yes |

`Decimal` | Yes, up to 28 decimal places | Yes |

`Single`

and `Double`

are called floating point number types. The bit representation of floating point numbers might not be exact. For example, you might think you are working with the value 3, but the bit representation of the floating point number actually represents the value 2.999999999999999 or the value 3.000000000000001. The consequence is that floating point arithmetic introduces the possibility of numercial errors in the result of the arithmetic operation. Don't worry too much about this. Floating point arithmetic has been well studied (it is not an easy topic) and computer implementations of floating point arithmetic today are standardized, aiming to minimize the possibility of numerical errors. Just try to keep in your mind that floating point arithmetic need not always be exact. It *can* lead to unexpected behavior of the VBA code and difficult to trace bugs.

### Why are fractional numbers hard?

Computers represent numbers using bits.

Non-fractional numbers can *always* be represented exactly, on the condition that enough bits are available. Any non- fractional number can be written as a sum of positive powers of two and a sign, i.e. (-1)^{z} (x_{1} × 2^{0} + x_{2} × 2^{1} + ... + x_{n} × 2^{n-1}) where z, x_{1}, x_{2}, ..., x_{n} are 0 or 1. Fractional numbers are represented in a similar way.

Fractional numbers are written as a sum of positive and *negative* powers of two and a sign, i.e. (-1)^{z} ((x_{1} × 2^{0} + x_{2} × 2^{1} + ... + x_{n} × 2^{n-1}) + (x_{0} × 2^{-1} + x_{-1} × 2^{-2} + ... + x_{-m} × 2^{-(m+1)})) where z, x_{-m}, ..., x_{-1}, x_{0}, x_{1}, x_{2}, ..., x_{n} are 0 or 1. Unlike non-fractional numbers, not every fractional value can be stored exactly using bits, even if an large supply of bits would be available. For example, the number 1.25 can be represented exactly as (1 × 2^{0}) + (0 × 2^{-1} + 1 × 2^{-2}). On the other hand, the number 1.26 cannot be represented exactly. One could approximate it using 10 bits by (1 × 2^{0}) + (0 × 2^{-1} + 1 × 2^{-2} + 0 × 2^{-3} + 0 × 2^{-4} + 0 × 2^{-5} + 0 × 2^{-6} + 1 × 2^{-7} + 0 × 2^{-8} + 1 × 2^{-9}) which corresponds to 0.259765625. The more bits are used, the better the approximation will become. However, it will never be exact.

### How to choose the type of a number?

In principle, you need to ask yourself only *one* question:

*"Can the number contain all the values that the VBA will assign to it?"*

Unfortunately, this question is hard to answer. You are not always sure what the user will try to do or how the code will be reused in other circumstances. Anyway, try to take a moment to reflect. It might save you some painful debugging...

### If you like, some gentle advice for VBA beginners

Often, two number types are sufficient. If the number contains fractional values, choose `Double`

, otherwise, choose `Long`

.

Number type |
---|

`Long` |

`Double` |

As you get more experienced or if the circumstances require so, you can refine your choice, when needed. Don't think you will be held back when restricting yourself to those two types. In JavaScript for instance, there is no choice: all numbers are of the type double. And JavaScript is used a lot with good success by many many programmers.

## Example: Choosing the number type is hard

Say you want to add the numbers from 1 to 100, both 1 and 100 included.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Public Sub WorkingWithNumbers1() Dim X As Integer X = 0 Dim Sum As Integer Sum = 0 For X = 1 To 100 Sum = Sum + X Next X MsgBox "The sum is " & CStr(Sum) & "." 'shows The sum is 5050 End Sub |

The code works fine. You can check that the sum is indeed 5050 (for instance, using the classical trick (1+100) x 100/2).

Now, say you want to add the numbers from 1 to 1000. Just change the following line.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Public Sub WorkingWithNumbers2() Dim X As Integer X = 0 Dim Sum As Integer Sum = 0 For X = 1 To 1000 Sum = Sum + X Next X MsgBox "The sum is " & CStr(Sum) & "." End Sub |

If you run the code, you will get an overflow error.

In iteration 256 of the `For`

loop, the code tries to add 256 to 32640 (which would give 32896). As, the maximum value for a number of the type `Integer`

is 32767 the variable `Sum`

overflows. The change from 100 to 1000 seems harmless, but it caused the code to crash. Let's solve the error.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Public Sub WorkingWithNumbers3() Dim X As Integer X = 0 Dim Sum As Long Sum = 0 For X = 1 To 1000 Sum = Sum + X Next X MsgBox "The sum is " & CStr(Sum) & "." 'shows The sum is 500500 End Sub |

The result is now correct: after the loop `Sum`

is 500500 (= (1+1000) x 1000/2).

Now change the code like this. Again, it looks like an innocent change.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Public Sub WorkingWithNumbers4() Dim X As Integer X = 0 Dim Sum As Long Sum = 0 For X = 1 To 1000 Sum = Sum + 0.5 Next X MsgBox "The sum is " & CStr(Sum) & "." 'shows The sum is 0. End Sub |

The code runs fine. However, `Sum`

is 0?? This result cannot be correct. Still, there was no error message. What has happened?

The problem lies in the fact that in the line `Sum = Sum + 0.5`

the VBA code tries to add a fractional value to a variable of the type `Long`

. This is not possible. Without asking you, VBA has converted the fractional value 0.5 to 0. Which explains the obtained result. Watch out for these so-called implicit conversions. They are everywhere in VBA. Admitted, they can be helpful, but, they can also a source of hard to find bugs.

Again the solution is to change the number type of the variable `Sum`

.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Public Sub WorkingWithNumbers5() Dim X As Integer X = 0 Dim Sum As Double Sum = 0 For X = XStart To XEnd Sum = Sum + 0.5 Next X MsgBox "The sum is " & CStr(Sum) & "." 'shows The sum is 500. End Sub |

Now the result is correct.

We could go on creating errors by making seemingly innocent changes. Keep in mind that choosing the right type for a number is hard, but, *very* important. Your skill will grow with experience.