Notwithstanding the fact that Excel will only display a certain
number of decimal places, is it possible to, with formula or
macro, calculate pi in Excel to a significant number of places?
I am aware of =PI(), however, this is not relevant to the discussion.
There is no formula for PI (see 2nd paragraph of this article...
http://en.wikipedia.org/wiki/Pi), so in the sense I think you are asking,
the answer would be no. However, if there was a maximum number of digits
that you would accept as a limit, then you could simply assign the value of
PI to that many digits to a constant (using a Const statement) within a
function and have the function return the left number of characters you want
at the time you use the function. For example, the following function will
return PI up to 1000 digits after the decimal point (remember, you won't be
able to do any accurate calculations with over 14 decimal places, 28 for
Variants of Decimal data Type provided you protect against over-flowing)...
Function PI(NumberOfDecimalPlaces As Long) As String
Const PIvalue As String =
"3.14159265358979323846264338327950288419716939937510" & _
"58209749445923078164062862089986280348253421170679"
& _
"82148086513282306647093844609550582231725359408128"
& _
"48111745028410270193852110555964462294895493038196"
& _
"44288109756659334461284756482337867831652712019091"
& _
"45648566923460348610454326648213393607260249141273"
& _
"72458700660631558817488152092096282925409171536436"
& _
"78925903600113305305488204665213841469519415116094"
& _
"33057270365759591953092186117381932611793105118548"
& _
"07446237996274956735188575272489122793818301194912"
& _
"98336733624406566430860213949463952247371907021798"
& _
"60943702770539217176293176752384674818467669405132"
& _
"00056812714526356082778577134275778960917363717872"
& _
"14684409012249534301465495853710507922796892589235"
& _
"42019956112129021960864034418159813629774771309960"
& _
"51870721134999999837297804995105973173281609631859"
& _
"50244594553469083026425223082533446850352619311881"
& _
"71010003137838752886587533208381420617177669147303"
& _
"59825349042875546873115956286388235378759375195778"
& _
"18577805321712268066130019278766111959092164201989"
If NumberOfDecimalPlaces <= 1000 Then
PI = Left(PIvalue, NumberOfDecimalPlaces)
Else
Err.Raise 6
End If
End Function
Rick Rothstein (MVP - Excel)