This is what the Microsoft on-line help says about using the Decimal data type in VBA
“”
At least that is what the Microsoft search listed for me, and Google listed nothing from Microsoft related to VBA on the first page.
The VBA help takes you to the Visual Basic page on the same data type, which says.
Holds signed 128-bit (16-byte) values representing 96-bit (12-byte) integer numbers scaled by a variable power of 10. The scaling factor specifies the number of digits to the right of the decimal point; it ranges from 0 through 28. With a scale of 0 (no decimal places), the largest possible value is +/-79,228,162,514,264,337,593,543,950,335 (+/-7.9228162514264337593543950335E+28). With 28 decimal places, the largest value is +/-7.9228162514264337593543950335, and the smallest nonzero value is +/-0.0000000000000000000000000001 (+/-1E-28).
This would be OK if VB and VBA were essentially the same in their use of this data type, but they are not. For instance:
- The VB help says that Decimals are declared with: Dim bigDec1 As Decimal, but this is not accepted in VBA.
- In VB appending the identifier type character @ to any identifier forces it to Decimal, but in VBA this forces the identifier to the Currency data type (which has only 4 decimal places).
- In VB appending the literal type character D to a literal forces it to the Decimal data type, but in VBA this generates an “Overflow” error message.
So can the Decimal data type actually be used in VBA? Yes, it can (and it is reasonably straightforward), but there are a number of aspects that need to be handled carefully, and use in engineering and scientific calculations is greatly restricted.
To use a Decimal in VBA, declare it as a variant, then use the CDec() function to convert an input value into a decimal. If data is being read from a spreadsheet cell it may be entered as a number if it has 15 or less significant figures, or as a text string if 16 or more figures are required. The code below will add the two values a and b, and return the result as a string, or optionally as a Decimal. Returning a Decimal value to the spreadsheet will result in it being converted to a double, but if it is being used in another VBA routine keeping it as a Decimal will be more efficient.
Function DecAdd(a As Variant, b As Variant, Optional RtnString As Boolean = True) As Variant
Dim Res As Variant
Res = CDec(a) + CDec(b)
If RtnString Then
DecAdd = Str(Res)
Else
DecAdd = Res
End If
End Function
This function will allow values (entered as text strings) with up to 28 significant figures to be added on the spreadsheet, and the Decimal.xlsb spreadsheet has similar short functions to subtract, multiply, divide, and find the maximum and minimum of two values.
There is also a function to find the square root of any input value (based on code taken from VBAExpress), but this brings us to the main drawback of this data type. If any of the VBA maths functions are used on a Decimal, the result will be returned as a Double, unless you write your own function, using only the basic arithmetic operators (plus a small number of other simple functions, such as Abs()).
Fortunately there is an easier way of carrying out high precision calculations. The XNumbers package will do arbitrary precision calculations, and is now available for Excel 2007 and later. See XNumbers for more details and free download.
Newton Excel Bach, not (just) an Excel Blog 