Value not equaling what it should -- can't figure out why

[mcomp72]

I have a variable of type Double that is not always the value that it should be, and I can't figure out why. Here's some of the code.

TotalOnForm = Val(Replace(.Range("AC41").Value, ",", ""))

TotalNotAssigned = TotalOnForm - TotalsInArray

(There is other code that assigns the value to TotalsInArray, but it's complicated and I don't think it's the problem, so I did not include it here.)

TotalNotAssigned, TotalOnForm, and TotalsInArray are all of type Double. (They are meant to hold dollar values with two decimals.)

I added those three variables to the watch window so I could see what they were while I was stepping through my code.

Sometimes TotalNotAssigned is not what it should be. For instance, in the test I was just doing, TotalOnForm and TotalsInArray were both equal to the same thing: 237.11

However, TotalNotAssigned does not equal zero when that last line of code is run. Instead, it is equal to 2.8421709430404E-14. I do not understand why this is happening. Any ideas?

Here's a screenshot of the Watch window, so you can see what I mean.
https://postimg.cc/BLYFwPn9

 

[Fazza]

Double gets you lots of digits in numbers. [URL]https://docs.microsoft.com/en-...anguage-reference/data-types/double-data-type[/url]
The sort of subtraction being done often has small results. A VERY familiar consequence of computing.

For dollar values, try instead the currency data type.
[URL]https://docs.microsoft.com/en-...erence/user-interface-help/currency-data-type[/url]

 

[MARK858]

Instead, it is equal to 2.8421709430404E-14. I do not understand why this is happening

mcomp72, further to what Fazza has stated you possibly have a floating point precision issue, see the link below (hard to tell without seeing all your calculations).


https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

 

[MARK858]

Btw, I think the links Fazza was trying to post are below (somehow there wa a double URL in the links).

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/double-data-type
https://docs.microsoft.com/en-us/of...erence/user-interface-help/currency-data-type

 

[mcomp72]

Thanks very much to both of you for the information.

The first thing I decided to try was to add ROUND to one of the lines of code, and now it works.

Instead of

TotalNotAssigned = TotalOnForm - TotalsInArray

I now have

TotalNotAssigned = Round(TotalOnForm, 2) - Round(TotalsInArray, 2)

TotalNotAssigned now calculates the way it should. Thanks for pointing me in the right direction!

 

[Fazza]

please see the highlighted note in https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function

in case that link has an error, I'll copy it here,

This VBA function returns something commonly referred to as bankers rounding. So be careful before using this function. For more predictable results, use Worksheet Round functions in Excel VBA.

Please google if you want to learn more about this.

 

[mcomp72]

This is great to know about -- thank you! I will use the Worksheet Round function instead of the VBA Round function.