VBA- Beware of Flawed Math

I thought I’d pass along a bug brought to my attention by ‘Jason’ regarding an issue with using the Int() function.

Now there has always been an issue with calculations due to floating point issues, which I’m not going to even attempt to get into in this post. There are far greater minds that have elaborated on this subject in the past. That said, whenever you need to perform any sort of calculation, you are best to always use currency or decimal data types.

Back to today’s post, the issue with the Int() function. Int() is used to return the whole number part from a number. So,

? Int(1)
 1 
? Int(5.25)
 5 
? Int(13.65)
 13

The Flaw!

In an Access Query

In a query
SELECT 40*0.3 AS Expr1, Int(12) AS Expr2, Int(40*0.3) AS Expr3;
Results in
12 | 12 | 12

In an Excel WorkSheet

=INT(40*0.3)
Results in
12

BUT In VBA (the bug)

?40*.3
12 
?int(12)
12

but then, the prolem arises with

?int(40*.3)
11

or in a simple function like

Function dd(a As Integer, b As Double)
    dd = Int(a * b)
End Function
? dd(40, 0.3)
11

Now, since the flaw exists in VBA, this means it is a generalized Office VBA bug, not just Access! So be forewarned the same issue will exist in Excel, Word, …

Where Does That Leave Us Exactly

Now that, apparently, a basic math function like Int() cannot be relied upon, what is one to do?!

Well, I posted this issue to the Access Dev Team and have yet to hear back, but, as usual, my fellow MVPs are there to offer their help! Gustav Brock indicated that using a conversion function will make Int() behave properly. So, the workaround becomes

? Int(CDec(40) * 0.3)
12

or

? Int(40 * CCur(0.3))
12

So, yes, you need to apply a conversion wherever you use Int() to ensure it works properly!

In the case of a function, switching the data type from Double to Currency will remedy the issue

Function dd(a As Integer, b As Currency)
    dd = Int(a * b)
End Function
? dd(40, 0.3)
12

or apply a conversion within the function itself, such as

Function dd(a As Integer, b As Double)
    dd = Int(a * CDec(b))
End Function
? dd(40, 0.3)
12

As always, when I hear back from the Access Dev team I will post any further information they may be able to share with us.

Update 2019-04-18, 2019-05-01

Well, it’s been just shy of 3 weeks now and no comment/reply from the Access Dev Team with regards to my e-mail on this subject, so I take it that I will not get any feedback from them on this. So, just be sure to ALWAYS use conversions wherever you use the Int() function to avoid any erroneous results!!!

…and if this issue exist in Int() should we be concerned about other mathematical functions? That’s the real question at the end of the day!!

Update 2019-04-24

Just some added info regarding the testing for the above.

When I wrote the article, I was running Access 2013 x32 on Windows 7, I have since tested (with the same results) with

  • Access 2003 on Windows XP
  • Access 2007 SP3 (x32) on Windows 7
  • Access 2010 SP2 (x32) on Windows 7
  • Access 2013 (x32) on Windows 10 (Version 1803, OS Build 17134.706)
  • Access 2016 (x32) on Windows 7

Further Resources on the Matter

In the comments below, Luke Chung, President of FMS Inc., just shared with me a technical paper that he produced more 20 years ago that detailed this and more! So be sure to check out When Microsoft Access Math Doesn’t Add Up to make sure you are aware of multiple math issues and how to best ensure your calculations are dependable.

Thank you Luke for sharing!

