VBA Number Types: Introduction


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.

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.

Image showing mapping the overflow error in VBA

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 typeNumber of bytes
Byte1 byte
Integer2 bytes
Long4 bytes
Single4 bytes
Double8 bytes
Currency8 bytes
DecimalProbably 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 1Byte 2Byte 3Byte 4
Long00000001000000000000000000000000
Single00000000000000001000000000111111

The bit presentation also affects the numercial properties.

Numercial properties of the number types in VBA

Can have fractional part?Are mathematical operations exact?
ByteNoYes
IntegerNoYes
LongNoYes
SingleYes, up to 7 decimal placesNot always
DoubleYes, up to 15 decimal placesNot always
CurrencyYes, always 4 decimal placesYes
DecimalYes, up to 28 decimal placesYes

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 (x1 × 20 + x2 × 21 + ... + xn × 2n-1) where z, x1, x2, ..., xn 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 ((x1 × 20 + x2 × 21 + ... + xn × 2n-1) + (x0 × 2-1 + x-1 × 2-2 + ... + x-m × 2-(m+1))) where z, x-m, ..., x-1, x0, x1, x2, ..., xn 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 × 20) + (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 × 20) + (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.

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.

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

Image showing mapping the overflow error in VBA

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.

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.

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.

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.