How accurate is the double data type?

[junfanbl]

I am putting together a template in Excel for rolling up and summing data. I was planning on using arrays to collect the necessary numbers and then sum them in the end using application.worksheetfunction.sum. However, I am wondering will I lose any accuracy if I use a data type of variant for the arrays? Or should I use double? Is there a big difference?

 

[SkipVought]

HI,

Well it depends on your data and your objective.

My initial question is why VBA? Why not simply use spreadsheet functions to aggregate on the sheet?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IRstuff]

Variant is a container that contains whatever variable type results from a calculation or an input, be it string, single, integer, or double, etc. Unless you are counting atoms, single is probably more than good enough. Variant should be used sparingly, as it requires VBA to do additional processing to determine what data type is being contained, while an explicit "double" does not. How much data and how much precision is involved? You need to determine that before asking such questions.

TTFN
I can do absolutely anything. I'm an expert!
homework forum: //

http://www.engineering.com/AskForum/aff/32.aspx

faq731-376 forum1529

 

[IDS]

As has been said, a variant is holder for anything. It can be a number, a string, a range, or any other object.

For your purposes the important point is that if you are working with the value property of cells in a range (which you are if you use worksheetfunction.sum) then the values will be variants, but they will have the same precision as the data in the cells, so you can confidently use variants without worrying about loss of precision. For any value other than currency the value will have double precision (64 bits). Currency also uses 64 bits, but is stored in a different format appropriate to currency calculations.

It is often worth converting a range object into a variant array, which can be done with:
Dim MyData as Variant
MyData = Range("myrange").Value
or
MyData = Range("myrange").Value2

These will (if "myrange" is a valid named range in the active workbook) create an array of the values in myrange. Using .Value preserves currency data type and .Value2 converts all numerical values into doubles (but it is still a variant array, because it can contain strings). Using .Value2 is significantly faster and I recommend it as the default.

If you are doing complex calculations on a huge array it can sometimes be worthwhile to convert the variant array into a double array, but that is rarely worth the trouble.

For your purposes it may be better to keep the range as a range object:
Dim MyData as Range
Set MyData = Range("myrange")

Note that if you do that any changes to MyData will also be displayed in "myrange" in the worksheet, whereas if you convert the data to a variant array and want the changed data to display in the worksheet you have to do:
Range("myrange").Value = Mydata.

Also note that having the active link between VBA range objects and the worksheet range can be very slow if a lot of small changes are made. In general it pays to minimise the number of data transfers between VBA and the worksheet.

Using singles will very rarely be of any benefit because VBA internally converts them into doubles in calculations anyway, so they are actually slower than using doubles in the first place, as well as losing precision.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Agent666]

I always found this summary quite useful in selecting an appropriate variable type depending on the data I am working with:-

Link