13 responses on “VBA- Beware of Flawed Math”

  1. Conrad

    Interesting article. I don’t believe the flaw is with entirely with int(), but with VBA. If create a variable ‘x’ as double and do the calculation (x=40*0.3) and check int(x) it is correct. If create variable v1 and v2 as double, and int(v1*v2) then flawed, but correct if x=v1*v2, and int(x). Also int(cdbl(40*0.3)) is correct. That is it’s flawed when ever rely on VBA to type cast the result. Not even mixed data type issue, as converting both to double doesn’t work: int(cdbl(40)*cdbl(0.3)). Though converting one or both to cdec() does work.

    1. Daniel Pineault Post author

      When I wrote the article, I was running Access 2013 x32 on Windows 7, I have since tested (with the same results) with

      • Access 2007 SP3 (x32) on Windows 7
      • Access 2010 SP2 (x32) on Windows 7
      • Access 2013 (x32) on Windows 10 (Version 1803, OS Build 17134.706)
      • Access 2016 (x32) on Windows 7
  4. Ben Sacherich

    I am able to reproduce the Int() behavior with Access 16, 32-bit.

    Instead of using conversion functions inside of Int(), you can just use CInt() which does not have this behavior.

  5. Luke Chung

    This is a long-standing problem that is way below the VBA layer. First published a paper on this in 1997 in Smart Access: http://fmsinc.com/tpapers/math/index.html

    Ridiculous that we have to special case subtraction. Been waiting over 20 years for a fix, but at least there’s a workaround for special casing subtraction.

    1. Daniel Pineault Post author

      Luke,
      Thank you for sharing the paper, it is most informative! Like you, I am floored that this still hasn’t been addressed, but the worst part is simply that most people are unaware of such issue. People depend on such calculation and don’t realize that the results may be inaccurate, this to me is alarming.

  6. DigitGuard

    Nothing wrong with VBA or Math, just the usual float limitations (https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html). There is no exact representation for 0.3 in Double type, closest Double is 0.299999999999999988897769753748 (https://www.binaryconvert.com/result_double.html) what leads to a result a bit smaller than 12 if multiplicated with 40 and ?Int(11.99999….) is 11!

    Storing the result
    Converting one Double value to Decimal/Currency, causes implicit Let-coercion of the other one to Decimal/Currency too (https://docs.microsoft.com/en-us/openspecs/microsoft_general_purpose_programming_languages/ms-vbal/e070115f-8d40-40cf-ac6d-ab18b9c6c906).

    Storing the result of 40 * 0.3 in a variable causes rounding at the 15th decimal fraction place (compare ?0.2999999999999999 vs ?0.299999999999999)

    Using CInt() causes a Let-coercion (value converted to an integer using Banker’s rounding https://docs.microsoft.com/en-us/openspecs/microsoft_general_purpose_programming_languages/ms-vbal/8d3ac81d-00ba-4cee-bda0-4bf7c32a7fc8) .

    So everything is fine, except we usually don’t care about that!

    Btw: same with VBA.Round() function, works fine if you are aware about the limit of Floats and use Round(CDec(FloatVal), n).

    1. Daniel Pineault Post author

      We’ll have to agree to disagree on this one.

      When I can’t count on getting the proper result from something as simple as int(40*.3), regardless of the underlying justification, there is a fundamental issue! Nothing more to say.

      1. DigitGuard

        Hi Daniel,

        first I wish you a Merry Chrismas!

        Second, many thanks for this great site! I used lots of code-snippets from here over the last years!

        But I I have to contradict your opinion: what else should be the result type of an integer/double multiplication than a double? If you know the traps (and I wasn’t aware of the whole problem till I saw (not reading/understanding all) “What Every Computer Scientist Should Know About Floating-Point Arithmetic” (btw, if that is the appendix, what is the main content?) of float conversions, solution is simple: cast Doubles to Decimal/Currency.

        The fundamental issue for me is the lack of information on that topic! There should be a hint on those functions that Doubles may cause unexpected (but correct) results and that they should be converted (and there should be a simpler version of “[MS-VBAL]: VBA Language Specification” as it is as hard to understand as to find),
        The other flaw seems to be the different behavior of Doubles stored in a variable (gets rounded) and the expression itself (not rounded) as arguments, but that seems to be a topic I can investigate and find a reason for.

        Finally I want to ask you to edit my former comment: Delete first kline of 2. paragrph and last line should be “if you are aware about the limit of Floats and use Round(CDec(FloatVal), n).” and can I donate (as you deserve it) Bitcoins?

        1. Daniel Pineault Post author

          You expect novice users to be aware of such? Come on.

          Math is math and is invariable.

          40 * 0.3 = 12 nothing else. The fact that in VBA it doesn’t because of floating point interpretation is the root cause of the issue, but nevertheless, the issue is there and shouldn’t be. Now, Microsoft expects users to do all sorts of manipulations to workaround there flawed handling of numerical values. No, not me. I call this a fundamental flaw in their math.

          The fact that VBA has issues is a matter of fact, I’m not the first to flag it. This leads to faulty calculations/results.

          I’m stopping here.

  7. FrankRuperto

    It’s very unfortunate that this bug exists and has never been fixed, so my solution has been to avoid using floating point and use decimal.