Re: Pi in VBA?

[AltaEgo]

Set a variable and use the variable

PI = Application.WorksheetFunction.Pi()

C = 2*PI*R

 

[Rick Rothstein]

You didn't like this one?

PI = 4 * ATN(1)

 

[Jim Thomlinson]

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?

 

[Rick Rothstein]

Why calculate a constant?

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.

 

[JoeU2004]

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

 

[Jim Thomlinson]

Why calculate a constant?

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

 

[JoeU2004]

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]

PS....

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

 

[Dana DeLouis]

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).

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