VBA Division of integers

Question

It seems improbable that this is not a duplicate of a question that already has an answer but I can't find that question or answer so here I go...

In VBA, in the immediate window, if I type:

?8/7 I get the result: 1.14285714285714 which if I then multiply by 7 gives me a number that is slightly LESS than 8, i.e. 7.99999999999998. My pocket calculator provides more decimal places, so it's better than VBA? Right? ;-)

However, if I add 0.000000000000003 to the RESULT 1.142... before I multiply I get 8 (which, by the way, is also incorrect); but my question is:

How can I control the precision of the answer with respect to the number of decimal places shown for ?8/7?

I have seen this answer relating to the accuracy of floating points but it is a different question, it explains the reasons for the inaccuracies whereas I am interested in getting a few extra digits.

To this end I have tried writing a function that returns a double but it does not return enough decimal places... for example I'd like to see: 1.142857142857142857. I also found references to a decimal data type but I think the article is for VB6 (instead of VBA) think I have exhausted the available data types in VBA... what am I missing?

Function test() As Double

    Dim a As Double
    Dim b As Double
    Dim c As Double

    a = 8
    b = 7
    c = a / b
    test = c

End Function

Possible duplicate of [Why Are Floating Point Numbers Inaccurate?](https://stackoverflow.com/questions/21895756/why-are-floating-point-numbers-inaccurate) — Matt Gibson, Dec 03 '17 at 09:50
See [SO: Compare double in VBA precision problem](https://stackoverflow.com/questions/235409/compare-double-in-vba-precision-problem) and [Comparing Floating Point Numbers, 2012 Edition](https://randomascii.wordpress.com/2012/02/25/comparing-floating-point-numbers-2012-edition/) and [Wikipedia: Numeric precision in Microsoft Excel](https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel)... I'm curious what you're working on that 15 precision points isn't sufficient? — ashleedawg, Dec 03 '17 at 09:59
@ashleedawg ah... this is purely academic I was showing my niece how to 'cheat' at homework and she pointed out that her calculator had more numbers... I had nothing — SlowLearner, Dec 03 '17 at 10:15
@JohnColeman is there a reference for that, no wait I'll read the other links. thanks — SlowLearner, Dec 03 '17 at 10:15
@SlowLearner -- ohh don't you _hate_ but _love_ that at the same time, when they outsmart you? My son's grade 8 math is the stuff I stopped paying attention to in Grade 12. — ashleedawg, Dec 03 '17 at 10:16

score 3 · Accepted Answer · answered Dec 03 '17 at 14:13

Graphing and scientific calculators are not built for speed. They can afford to use software implementation of decimal floats for their calculations rather than IEEE doubles. So it is quite possible that your pocket calculator has greater precision than VBA, but at a cost. What they do is likely to be more than an order of magnitude slower.

VBA lacks a built-in arbitrary precision floating point library, but its decimal type supports higher precision than doubles. Unfortunately, it isn't a full-fledged data type but is rather a subtype of Variant. To create them, you need to use CDec():

Sub test()
    Dim x As Variant
    x = CDec(8) / 7
    Debug.Print x
End Sub

This displays 1.1428571428571428571428571429

When using Decimal, you need to be aware of the fact that if you are not careful, they can pop back to e.g. a Double depending on how you use them:

Sub test2()
    Dim x As Variant
    x = CDec(x)
    Debug.Print TypeName(x)
    x = 8 / 7
    Debug.Print x
    Debug.Print TypeName(x)
End Sub

Output:

Decimal
 1.14285714285714 
Double

Thank you - I didn't know about subtypes, much appreciation for taking the time to explain things the way you did. It all makes sense now, cheers — SlowLearner, Dec 03 '17 at 23:58

VBA Division of integers

1 Answers1