PS....

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" wrote in message
...
"Prof Wonmug" wrote:
On Tue, 28 Apr 2009 07:12:12 -0700, Jim Thomlinson
Why calculate a constant?

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