Pi in VBA?
Prof Wonmug
function?
Excel 2007 has a pi() worksheet function, but there doesn't appear to
be a corresponding VBA function. The best I could come up with is
C = 2 * Application.WorksheetFunction.Pi() * R
That works, but it's a litte messy.
dk
WHY DONT YOU JUST HARD CODE IT ?
Rick Rothstein
Const PI = 3.14159265358979
Or you can let VB calculate it for you...
PI = 4 * ATN(1)
--
Rick (MVP - Excel)
"Prof Wonmug" <won...@e.mcc> wrote in message
news:253dv4t7qqa295s87...@4ax.com...
AltaEgo
PI = Application.WorksheetFunction.Pi()
C = 2*PI*R
--
Steve
"Prof Wonmug" <won...@e.mcc> wrote in message
news:253dv4t7qqa295s87...@4ax.com...
JoeU2004
> You could just assign the value to a constant directly...
> Const PI = 3.14159265358979
>
> Or you can let VB calculate it for you...
> PI = 4 * ATN(1)
Your constant for PI does not equal VB 4*Atn(1) or Excel PI().
However, if you enter the constant as 3.141592653589793, that does result in
the same binary value as 4*Atn(1) and PI(), even though VB will not display
the last 3.
Prof Wonmug
wrote:
>Set a variable and use the variable
>
>PI = Application.WorksheetFunction.Pi()
>
>C = 2*PI*R
Yep, that's probably the best I can do. Thanks.
Rick Rothstein
PI = 4 * ATN(1)
--
Rick (MVP - Excel)
"Prof Wonmug" <won...@e.mcc> wrote in message
news:re8dv45e9dp60coel...@4ax.com...
Jim Thomlinson
makes sense. I guess that is why they pay you the big money. That being said
I would be inclined to just use the constant and avoid the overhead of a
function. Why calculate a constant?
--
HTH...
Jim Thomlinson
Prof Wonmug
wrote:
This is why I don't want to hard code a trancendental constant.
Prof Wonmug
<rick.new...@NO.SPAMverizon.net> wrote:
>You didn't like this one?
>
>PI = 4 * ATN(1)
I did. It was my second choice. Using the worksheet function is a
little more obvious, that's all.
Prof Wonmug
<James_Thomlinson@owfg-Re-Move-This-.com> wrote:
>I like that one... I would never have thought to use the ArcTangent but it
>makes sense. I guess that is why they pay you the big money. That being said
>I would be inclined to just use the constant and avoid the overhead of a
>function.
You mean the 10 ns overhead (or whatever it is)?
If the function call is in a tight loop that is called billions of
times and if the function call (to define the constant) cannot be
moved outside the loop, then maybe.
>Why calculate a constant?
Accuracy, portability, compatibility?
Rick Rothstein
>
> Accuracy, portability, compatibility?
For the most accuracy, declare PI like this...
Dim PI As Variant
PI = CDec("3.1415926535897932384626433833")
Then, if PI is not placed inside a VB math function call, your calculations
should maintain an accuracy of 28 significant figures (VB math function
calls can only return a Double at most, so if you placed PI inside the math
function call, like Sin(PI/6) for example, then the Sin function will return
a Double; but if you did PI*Sin(0.123) for example, then the calculation
would return a number with 28 significant digits
(0.3854422854886583808804090009 to be exact). So, from your original
question, this...
C = 2 * PI * R
would assign to C a value accurate to 28 significant digits.
--
Rick (MVP - Excel)
"Prof Wonmug" <won...@e.mcc> wrote in message
news:kb6ev4tlp5ticn01p...@4ax.com...
JoeU2004
> On Tue, 28 Apr 2009 03:17:37 -0400, "Rick Rothstein" wrote:
>>You didn't like this one?
>>PI = 4 * ATN(1)
>
> I did. It was my second choice. Using the worksheet
> function is a little more obvious, that's all.
.... And more likely to be accurate insofar as matching the Excel value. I
agree.
__You__ were the one who was asking for a VB-only solution, or so it seemed.
----- previous message -----
"Prof Wonmug" <won...@e.mcc> wrote in message
Jim Thomlinson
In a couple of seconds I can google Pi and get more decimals than my
computer can effectively handle so accuracy is not an issue. As for
portability and compatability, what is more portable or compatable than a
constant. No functions. No problems. While I agree that the overhead is
minimal how much wasted overhead is acceptable? If you want readability then
nothing will be more clear than a constant.
Just my 2 cents and I have probably overcharged...
--
HTH...
Jim Thomlinson
JoeU2004
> On Tue, 28 Apr 2009 07:12:12 -0700, Jim Thomlinson
>
> Accuracy, portability, compatibility?
It should be noted that Jim is referring to the expression 4*Atn(1). That
does not ensure accuracy or compatilibity.
Atn is a transcendental function, which is typically estimated using a
polynomial algorithm. Moreover, VBA sometimes uses different algorithms
than Excel for similar functions. I was pleasantly surprised to learn that
4*Atn(1) has exactly the same binary result as Excel's PI function. There
was certainly no guarantee that would be the case.
On the other hand, Wonmug had used WorksheetFunction.PI(). I do agree that
that is better than a constant for ensuring compatibility with the Excel PI
function with the same accuracy.
Entering a constant with decimal fractions that is not exactly the sum of up
to 53 consecutive powers of 2 might not be portable. I don't know if the
IEEE specifies a standard conversion algorithm. But it is clear that Excel
and VBA treat numbers with more than 15 significant digits differently.
Even within 15 significant digits, I have seen constants where the Excel
conversion could be improved by adding 1 or 2 to the least significant bit.
So I can imagine that different implementations of Excel and VBA could do
the conversion differently. (But I don't know if that would violate a
conversion standard, if any.)
JoeU2004
I wrote:
> On the other hand, Wonmug had used WorksheetFunction.PI().
> [....]
> Entering a constant with decimal fractions that is not
> exactly the sum of up to 53 consecutive powers of 2 might
> not be portable.
You can get the best of both worlds by assigning WorksheetFunction.PI to a
module-level variable only the first time. For example:
Private pi as double
Function doit()
If pi = 0 Then pi = WorksheetFunction.PI
....
end Function
I cannot say with impunity that that is any better than simply calling
WorksheetFunction.PI the first time in each function. But I suspect it is.
I also cannot say how using a module-level variable compares with using a
function-level Const identifier. But I suspect they are both loaded from
memory.
----- original message -----
"JoeU2004" <joeu...@hotmail.com> wrote in message
news:%23ZAT5VD...@TK2MSFTNGP03.phx.gbl...
Dana DeLouis
> example, then the calculation would return
> a number with 28 significant digits
> (0.3854422854886583808804090009 to be exact).
As a side note, Trig Functions like Sin are not supported.
Hence, the solution is only accurate to 15 digits, despite the number of
digits displayed.
HTH :>)
Dana DeLouis
= = = =
Dana DeLouis
Not really recommended, but a poor-man's version of the Pi symbol can
sometimes be done via the Paragraph symbol. You have to have a little
imagination thou to see it as Pi :>)
Sub Demo()
Dim c
Dim � 'Alt + 0182
� = [Pi()]
c = 2 * � * 5
End Sub
Dana DeLouis
= = = =
AltaEgo
Wrapping in [ ] instead of Application.WorksheetFunction is new
information for me. Hopefully, the memory banks will retain this gem.
--
Steve
"Dana DeLouis" <ddel...@bellsouth.net> wrote in message
news:epRs7tQy...@TK2MSFTNGP04.phx.gbl...
Prof Wonmug
<ddel...@bellsouth.net> wrote:
>Hi. Just to be a little different...
>Not really recommended, but a poor-man's version of the Pi symbol can
>sometimes be done via the Paragraph symbol. You have to have a little
>imagination thou to see it as Pi :>)
>
>Sub Demo()
> Dim c
> Dim � 'Alt + 0182
>
> � = [Pi()]
>
> c = 2 * � * 5
>End Sub
You've got *two* surprises in that code snippet. I doubt I'll use the
paragraph symbol (you could have won a reasonably large bet with me in
a bar with that one), but the [pi()] notation in intriguing.
The best I can discover is that it is another way of invoking the
Evaluate method, which I also don't fully understand.
Are you the keeper of VBA esoterica?
Nick H
>Sub Demo()
> Dim c
> Dim ¶ 'Alt + 0182
> ¶ = [Pi()]
> c = 2 * ¶ * 5
>End Sub
------snip------
That is SO neat!
The poor sods who inherit my code are going to be scratching their
heads at all the weird variable symbols that are going to be cropping
up. <vbg>
Nick H
pjbh...@gmail.com
Walter Briscoe
Thu, 25 Sep 2014 14:32:18 in microsoft.public.excel.programming,
pjbh...@gmail.com writes
?4*atn(1)
3.14159265358979
--
Walter Briscoe
Auric__
universe) I just assign it to a constant if I need it:
Const PI = 3.14159265358979
--
Usenet wants your money to buy a heart.
joeu2004
> I just assign it to a constant if I need it:
> Const PI = 3.14159265358979
expression:
Const pi As Double = 3.14159265358979 + 3.1E-15
Or you could write:
Const pi As Double = "3.1415926535897931"
since VBA converts all digits, not just the first 15 significant digits as
Excel does.
FYI, I avoid writing the following:
Const pi As Double = 3.1415926535897931
It works initially. But VBA displays the statement as
Const pi As Double = 3.14159265358979
and the value of pi will be changed to 3.14159265358979 if we edit the line
(e.g. append a comment) and perhaps under other conditions that cause VBA to
re-interpret the statement.
To confirm the differences, try the following macro.
Sub testit()
Const pi As Double = "3.1415926535897931"
Const pi1 As Double = 3.14159265358979
Dim pi2 As Double, pi3 As Double
Range("a1").Clear
Range("a1").Formula = "=PI()"
pi2 = WorksheetFunction.pi()
pi3 = 4 * Atn(1)
MsgBox Format(pi1 - pi, "0.0000E+0") & _
vbNewLine & (pi1 = pi) & _
vbNewLine & (pi1 = Range("a1")) & _
vbNewLine & Format(pi - pi2, "0.0000E+0") & _
vbNewLine & Format(pi3 - pi2, "0.0000E+0") & _
vbNewLine & Format(pi - pi3, "0.0000E+0") & _
vbNewLine & (pi = pi2) & _
vbNewLine & (pi = Range("a1"))
End Sub
Walter Briscoe
28 Sep 2014 05:35:05 in microsoft.public.excel.programming, Auric__
<not.m...@email.address> writes
pjbh...@gmail.com did not give a reason for resurrecting it.
I like Const PI = 3.14159265358979
I prefer Const PI As Double = 3.14159265358979
but there is no difference in meaning. YMMV. ;)
--
Walter Briscoe
Auric__
> "Auric__" <not.m...@email.address> wrote:
>> I just assign it to a constant if I need it:
>> Const PI = 3.14159265358979
>
> If you want to ensure a match with Excel PI(), use the following
> constant expression:
>
> Const pi As Double = 3.14159265358979 + 3.1E-15
>
> Or you could write:
>
> Const pi As Double = "3.1415926535897931"
http://oeis.org/A000796
> since VBA converts all digits, not just the first 15 significant digits
> as Excel does.
>
> FYI, I avoid writing the following:
>
> Const pi As Double = 3.1415926535897931
>
> It works initially. But VBA displays the statement as
>
> Const pi As Double = 3.14159265358979
>
> and the value of pi will be changed to 3.14159265358979 if we edit the
> line (e.g. append a comment) and perhaps under other conditions that
> cause VBA to re-interpret the statement.
>
> To confirm the differences, try the following macro.
On the one hand, if I ever needed more precision than I posted, I would be
likely to go nuts:
Const PI = "3.14159265358979323846264338327950288419716939937510582097494"
On the other hand, I don't think I've *ever* needed more than about 4 or 5
digits. My programs aren't mathematical or scientific (accounting, yes, but
my numbers don't involve circles) and the graphical toys I've written don't
need that much precision, not by a long shot. ;-)
--
I just checked your horoscope.
It recommends you not be alive for the next month.
joeu2004
>> Const pi As Double = "3.1415926535897931"
>
> The internet says that last digit should be 2. Shrug.
representation that Excel PI() returns.
It does not matter whether we use "3.1415926535897931", "3.1415926535897932"
or "3.14159265358979323846264338327950288419716939937510582097494".
The 64-bit representation is the same, to wit:
3.14159265358979,3115997963468544185161590576171875
My point was: that is different from the 64-bit representation of
"3.14159265358979", which is:
3.14159265358979,0007373494518105871975421905517578125
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() are:
PI()-2^-51: 3.14159265358979,2671908753618481568992137908935546875
PI(): 3.14159265358979,3115997963468544185161590576171875
PI()+2^-51: 3.14159265358979,3560087173318606801331043243408203125
As you can see, "3.1415926535897931" is the closest 64-bit representation of
the mathematical constant.
"Auric__" <not.m...@email.address> wrote:
> On the other hand, I don't think I've *ever* needed more than
> about 4 or 5 digits.
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.
joeu2004
> The 64-bit representation is limited to the sum of
> 53 consecutive powers of 2.
herb munson
> What's the best way to get the most accurate value for pi in a VBA
> function?
> Excel 2007 has a pi() worksheet function, but there doesn't appear to
> be a corresponding VBA function. The best I could come up with is
> C = 2 * Application.WorksheetFunction.Pi() * R
> That works, but it's a litte messy.