[excel] Declare a variable as Decimal

I'm optimizing a macro in VBA which had none of the data types declared, so everything was clumsily treated by the compiler as a variant. I'm dealing with scientific measurements, so I need precision.

How do I declare Dim decAsdf as Decimal (not like that but the proper way)?

This question is related to excel vba excel-2007

The answer is


The best way is to declare the variable as a Single or a Double depending on the precision you need. The data type Single utilizes 4 Bytes and has the range of -3.402823E38 to 1.401298E45. Double uses 8 Bytes.

You can declare as follows:

Dim decAsdf as Single

or

Dim decAsdf as Double

Here is an example which displays a message box with the value of the variable after calculation. All you have to do is put it in a module and run it.

Sub doubleDataTypeExample()
Dim doubleTest As Double


doubleTest = 0.0000045 * 0.005 * 0.01

MsgBox "doubleTest = " & doubleTest
End Sub

You can't declare a variable as Decimal - you have to use Variant (you can use CDec to populate it with a Decimal type though).


To declare a variable as a Decimal, first declare it as a Variant and then convert to Decimal with CDec. The type would be Variant/Decimal in the watch window:

enter image description here

Considering that programming floating point arithmetic is not what one has studied during Maths classes at school, one should always try to avoid common pitfalls by converting to decimal whenever possible.

In the example below, we see that the expression:

0.1 + 0.11 = 0.21

is either True or False, depending on whether the collectibles (0.1,0.11) are declared as Double or as Decimal:

Public Sub TestMe()

    Dim preciseA As Variant: preciseA = CDec(0.1)
    Dim preciseB As Variant: preciseB = CDec(0.11)

    Dim notPreciseA As Double: notPreciseA = 0.1
    Dim notPreciseB As Double: notPreciseB = 0.11

    Debug.Print preciseA + preciseB
    Debug.Print preciseA + preciseB = 0.21 'True

    Debug.Print notPreciseA + notPreciseB
    Debug.Print notPreciseA + notPreciseB = 0.21 'False

End Sub

enter image description here


Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to excel-2007

Declare a variable as Decimal filter out multiple criteria using excel vba Excel Formula which places date/time in cell when data is entered in another cell in the same row Creating a list/array in excel using VBA to get a list of unique names in a column Delete all data rows from an Excel table (apart from the first) Excel formula to get week number in month (having Monday) Run-time error '1004' - Method 'Range' of object'_Global' failed A formula to copy the values from a formula to another column Excel how to find values in 1 column exist in the range of values in another Delete entire row if cell contains the string X