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.

On Apr 28, 10:00*am, Prof Wonmug wrote:
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.

WHY DONT YOU JUST HARD CODE IT ?

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)

--
Rick (MVP - Excel)


"Prof Wonmug" wrote in message
...
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.

"Rick Rothstein" wrote:
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.

On Mon, 27 Apr 2009 23:09:15 -0700, "JoeU2004"
wrote:

"Rick Rothstein" wrote:
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.

This is why I don't want to hard code a trancendental constant.

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

Dana DeLouis
= = = =

Prof Wonmug wrote:
On Mon, 27 Apr 2009 23:09:15 -0700, "JoeU2004"
wrote:

"Rick Rothstein" wrote:
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.

This is why I don't want to hard code a trancendental constant.

Thank you.

Wrapping in [ ] instead of Application.WorksheetFunction is new
information for me. Hopefully, the memory banks will retain this gem.

--
Steve

"Dana DeLouis" wrote in message
...
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

Dana DeLouis
= = = =

Prof Wonmug wrote:
On Mon, 27 Apr 2009 23:09:15 -0700, "JoeU2004"
wrote:

"Rick Rothstein" wrote:
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.

This is why I don't want to hard code a trancendental constant.

On Wed, 29 Apr 2009 16:45:56 -0400, Dana DeLouis
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?

------snip------
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

Set a variable and use the variable

PI = Application.WorksheetFunction.Pi()

C = 2*PI*R


--
Steve

"Prof Wonmug" wrote in message
...
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.

On Tue, 28 Apr 2009 15:56:17 +1000, "AltaEgo" <Somewhere@NotHere
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.

You didn't like this one?

PI = 4 * ATN(1)

--
Rick (MVP - Excel)


"Prof Wonmug" wrote in message
...
On Tue, 28 Apr 2009 15:56:17 +1000, "AltaEgo" <Somewhere@NotHere
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.

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. Why calculate a constant?
--
HTH...

Jim Thomlinson


"Rick Rothstein" wrote:

You didn't like this one?

PI = 4 * ATN(1)

--
Rick (MVP - Excel)


"Prof Wonmug" wrote in message
...
On Tue, 28 Apr 2009 15:56:17 +1000, "AltaEgo" <Somewhere@NotHere
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.

On Tue, 28 Apr 2009 07:12:12 -0700, Jim Thomlinson
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?

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.

"Prof Wonmug" wrote:
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" wrote in message
...
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.

On Tuesday, April 28, 2009 1:00:22 AM UTC-4, Prof Wonmug wrote:
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.

This helped. Thank you.

In message of
Thu, 25 Sep 2014 14:32:18 in microsoft.public.excel.programming,
writes
On Tuesday, April 28, 2009 1:00:22 AM UTC-4, Prof Wonmug wrote:
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.

This helped. Thank you.

By definition, pi = 4*arctan(1).
?4*atn(1)
3.14159265358979
--
Walter Briscoe

Walter Briscoe wrote:

In message of
Thu, 25 Sep 2014 14:32:18 in microsoft.public.excel.programming,
writes
On Tuesday, April 28, 2009 1:00:22 AM UTC-4, Prof Wonmug wrote:
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.

This helped. Thank you.

By definition, pi = 4*arctan(1).
?4*atn(1)
3.14159265358979

Since the value of pi won't ever change (barring a fundamental change in the
universe) I just assign it to a constant if I need it:

Const PI = 3.14159265358979

--
Usenet wants your money to buy a heart.

"Auric__" 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"

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

joeu2004 wrote:

"Auric__" 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"

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

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.
[snip]

On the one hand, if I ever needed more precision than I posted, I would be
likely to go nuts:

Const PI = "3.14159265358979323846264338327950288419716939937 510582097494"

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.

In message of Sun,
28 Sep 2014 05:35:05 in microsoft.public.excel.programming, Auric__
writes
Walter Briscoe wrote:

In message of
Thu, 25 Sep 2014 14:32:18 in microsoft.public.excel.programming,
writes
On Tuesday, April 28, 2009 1:00:22 AM UTC-4, Prof Wonmug wrote:
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.

This helped. Thank you.

By definition, pi = 4*arctan(1).
?4*atn(1)
3.14159265358979

Since the value of pi won't ever change (barring a fundamental change in the
universe) I just assign it to a constant if I need it:

Const PI = 3.14159265358979


I failed to notice that this thread dates from April 28, 2009.
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

You're right that there isn't a built-in VBA function for pi, but there are a few ways to get an accurate value for pi in VBA.

One option is to use the constant "4 * Atn(1)" which is a commonly used approximation for pi. This is essentially saying that pi is equal to 4 times the arctangent of 1. Here's an example of how you could use this in a VBA function:

Another option is to use the same pi() worksheet function that you mentioned in your question, but call it using the Application.WorksheetFunction object. Here's an example:

Both of these options should give you an accurate value for pi in your VBA function.

1. Use the constant "4 * Atn(1)" to get an approximation for pi.
2. Call the pi() worksheet function using the Application.WorksheetFunction object.