General information about "Currency"
From -922337203685477.5808 to 922337203685477.5807
In VBA, a number of the type Currency is a number that can contain values in {-922337203685477.5808,...,-0.0001,0,0.0001,..., 922337203685477.5807}.
1 2 3 4 | Dim Number As Currency Number = 33333333.3333 |
The following VBA code works.
1 2 3 4 5 6 7 | Number = 0 Number = 0.0001 Number = -0.0001 Number = 922337203685477.5807 Number = -922337203685477.5808 |
When you assign a value outside of the range {-922337203685477.5808,...,-0.0001,0,0.0001,..., 922337203685477.5807}, the VBA code will crash. At run time, it will generate an overflow error.
1 2 3 4 | Number = -922337203685478 'run time error, overflow Number = 922337203685478 'run time error, overflow |
Hexadecimal assignment
You can use hexadecimal assignment in VBA.
1 2 3 4 | Number = 12345.12 'Number = 12345.12 Number = &H1212345.12 'Number = 18948933 |
Fractional values
A number of the type Currency can fractional values, however, it only has 4 decimal places. The type Currency is not a floating point number type.
1 2 3 4 | Number = 3.141592 'Number = 3.1416 Number = 1/3 'Number = 0.3333 |
Default
On declaration, by default, VBA assigns the value 0 to the number of the type Currency.
1 2 3 | Dim Number As Currency 'Number = 0 |
Type declaration character
The type declaration character for Long is @.
1 2 3 4 5 6 | Dim Number As Currency Number = 1@ MsgBox VarType(1@) 'shows 6, which means Currency |
Eight bytes
A number of the type Currency takes up eight bytes in the memory. In a byte, there are 8 bits, each of which can take the value 0 or 1. Hence, eight bytes can have 264 possible bit representations. 9223372036854775808 of them are negative, 9223372036854775807 of them are positive, 1 of them is zero. Each of these 18446744073709551616 possible bit representations corresponds to one value in {-922337203685477.5808,...,-0.0001,0,0.0001,..., 922337203685477.5807}.
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 | 00000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000000 | 0 | |
| 00000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000001 | 0.0001 | |
| 00000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000010 | 0.0002 | |
| ... | |||||||||
| 01111111 | 11111111 | 11111111 | 11111111 | 11111111 | 11111111 | 11111111 | 11111111 | 922337203685477,5807 | |
| 10000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000001 | -0.0001 | |
| 10000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000000 | 00000010 | 0.0002 | |
| ... | |||||||||
| 11111111 | 11111111 | 11111111 | 11111111 | 11111111 | 11111111 | 11111111 | 11111111 | -922337203685477,5808 |
Here is how to read the bit representation. For instance, the bit representation 01000001 00000000 0000001 00000000 00000000 00000000 00001001 00000001
| Bit 1 | 0 x 128 | = | 0 |
| Bit 2 | 1 x 64 | = | 64 |
| Bit 3 | 0 x 32 | = | 0 |
| Bit 4 | 0 x 16 | = | 0 |
| Bit 5 | 0 x 8 | = | 0 |
| Bit 6 | 0 x 4 | = | 0 |
| Bit 7 | 0 x 2 | = | 0 |
| Bit 8 | 1 x 1 | = | 1 |
corresponds to 65 (= 64 + 1).
Doing +,-,/ etc.
Of course, you can make additions, subtractions, multiplications and so on. For CurrencyNumercial operations are exact, provided that you stay within the range {-922337203685477.5808,...,-0.0001,0,0.0001,..., 922337203685477.5807}.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Dim Number1 As Byte Dim Number2 As Byte Dim Number3 As Byte Number1 = 90 Number2 = 170 Number3 = Number1 + 37 'Number3 = 127 Number3 = Number2 - Number1 'Number3 = 80 Number3 = Number1 * 2 'Number3 = 180 Number3 = Number1 / 5 'Number3 = 18 Number3 = Number1 \ 10 'Number3 = 90 Number3 = Number1 Mod 7 'Number3 = 6 |
This VBA code will crash because the result is outside the range {-922337203685477.5808,...,-0.0001,0,0.0001,..., 922337203685477.5807}.
1 2 3 4 5 | Number3 = Number1 ^ 2 'run time error, overflow Number3 = Number2 + Number1 'run time error, overflow Number3 = -Number1 'run time error, overflow |
Or, more subtle, and more dangerous.
1 2 3 4 5 6 7 8 | 'calculate n! Dim Index As Long Faculty = 1 For Index = 1 To 20 Faculty = Faculty * Index 'run time error, overflow 'when Index = 13, run time error, overflow Next Index |
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 |
Be very careful when doing numerical operations. This VBA code does not crash.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Dim Number1 As Byte Dim Number2 As Byte Dim Number3 As Byte 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 {-2147483648,...,-1,0,1,...,2147483647} is quite wide, but not endless. Watch out for overflows. Imagine you have a counter that is augmented with 1 each time you read a line from a file. Probably, when building your code, you will read small files. However, in todays data world, files that have more than 2 billion lines exist and may cause your code to crash. It is not that likely, but be aware.
Conversion to Currency
VBA offers a conversion function CCur() that will try to convert anything into a number of the type Currency.
1 2 3 4 5 6 7 8 9 10 11 12 | Dim Number As Currency Number = CCur(25) 'Number = 25 Number = CCur(256) 'run time error, overflow Number = CCur(-1) 'run time error, overflow Number = CCur(25.2) 'Number = 25 Number = CCur("25,2") 'Number = 25 Number = CCur("256") 'run time error, overflow Number = CCur("VBA") 'run time error, type mismatch |
Bit operations
VBA supports a limited number of bit operations. Better not to use them.
1 2 3 4 5 6 7 8 9 10 | Dim Number1 As Currency Dim Number2 As Currency 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 |