[I’d left this post in drafts a while ago and forgot about it. Can’t quite remember what I was doing, think it may have been a time thing. The solution is the bit that counts.]

I don’t know why I suddenly had an issue with calculations in my code, but they were throwing up some real problems. Only in one section though, which is thoroughly annoying.

I have a value held as a double, 0.975, and I’m subtracting another value held as a double, 0.988. The result should be -0.013. But it’s giving a result 1.3333333333E-10 (I didn’t write it down but it was something like that).

I know that there are issues with accuracy when working with different data types as the way values are stored digitally can give weird results for some numbers. But I’ve always thought this was when using division, not what appears to be a simple subtraction. I’ve dealt with an accuracy issue previously by using rounding to 2 decimal places, as that was fine then. However, I didn’t want to restrict this value because it might run to a few decimal places.

Although I found an article saying you can declare as a decimal in VBA, it didn’t work when I tried it and gave an error. I did find another suggestion though which said that it is possible to declare as a variant, then use that value converted to a decimal, using CDec(value). So you can convert to decimal but not declare as decimal, another quirk of the now legendary VBA. When I did this with the two values, it worked –

ValueA = 0.975

ValueB = 0.988

Result = CDec(ValueA) – CDec(ValueB)

I can’t work out why in one module it didn’t cause an issue, but in another, it did. Same numbers, same data types, one works, the other doesn’t.