I have noticed a precision difference between the following two calculations done in VBA (I'm using the VBA immediate window for output):

2 * WorksheetFunction.Pi = 6.28318530717959
2 *     3.14159265358979 = 6.28318530717958

Notice the last digit is 8, above was 9, even though 3.14159265358979 used in the calculation is the result given by "WorksheetFunction.Pi".

The first result is the more accurate result looking at the longer value of PI (2 * 3.141592653589793 = 6.283185307179586 which would round to 59).

Any explanation for the above, and is it possible to manually use a longer value of Pi in VBA?

CodePudding user response：

According to this documentation the PI function only returns 15 digits as a double.

There is an old but interesting conversation about PI-precision in VBA here with multiple solutions by an Excel MVP, one would be declaring PI like this PI = 4 * ATN(1) and the other one where the MVP says "For the most accuracy, declare PI like this..." is this

Dim PI As Variant
PI = CDec("3.1415926535897932384626433833")

I have not verified if this actually works, but it seems pretty promising.