General information about "Decimal"
Decimal is a subtype of Variant
In VBA, the number type Decimal is special. Decimal is not an independent number type. It is a subtype of the type Variant. The type Variant can contain all kind of data: numbers, texts, objects, Null, Empty and so on. Practically, this means that the following VBA code does not work.
1 2 3 | Dim Number As Decimal |
The following code does work. You need to use the conversion function CDec() to convert from String to Decimal.
1 2 3 4 | Dim Number As Variant Number = CDec("123456789,123456789123456789") |
From -79228162514264337593543950335 to 79228162514264337593543950335
A number of the type Decimal is a number that can contain values with up to 29 decimal digits in the range {-79228162514264337593543950335,..., -79228162514264337593543950335}.
The following VBA code works.
1 2 3 4 | Number = CDec("79228162514264337593543950335") Number = CDec("-79228162514264337593543950335") |
When you assign a value outside that is larger than 79228162514264337593543950335 or smaller than -79228162514264337593543950335, the VBA code will crash. At run time, it will generate an overflow error.
1 2 3 4 | Number = CDec("79228162514264337593543950336") 'run time error, overflow Number = CDec("-79228162514264337593543950336") 'run time error, overflow |
Fractional values
A number of the type Decimal can have a fractional part that has at most 28 decimal digits. The total number of digits is 29, at most. Hence, if the non-fractional consist of 9 digits, the fractional part can cosist of at most 20 decimal places. Hence, the smallest number of the type Decimal - in absolute value - is 0.0000000000000000000000000001 (there are 27 zeroes after the decimal sign). In constrast to the number types Single and Double, the number type Decimal is not a floating point number type.
1 2 3 4 | Number = CDec("0,0000000000000000000000000001") 'Number is 0,0000000000000000000000000001 Number = CDec("123456789,12345678912345678912") 'Number is 123456789,12345678912345678912 |
Be careful, the following VBA code does work.
1 2 3 4 5 6 7 | Number = CDec("0,00000000000000000000000000001") 'Number is 0 Number = CDec("123456789,123456789123456789123") 'Number is 123456789.12345678912345678912 Number = CDec("123456789,123456789123456789125") 'Number is 123456789.12345678912345678912 Number = CDec("123456789,1234567891234567891251") 'Number is 123456789.12345678912345678913 |
Default
On declaration, by default, VBA assigns the value Empty to the number of the type Variant. In a numerical context, the value Empty behaves as 0.
1 2 3 4 | Dim Number As Variant 'Number = Empty Number = Number + CDec("123456789,123456789") 'Number = 123456789.123456789 |
Type declaration character
In VBA, there is no type declaration character for Decimal.
Bytes?
We are not sure how many bytes a number of the type Decimal takes in the memory in VBA.
In Visual Basic, according to the documentation, a number of the type Decimal takes 16 bytes in the memory. Twelve of these bytes hold an integer value. As 12 bytes represent 96 bits, there are 296-1 = 79228162514264337593543950336 possible bit representations. This corresponds to the largest possible value for a number of the type Decimal minus one, 79228162514264337593543950335. This leaves 4 bytes (or 32 bits) to represent the scaling factor and the sign, which seems a lot as the scaling factor is limited to a number from 1 to 28 and the sign only requires one bit.
Doing +,-,/ etc.
For Decimal, numercial operations are exact, provided that you stay within the range of numbers that contain at most 29 decimal digits and are in the range in the range {-79228162514264337593543950335,..., -79228162514264337593543950335}.
1 2 3 4 5 | Dim Number As Variant Number = CDec("123456789,123456789") Number = Number * 2 'Number = 246913578,246913578 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Dim Number1 As Variant Dim Number2 As Variant Dim Number3 As Variant Number1 = CDec("90") Number2 = CDec("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 = 9 Number3 = Number1 Mod 7 'Number3 = 6 |
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 |
The range {-79228162514264337593543950335,..., -79228162514264337593543950335} is unimaginably wide, but not endless. Watch out for overflows. This VBA code will crash because the result is outside the range of the type
Decimal.
1 2 3 4 5 6 7 8 9 | 'calculate n! Dim Faculty As Variant Faculty = CDec("1") Dim Index As Long For Index = 1 To 28 Faculty = Faculty * Index 'run time error, overflow 'when Index = 28, run time error, overflow Next Index |
The following VBA code also overflows but does not crash. It works, even though the numbers overflow and result is not exact.
1 2 3 4 5 6 7 8 9 10 11 | Dim Number As Variant Number = CDec("123456789,123456789123456789") Number = Number / 1000 'Number = 123456,789123456789123456789 Number = Number / 1000 'Number = 123,456789123456789123456789 Number = Number / 1000 'Number = 0,123456789123456789123456789 Number = Number / 1000 'Number = 0,0001234567891234567891234568 Number = Number / 1000 'Number = 0,0000001234567891234567891235 Number = Number / 1000 'Number = 0,0000000001234567891234567891 |
The power operator ^ doesn't work with Decimal. If you use it, VBA changes the subtype of Variant while doing the calculations.
1 2 3 4 5 6 7 8 | Dim Number As Variant Number = CDec("123,45") MsgBox VarType(Number) 'shows 14, which means Decimal Number = Number ^ 2 MsgBox VarType(Number) 'shows 5, which means Double |
Calculations using the type Decimal are known to be slower than calculations using the other the number types.
Conversion to Decimal
VBA offers a conversion function CDec() that will try to convert anything into a number of the type Decimal. As, in VBA, Decimal is not an independent number type, you will need it constantly.
1 2 3 4 5 6 7 8 9 10 11 12 | Dim Number As Variant Number = CDec(25) 'Number = 25 Number = CDec(256) 'run time error, overflow Number = CDec(-1) 'run time error, overflow Number = CDec(25.2) 'Number = 25 Number = CDec("25,2") 'Number = 25 Number = CDec("256") 'run time error, overflow Number = CDec("VBA") 'run time error, type mismatch |