"Auric__" wrote:
joeu2004 wrote:
Const pi As Double = "3.1415926535897931"

The internet says that last digit should be 2. Shrug.

I was referring to creating the same 64-bit binary floating-point
representation that Excel PI() returns.

It does not matter whether we use "3.1415926535897931", "3.1415926535897932"
or "3.14159265358979323846264338327950288419716939937 510582097494".

The 64-bit representation is the same, to wit:

3.14159265358979,311599796346854418516159057617187 5

My point was: that is different from the 64-bit representation of
"3.14159265358979", which is:

3.14159265358979,000737349451810587197542190551757 8125

I use comma to demarcate 15 significant digits, which is all that Excel and
VBA will format.

But it takes 17 significant digits to reproduce the exact 64-bit
representation.

The 64-bit representation is limited to the sum of 53 consecutive powers of
2. So we cannot represent all numbers. The 64-bit values nearest Excel
PI() a

PI()-2^-51: 3.14159265358979,267190875361848156899213790893554 6875
PI(): 3.14159265358979,311599796346854418516159057617187 5
PI()+2^-51: 3.14159265358979,356008717331860680133104324340820 3125

As you can see, "3.1415926535897931" is the closest 64-bit representation of
the mathematical constant.


"Auric__" wrote:
On the other hand, I don't think I've *ever* needed more than
about 4 or 5 digits.

You would if you ever wrote something like [1]:

If Range("A1") = pi Then

where the formula in A1 might evaluate to effectively PI().

The point is: you offered a Const declaration as an alternative to using
WorksheetFunction.Pi.

I was merely correcting the Const value so the alternative was indeed
equivalent.

Whether or not your application requires the precision of
WorksheetFunction.Pi is up to you.


-----
[1] Arguably, it is not a good idea to write Range("A1") = pi. That is
probably not the programmer's intent, in the first place. But I was
anticipating someone asking why Range("A1") = pi is false with your Const
declaration and A1 is =PI(), which Excel displays as 3.14159265358979.