General information about "Integer"
From -32768 to 32767
In VBA, a number of the type Integer is a number that can contain values in {-32768,...,-1,0,1,..., 32767}.
1 2 3 | Dim Number As Integer Number = 1000 |
The following VBA code works.
1 2 3 | Number = -32768 'Number = -32768 Number = 32767 'Number = 32767 |
When you assign a value outside of the range {-32768,...,-1,0,1,..., 32767}. the VBA code will crash. At run time, it will generate an overflow error.
1 2 3 | Number = -32769 'run time error, overflow Number = 32768 'run time error, overflow |
Hexadecimal assigment
You can use hexadecimal assignment in VBA.
1 2 3 4 5 | Dim Number As Integer Number = 1234 'Number = 1234 Number = &H1234 'Number = 4660 |
No fractional values
A number of the type Integer cannot contain fractional values, like 3.1415. Be careful, because of an implicit conversion, this VBA code does work.
1 2 | Number = 3.1415 'Number = 3 |
Default
On declaration, by default, VBA assigns the value 0 to the number of the type Integer.
1 2 3 | Dim Number As Integer 'Number = 0 |
Type declaration character
There is no type declaration character for Integer. If the number is in the range {-32768,...,-1,0,1,..., 32767}, the type of the number is set to Integer by default.
1 2 3 4 5 | Dim Number As Integer Number = 1 MsgBox VarType(1) 'shows 2, which means Integer |
Two memory bytes
A number of the type Integer takes up two bytes in the memory. In a byte, there are 8 bits, each of which can take the value 0 or 1. Hence, two bytes can have 2^16 possible bit representations. 215 of them are negative, 215-1 of them are positive, 1 of them is zero. Each of these 65536 possible bit representations corresponds to one value in {-32768,...,-1,0,1,..., 32767}. One bit serves as the sign bit.
To illustrate, one possible bit representation could be as follows. The VBA bit representation might be slightly different, but the logic is the same.
| Bits | Value | |||
|---|---|---|---|---|
| 00000000 | 00000000 | 0 | ||
| 00000000 | 00000001 | 1 | ||
| 00000000 | 00000010 | 2 | ||
| ... | ||||
| 01111111 | 11111110 | 32766 | ||
| 01111111 | 11111111 | 32767 | ||
| 10000000 | 00000001 | -1 | ||
| 10000000 | 00000010 | -2 | ||
| ... | ||||
| 11111111 | 11111110 | -32767 | ||
| 11111111 | 11111111 | -32768 |
Here is how to read the bit representation. For instance, the bit representation 00110010 00110110
| Bit 1 | sign bit | 0 means +, 1 means - | |
| Bit 2 | 0 x 16384 | = | 0 |
| Bit 3 | 1 x 8192 | = | 8192 |
| Bit 4 | 1 x 4096 | = | 4096 |
| Bit 5 | 0 x 2048 | = | 0 |
| Bit 6 | 0 x 1024 | = | 0 |
| Bit 7 | 1 x 512 | = | 512 |
| Bit 8 | 0 x 256 | = | 0 |
| Bit 9 | 0 x 128 | = | 0 |
| Bit 10 | 0 x 64 | = | 0 |
| Bit 11 | 1 x 32 | = | 32 |
| Bit 12 | 1 x 16 | = | 16 |
| Bit 13 | 0 x 8 | = | 0 |
| Bit 14 | 1 x 4 | = | 4 |
| Bit 15 | 1 x 2 | = | 2 |
| Bit 16 | 0 x 1 | = | 0 |
corresponds to 12854 (= 2 + 4 + 16 + 32 + 512 + 4096 + 8192).
Doing +,-,/ etc.
You can make additions, subtractions, multiplications and so on. Numercial operations are exact, provided that you stay within the range {-32768,...,-1,0,1,...,32767}.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Dim Number1 As Integer Dim Number2 As Integer Dim Number3 As Integer Number1 = 4444 Number2 = 3333 Number3 = Number1 + 1111 'Number3 = 5555 Number3 = Number2 - Number1 'Number3 = -1111 Number3 = Number1 * 2 'Number3 = 8888 Number3 = Number1 / 4 'Number3 = 1111 Number3 = Number1 \ 10 'Number3 = 444 Number3 = Number1 Mod 100 'Number3 = 44 |
This VBA code will crash because the result is outside the range {-32768,..., 32767}.
1 2 3 | Number3 = Number1 ^ 2 'run time error, overflow Number3 = Number2 + 32000 'run time error, overflow |
Or, more subtle, and more dangerous.
1 2 3 4 5 6 | Dim Index As Integer Number3 = 0 For Index = 1 To 100 Number3 = Number3 + Number1 'run time error, overflow 'when Index = 8, run time error, overflow Next Index |
Of course, division by zero is forbidden. This VBA code will also crash.
1 2 | Number3 = Number1 / 0 'run time error, division by zero |
Be careful when doing numerical operations. This VBA code does not crash.
1 2 3 4 5 6 7 8 9 10 11 12 13 | Dim Number1 As Integer Dim Number2 As Integer Dim Number3 As Integer Number1 = 90.5 'Number1 = 90 Number2 = 170.8 'Number1 = 170 Number3 = Number1 * 2.8 'Number3 = 252 Number3 = Number1 / 5.5 'Number3 = 16 Number3 = Number1 / 5.9 'Number3 = 15 Number3 = Number1 / 7 'Number3 = 13 Number3 = Number1 / 99999 'Number3 = 0 |
The range {-32768,...,-1,0,1,...,32767} is not very wide. Even for simple numerical operations, like augmenting a counter with 1 in a loop, the range risks being too small. If you don't think about this when coding - which is normal - it may lead to avoidable and hard to find bugs.
Conversion to Integer
VBA has a conversion function CInt() that will try to convert anything into a number of the type Integer.
1 2 3 4 5 6 7 8 9 10 11 | Dim Number As Integer Number = CInt(12345) 'Number = 12345 Number = CInt(123456) 'run time error, overflow Number = CInt(-12345) 'Number = -12345 Number = CInt(12345.6) 'Number = 12346 Number = CInt("12345,6") 'Number = 12346 Number = CInt("123456") 'run time error, overflow Number = CInt("VBA") 'run time error, type mismatch |
The function CInt() is not the same as the function Int(). The function Int() returns the largest non-fractional number that is lower or equal to the argument.
1 2 3 4 5 | Number = Int(12345.6) 'Number = 12345 Number = Int(-12345.6) 'Number = -12346 Number = CInt(12345.6) 'Number = 12346 Number = CInt(-12345.6) 'Number = -12346 |
Bit operations
VBA supports a limited number of bit operations. Better not to use them.
1 2 3 4 5 6 7 8 9 | Dim Number1 As Integer Dim Number2 As Integer Number1 = 78 'Number1 = 00000000 01001110, = 78 Number2 = Number1 And 13 'Number1 = 00000000 00001100, = 12 Number2 = Number1 Or 32 'Number1 = 00000000 01101110, = 110 Number2 = Number1 Xor 32 'Number1 = 00000000 01101110, = 110 Number2 = Not Number1 'Number1 = 11111111 10110001, = -79 |