INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How accurate is the double data type?

How accurate is the double data type?

(OP)
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?

RE: How accurate is the double data type?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How accurate is the double data type?

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: //www.engineering.com/AskForum/aff/32.aspx
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers

RE: How accurate is the double data type?

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/

RE: How accurate is the double data type?

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

Link

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close