This is a discussion on Why does Excel add "extra" numbers in the 10-16 decimal place holders? within the Excel Questions forums, part of the Question Forums category; Greetings all! This is probably an easy question for the experienced, but could someone tell me why: When I dimension ...
This is probably an easy question for the experienced, but could someone tell me why:
When I dimension a numeric decimal value as a single or double in VBA code (such as 5.05, etc.), why does excel turn it into 5.0499999999999998?I'm thinking that the issue is with excel because I'm using the value(s) in the spreadsheet to populate fields on a Word mail merge main document and even when the Excel cell displays the correct value in the cell (5.05), the value in the Word merge field ends up 5.0499999999999998. Not to mention, sometimes even though the correct value is in the cell, when it is selected as the active cell the value that appears in the formula bar is not 5.05 but 5.049999999999999998.
I've tried using the round function as cleverly as I can, including changing the number from 5.05 to 5.051 and then rounding to 2 decimal places, but it doesn't make a difference.
Anyone have any ideas as to why this is happening? I have other hard coded values within the code and they work just fine, but they're positive, whole, real numbers.
Thanks in advance for your time!
Excel has trouble handling decimals, because numbers in Excel are generally in Base 2, so what you get is the nearest representation.
To get accurate decimals you can use the Decimal data type in VBA (declare it as a Variant, then use the CDec function) or you can use Currency.
Self-preservation: For when you've got yourself in a jam
My site contains a number of Excel and Access Resources