Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




Pi in VBA?
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. 
#2




Answer: Pi in VBA?
You're right that there isn't a builtin 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: Formula:
Formula:
__________________
I am not human. I am an Excel Wizard Last edited by kevin : April 2nd 23 at 07:06 PM 
#3
Posted to microsoft.public.excel.programming




Pi in VBA?
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 ? 
#4
Posted to microsoft.public.excel.programming




Pi in VBA?
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. 
#5
Posted to microsoft.public.excel.programming




Pi in VBA?
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. 
#6
Posted to microsoft.public.excel.programming




Pi in VBA?
"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. 
#7
Posted to microsoft.public.excel.programming




Pi in VBA?
On Tue, 28 Apr 2009 15:56:17 +1000, "AltaEgo" <[email protected]
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. 
#8
Posted to microsoft.public.excel.programming




Pi in VBA?
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" <[email protected] 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. 
#9
Posted to microsoft.public.excel.programming




Pi in VBA?
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" <[email protected] 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. 
#10
Posted to microsoft.public.excel.programming




Pi in VBA?
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. 
#11
Posted to microsoft.public.excel.programming




Pi in VBA?
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. 
#12
Posted to microsoft.public.excel.programming




Pi in VBA?
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? 
#13
Posted to microsoft.public.excel.programming




Pi in VBA?
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.  Rick (MVP  Excel) "Prof Wonmug" wrote in message ... 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? 
#14
Posted to microsoft.public.excel.programming




Pi in VBA?
"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 VBonly 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. 
#15
Posted to microsoft.public.excel.programming




Pi in VBA?
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...  HTH... Jim Thomlinson "Prof Wonmug" wrote: 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? 
#16
Posted to microsoft.public.excel.programming




Pi in VBA?
"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.) 
#17
Posted to microsoft.public.excel.programming




Pi in VBA?
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 modulelevel 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 modulelevel variable compares with using a functionlevel 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.) 
#18
Posted to microsoft.public.excel.programming




Pi in VBA?
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 = = = = Rick Rothstein wrote: 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. 
#19
Posted to microsoft.public.excel.programming




Pi in VBA?
Hi. Just to be a little different...
Not really recommended, but a poorman'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. 
#20
Posted to microsoft.public.excel.programming




Pi in VBA?
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 poorman'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. 
#21
Posted to microsoft.public.excel.programming




Pi in VBA?
On Wed, 29 Apr 2009 16:45:56 0400, Dana DeLouis
wrote: Hi. Just to be a little different... Not really recommended, but a poorman'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? 
#22
Posted to microsoft.public.excel.programming




Pi in VBA?
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 
#23
Posted to microsoft.public.excel.programming




Pi in VBA?
On Tuesday, April 28, 2009 1:00:22 AM UTC4, 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. 
#24
Posted to microsoft.public.excel.programming




Pi in VBA?
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 UTC4, 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 
#25
Posted to microsoft.public.excel.programming




Pi in VBA?
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 UTC4, 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. 
#27
Posted to microsoft.public.excel.programming




Pi in VBA?
"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.1E15 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 reinterpret 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 
#28
Posted to microsoft.public.excel.programming




Pi in VBA?
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.1E15 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 reinterpret 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. 
#29
Posted to microsoft.public.excel.programming




Pi in VBA?
"Auric__" wrote:
joeu2004 wrote: Const pi As Double = "3.1415926535897931" The internet says that last digit should be 2. Shrug. I was referring to creating the same 64bit binary floatingpoint representation that Excel PI() returns. It does not matter whether we use "3.1415926535897931", "3.1415926535897932" or "3.14159265358979323846264338327950288419716939937 510582097494". The 64bit representation is the same, to wit: 3.14159265358979,311599796346854418516159057617187 5 My point was: that is different from the 64bit representation of "3.14159265358979", which is: 3.14159265358979,000737349451810587197542190551757 8125 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 64bit representation. The 64bit representation is limited to the sum of 53 consecutive powers of 2. So we cannot represent all numbers. The 64bit values nearest Excel PI() a PI()2^51: 3.14159265358979,267190875361848156899213790893554 6875 PI(): 3.14159265358979,311599796346854418516159057617187 5 PI()+2^51: 3.14159265358979,356008717331860680133104324340820 3125 As you can see, "3.1415926535897931" is the closest 64bit representation of the mathematical constant. "Auric__" wrote: On the other hand, I don't think I've *ever* needed more than about 4 or 5 digits. You would if you ever wrote something like [1]: 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. 
#30
Posted to microsoft.public.excel.programming




Pi in VBA?
Clarification.... I wrote:
The 64bit representation is limited to the sum of 53 consecutive powers of 2. ..... times an exponential factor (a power of 2). 
Reply 
Thread Tools  Search this Thread 
Display Modes  

