## The VBA Decimal data type

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:

1. The VB help says that Decimals are declared with: Dim bigDec1 As Decimal, but this is not accepted in VBA.
2. 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).
3. 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
Else