|
How to strore exact double from macro into Excel cell?
I know this is "stoopid" because I'm sure I have done this before with no
problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! |
How to strore exact double from macro into Excel cell?
Excel keeps track of 15 significant digits when you treat the entry as a number.
VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. with Cells(1 + i + 9, 2) .numberformat = "@" 'text .value = x 'or maybe '.value = cstr(x) end with Untested, though. JoeU2004 wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! -- Dave Peterson |
How to strore exact double from macro into Excel cell?
What are your UDFs ??
-- Gary''s Student - gsnu200904 "JoeU2004" wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! |
How to strore exact double from macro into Excel cell?
"Dave Peterson" wrote:
VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. I think you missed the point. Let me rephrase.... Suppose we have the following VBA procedures: Function doit1() doit1 = Range("a2") End Function Sub doit2() Range("a4") = Range("a2") End Sub Enter the following in Excel: A1: 0.28 A2: =0.28 + 2^-54 A3: =doit1() A4: (to be filled in by macro doit2) Execute the doit2() macro, then enter the following in Excel: B1: =A1-A2=0 (FALSE) B2: =A3-A4=0 (FALSE!) B3: =A2-A3=0 (TRUE) B4: =A1-A4=0 (TRUE!) The FALSE value of B1 demonstrates that the internal binary representation of A1 and A2 are different as intended, even though we cannot see that when formatting to 15 decimal places, Excel's conversion limit. The FALSE value of B2 demonstrates that function doit1 returns a different internal binary representation than macro doit2 returns. Why is that? They both get their value from A2. The TRUE value of B3 demonstrates that function doit1 faithfully duplicates the internal binary representation in A2. The TRUE value of B4 demonstrates that macro doit2 converts A2 to the internal binary representation of the constant 0.28 (A1). Can we coerce macro doit2 to faithfully duplicate the internal binary representation in A2, as function doit1 does? (Without employing some object property/method trick. I really want to use expressions on the right-hand side of the assignment, and not rely on cell references like Range("a2"). I am using the latter only for this example to rule out discussions of differences between Excel and VBA, I hope.) Excel keeps track of 15 significant digits when you treat the entry as a number. Ostensibly, this has nothing to do with Excel's 15-significant-digit conversion limit. Unless.... Are you confirming my WAG, to wit: in a macro, but not in a function, Excel (or VBA?!) first converts the binary value of an assignment to a cell to a numeric string, subject to its 15-significant-digit conversion limit; then Excel converts the numeric string to binary as if it were entered manually. (Of course, we cannot have "an assignment to a cell" in a function. What I mean by "not in a function" is: the same binary-to-string-to-binary conversion does not happen for a function return value -- an assignment to the function name.) That is only speculation at the moment. If it is true, where should I have learned about that difference between macros cell assignments and functions return values? Arguably, this probably matters to only two people in the whole world. (And I'm not sure that Jerry even cares.) Most people try to avoid thinking about the binary representation, and that's just fine. PS: I am a software architect, so I relate to things better if I have an understanding of the architectural relationships among the components of a system. I really do know how Excel and VBA interact architecturally. Is there someplace I can go to get that architectural knowledge? Is there a "devil book" for Excel/VBA? ----- original message ----- "Dave Peterson" wrote in message ... Excel keeps track of 15 significant digits when you treat the entry as a number. VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. with Cells(1 + i + 9, 2) .numberformat = "@" 'text .value = x 'or maybe '.value = cstr(x) end with Untested, though. JoeU2004 wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! -- Dave Peterson |
How to strore exact double from macro into Excel cell?
You're right. I missed your point.
I thought you wanted something like: Function myFunc() As Variant Dim myVal As Variant myVal = CDec(".12341234123412341234123412341234") myFunc = myVal 'or myFunc = "'" & myVal End Function I'm sure Mike Middleton will be able to offer a reasonable explanation (if you can get his attention). On the other hand, these 4 all returned True: =A1=A2 =A3=A4 =A2=A3 =A1=A4 So maybe it's the subtraction that's (partially??) causing the trouble and the comparison to 0 causing the other portion. I'll eagerly <vbg await Mike's response. JoeU2004 wrote: "Dave Peterson" wrote: VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. I think you missed the point. Let me rephrase.... Suppose we have the following VBA procedures: Function doit1() doit1 = Range("a2") End Function Sub doit2() Range("a4") = Range("a2") End Sub Enter the following in Excel: A1: 0.28 A2: =0.28 + 2^-54 A3: =doit1() A4: (to be filled in by macro doit2) Execute the doit2() macro, then enter the following in Excel: B1: =A1-A2=0 (FALSE) B2: =A3-A4=0 (FALSE!) B3: =A2-A3=0 (TRUE) B4: =A1-A4=0 (TRUE!) The FALSE value of B1 demonstrates that the internal binary representation of A1 and A2 are different as intended, even though we cannot see that when formatting to 15 decimal places, Excel's conversion limit. The FALSE value of B2 demonstrates that function doit1 returns a different internal binary representation than macro doit2 returns. Why is that? They both get their value from A2. The TRUE value of B3 demonstrates that function doit1 faithfully duplicates the internal binary representation in A2. The TRUE value of B4 demonstrates that macro doit2 converts A2 to the internal binary representation of the constant 0.28 (A1). Can we coerce macro doit2 to faithfully duplicate the internal binary representation in A2, as function doit1 does? (Without employing some object property/method trick. I really want to use expressions on the right-hand side of the assignment, and not rely on cell references like Range("a2"). I am using the latter only for this example to rule out discussions of differences between Excel and VBA, I hope.) Excel keeps track of 15 significant digits when you treat the entry as a number. Ostensibly, this has nothing to do with Excel's 15-significant-digit conversion limit. Unless.... Are you confirming my WAG, to wit: in a macro, but not in a function, Excel (or VBA?!) first converts the binary value of an assignment to a cell to a numeric string, subject to its 15-significant-digit conversion limit; then Excel converts the numeric string to binary as if it were entered manually. (Of course, we cannot have "an assignment to a cell" in a function. What I mean by "not in a function" is: the same binary-to-string-to-binary conversion does not happen for a function return value -- an assignment to the function name.) That is only speculation at the moment. If it is true, where should I have learned about that difference between macros cell assignments and functions return values? Arguably, this probably matters to only two people in the whole world. (And I'm not sure that Jerry even cares.) Most people try to avoid thinking about the binary representation, and that's just fine. PS: I am a software architect, so I relate to things better if I have an understanding of the architectural relationships among the components of a system. I really do know how Excel and VBA interact architecturally. Is there someplace I can go to get that architectural knowledge? Is there a "devil book" for Excel/VBA? ----- original message ----- "Dave Peterson" wrote in message ... Excel keeps track of 15 significant digits when you treat the entry as a number. VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. with Cells(1 + i + 9, 2) .numberformat = "@" 'text .value = x 'or maybe '.value = cstr(x) end with Untested, though. JoeU2004 wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! -- Dave Peterson -- Dave Peterson |
How to strore exact double from macro into Excel cell?
"Dave Peterson" wrote:
On the other hand, these 4 all returned True: =A1=A2 =A3=A4 =A2=A3 =A1=A4 That form is affected by Excel's attempt to hide "infinitesimal" differences. I wanted to avoid Excel's heuristic. That is why I wrote the comparison in the form =A1-A2=0. So maybe it's the subtraction that's (partially??) causing the trouble and the comparison to 0 causing the other portion. You continue to miss the point. There is no "trouble" with the comparisons. The "trouble" is with the different behavior of assignment of a numeric value to cell in a macro v. assignment of the same numeric value to a function name (i.e. returning the value). I am asking: why does the macro assignment change the binary value -- __that__ is the "trouble" -- whereas the function assignment preserves the binary value -- that is the __desired__ behavior. You seem to be overwhelmed by the details of the binary representation. I suggest that you sit this one out. ----- original message ----- "Dave Peterson" wrote in message ... You're right. I missed your point. I thought you wanted something like: Function myFunc() As Variant Dim myVal As Variant myVal = CDec(".12341234123412341234123412341234") myFunc = myVal 'or myFunc = "'" & myVal End Function I'm sure Mike Middleton will be able to offer a reasonable explanation (if you can get his attention). On the other hand, these 4 all returned True: =A1=A2 =A3=A4 =A2=A3 =A1=A4 So maybe it's the subtraction that's (partially??) causing the trouble and the comparison to 0 causing the other portion. I'll eagerly <vbg await Mike's response. JoeU2004 wrote: "Dave Peterson" wrote: VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. I think you missed the point. Let me rephrase.... Suppose we have the following VBA procedures: Function doit1() doit1 = Range("a2") End Function Sub doit2() Range("a4") = Range("a2") End Sub Enter the following in Excel: A1: 0.28 A2: =0.28 + 2^-54 A3: =doit1() A4: (to be filled in by macro doit2) Execute the doit2() macro, then enter the following in Excel: B1: =A1-A2=0 (FALSE) B2: =A3-A4=0 (FALSE!) B3: =A2-A3=0 (TRUE) B4: =A1-A4=0 (TRUE!) The FALSE value of B1 demonstrates that the internal binary representation of A1 and A2 are different as intended, even though we cannot see that when formatting to 15 decimal places, Excel's conversion limit. The FALSE value of B2 demonstrates that function doit1 returns a different internal binary representation than macro doit2 returns. Why is that? They both get their value from A2. The TRUE value of B3 demonstrates that function doit1 faithfully duplicates the internal binary representation in A2. The TRUE value of B4 demonstrates that macro doit2 converts A2 to the internal binary representation of the constant 0.28 (A1). Can we coerce macro doit2 to faithfully duplicate the internal binary representation in A2, as function doit1 does? (Without employing some object property/method trick. I really want to use expressions on the right-hand side of the assignment, and not rely on cell references like Range("a2"). I am using the latter only for this example to rule out discussions of differences between Excel and VBA, I hope.) Excel keeps track of 15 significant digits when you treat the entry as a number. Ostensibly, this has nothing to do with Excel's 15-significant-digit conversion limit. Unless.... Are you confirming my WAG, to wit: in a macro, but not in a function, Excel (or VBA?!) first converts the binary value of an assignment to a cell to a numeric string, subject to its 15-significant-digit conversion limit; then Excel converts the numeric string to binary as if it were entered manually. (Of course, we cannot have "an assignment to a cell" in a function. What I mean by "not in a function" is: the same binary-to-string-to-binary conversion does not happen for a function return value -- an assignment to the function name.) That is only speculation at the moment. If it is true, where should I have learned about that difference between macros cell assignments and functions return values? Arguably, this probably matters to only two people in the whole world. (And I'm not sure that Jerry even cares.) Most people try to avoid thinking about the binary representation, and that's just fine. PS: I am a software architect, so I relate to things better if I have an understanding of the architectural relationships among the components of a system. I really do know how Excel and VBA interact architecturally. Is there someplace I can go to get that architectural knowledge? Is there a "devil book" for Excel/VBA? ----- original message ----- "Dave Peterson" wrote in message ... Excel keeps track of 15 significant digits when you treat the entry as a number. VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. with Cells(1 + i + 9, 2) .numberformat = "@" 'text .value = x 'or maybe '.value = cstr(x) end with Untested, though. JoeU2004 wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! -- Dave Peterson -- Dave Peterson |
How to strore exact double from macro into Excel cell?
I wrote:
The FALSE value of B2 demonstrates that function doit1 returns a different internal binary representation than macro doit2 returns. Why is that? They both get their value from A2. On second thought, I suspect no one is likely to be able to answer the question dispositively. In fact, it is probably something that no one was aware of, since 99.9...9% of all Excel users see only the 15 significant digits that Excel converts. I really do [not] know how Excel and VBA interact architecturally. Is there someplace I can go to get that architectural knowledge? I suspect the explanation is the same as the reason why copy-and-paste-special-value does not faithfully duplicate the internal binary pattern, an observation I shared in these forums some time ago. I remember that Jerry responded; but I don't remember if he had a dispositive answer or simply a reasonable speculation. Anyway, that's just the way it is. As a software architect myself, I can easily imagine architectural differences between macros and functions that might lend themselves to the different behavior. I do wish someone could answer the immediate question above, namely where I could learn about these architectural differences. ----- original message ----- "JoeU2004" wrote in message ... "Dave Peterson" wrote: VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. I think you missed the point. Let me rephrase.... Suppose we have the following VBA procedures: Function doit1() doit1 = Range("a2") End Function Sub doit2() Range("a4") = Range("a2") End Sub Enter the following in Excel: A1: 0.28 A2: =0.28 + 2^-54 A3: =doit1() A4: (to be filled in by macro doit2) Execute the doit2() macro, then enter the following in Excel: B1: =A1-A2=0 (FALSE) B2: =A3-A4=0 (FALSE!) B3: =A2-A3=0 (TRUE) B4: =A1-A4=0 (TRUE!) The FALSE value of B1 demonstrates that the internal binary representation of A1 and A2 are different as intended, even though we cannot see that when formatting to 15 decimal places, Excel's conversion limit. The FALSE value of B2 demonstrates that function doit1 returns a different internal binary representation than macro doit2 returns. Why is that? They both get their value from A2. The TRUE value of B3 demonstrates that function doit1 faithfully duplicates the internal binary representation in A2. The TRUE value of B4 demonstrates that macro doit2 converts A2 to the internal binary representation of the constant 0.28 (A1). Can we coerce macro doit2 to faithfully duplicate the internal binary representation in A2, as function doit1 does? (Without employing some object property/method trick. I really want to use expressions on the right-hand side of the assignment, and not rely on cell references like Range("a2"). I am using the latter only for this example to rule out discussions of differences between Excel and VBA, I hope.) Excel keeps track of 15 significant digits when you treat the entry as a number. Ostensibly, this has nothing to do with Excel's 15-significant-digit conversion limit. Unless.... Are you confirming my WAG, to wit: in a macro, but not in a function, Excel (or VBA?!) first converts the binary value of an assignment to a cell to a numeric string, subject to its 15-significant-digit conversion limit; then Excel converts the numeric string to binary as if it were entered manually. (Of course, we cannot have "an assignment to a cell" in a function. What I mean by "not in a function" is: the same binary-to-string-to-binary conversion does not happen for a function return value -- an assignment to the function name.) That is only speculation at the moment. If it is true, where should I have learned about that difference between macros cell assignments and functions return values? Arguably, this probably matters to only two people in the whole world. (And I'm not sure that Jerry even cares.) Most people try to avoid thinking about the binary representation, and that's just fine. PS: I am a software architect, so I relate to things better if I have an understanding of the architectural relationships among the components of a system. I really do know how Excel and VBA interact architecturally. Is there someplace I can go to get that architectural knowledge? Is there a "devil book" for Excel/VBA? ----- original message ----- "Dave Peterson" wrote in message ... Excel keeps track of 15 significant digits when you treat the entry as a number. VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. with Cells(1 + i + 9, 2) .numberformat = "@" 'text .value = x 'or maybe '.value = cstr(x) end with Untested, though. JoeU2004 wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! -- Dave Peterson |
How to strore exact double from macro into Excel cell?
I didn't miss the point this time.
You want to know why excel does this. I don't think anyone will be able to answer that question. They may be able to explain the behavior, but not the why. JoeU2004 wrote: "Dave Peterson" wrote: On the other hand, these 4 all returned True: =A1=A2 =A3=A4 =A2=A3 =A1=A4 That form is affected by Excel's attempt to hide "infinitesimal" differences. I wanted to avoid Excel's heuristic. That is why I wrote the comparison in the form =A1-A2=0. So maybe it's the subtraction that's (partially??) causing the trouble and the comparison to 0 causing the other portion. You continue to miss the point. There is no "trouble" with the comparisons. The "trouble" is with the different behavior of assignment of a numeric value to cell in a macro v. assignment of the same numeric value to a function name (i.e. returning the value). I am asking: why does the macro assignment change the binary value -- __that__ is the "trouble" -- whereas the function assignment preserves the binary value -- that is the __desired__ behavior. You seem to be overwhelmed by the details of the binary representation. I suggest that you sit this one out. ----- original message ----- "Dave Peterson" wrote in message ... You're right. I missed your point. I thought you wanted something like: Function myFunc() As Variant Dim myVal As Variant myVal = CDec(".12341234123412341234123412341234") myFunc = myVal 'or myFunc = "'" & myVal End Function I'm sure Mike Middleton will be able to offer a reasonable explanation (if you can get his attention). On the other hand, these 4 all returned True: =A1=A2 =A3=A4 =A2=A3 =A1=A4 So maybe it's the subtraction that's (partially??) causing the trouble and the comparison to 0 causing the other portion. I'll eagerly <vbg await Mike's response. JoeU2004 wrote: "Dave Peterson" wrote: VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. I think you missed the point. Let me rephrase.... Suppose we have the following VBA procedures: Function doit1() doit1 = Range("a2") End Function Sub doit2() Range("a4") = Range("a2") End Sub Enter the following in Excel: A1: 0.28 A2: =0.28 + 2^-54 A3: =doit1() A4: (to be filled in by macro doit2) Execute the doit2() macro, then enter the following in Excel: B1: =A1-A2=0 (FALSE) B2: =A3-A4=0 (FALSE!) B3: =A2-A3=0 (TRUE) B4: =A1-A4=0 (TRUE!) The FALSE value of B1 demonstrates that the internal binary representation of A1 and A2 are different as intended, even though we cannot see that when formatting to 15 decimal places, Excel's conversion limit. The FALSE value of B2 demonstrates that function doit1 returns a different internal binary representation than macro doit2 returns. Why is that? They both get their value from A2. The TRUE value of B3 demonstrates that function doit1 faithfully duplicates the internal binary representation in A2. The TRUE value of B4 demonstrates that macro doit2 converts A2 to the internal binary representation of the constant 0.28 (A1). Can we coerce macro doit2 to faithfully duplicate the internal binary representation in A2, as function doit1 does? (Without employing some object property/method trick. I really want to use expressions on the right-hand side of the assignment, and not rely on cell references like Range("a2"). I am using the latter only for this example to rule out discussions of differences between Excel and VBA, I hope.) Excel keeps track of 15 significant digits when you treat the entry as a number. Ostensibly, this has nothing to do with Excel's 15-significant-digit conversion limit. Unless.... Are you confirming my WAG, to wit: in a macro, but not in a function, Excel (or VBA?!) first converts the binary value of an assignment to a cell to a numeric string, subject to its 15-significant-digit conversion limit; then Excel converts the numeric string to binary as if it were entered manually. (Of course, we cannot have "an assignment to a cell" in a function. What I mean by "not in a function" is: the same binary-to-string-to-binary conversion does not happen for a function return value -- an assignment to the function name.) That is only speculation at the moment. If it is true, where should I have learned about that difference between macros cell assignments and functions return values? Arguably, this probably matters to only two people in the whole world. (And I'm not sure that Jerry even cares.) Most people try to avoid thinking about the binary representation, and that's just fine. PS: I am a software architect, so I relate to things better if I have an understanding of the architectural relationships among the components of a system. I really do know how Excel and VBA interact architecturally. Is there someplace I can go to get that architectural knowledge? Is there a "devil book" for Excel/VBA? ----- original message ----- "Dave Peterson" wrote in message ... Excel keeps track of 15 significant digits when you treat the entry as a number. VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. with Cells(1 + i + 9, 2) .numberformat = "@" 'text .value = x 'or maybe '.value = cstr(x) end with Untested, though. JoeU2004 wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
How to strore exact double from macro into Excel cell?
ps.
that's what this meant: I'll eagerly <vbg await Mike's response. JoeU2004 wrote: <<snipped You seem to be overwhelmed by the details of the binary representation. I suggest that you sit this one out. <<snipped-- Dave Peterson |
How to strore exact double from macro into Excel cell?
"Dave Peterson" wrote:
I didn't miss the point this time. With all due respect, obviously you did. You wrote: "So maybe it's the subtraction that's (partially??) causing the trouble and the comparison to 0 causing the other portion". So clearly, you think my question has something to do with the arithmetic results. My question has nothing to do with that. I am merely using the differences in the arithmetic results -- the internal binary representation -- to demonstrate the problem I am asking about. You want to know why excel does this. I don't think anyone will be able to answer that question. They may be able to explain the behavior, but not the why. I am not asking "why?" in the sense of "what is the motivation?". I used the word "why?" in the sense of "what is going on?". Yes, I am interested in the explanation of "the" behavior. What you do not seem to understand is what "the" refers to. I am asking about the difference in how VBA functions and macros communicate results back to Excel. In the one case, exact binary results are communicated; in the other case, a conversion takes place. I do not mean to keep beating you up for your lack of understanding of the question. But if I am not making myself clear to you, I am probably not making myself clear to others, especially the person who can indeed answer my question. ----- Having said all that, I am satisfied with my own "explanation" (rationalization). As for "why?" (motivation), I usually do not try to answer that question. But.... I suspect it is an accident of implementation. But if there was a conscious thought given to this, I suspect the macro behavior is an attempt to make the assignment of values to cells mimic data entry, which is indeed limited to 15 significant digits. (However, we are able to assign 1.79769313486231E+308 to Range("A1") in a macro, whereas we are limited to 9.99999999999999E+307 for manual data entry in Excel 2003. Details, details! ;-) On the other hand, with functions, we are accustomed to getting exact binary results. The 15-significant-digit limitation applies only to how Excel displays the value, not to the internal binary representation. For example, PI() returns the exactly 3.14159265358979,311599796346854418516159057617187 5 (in A1). But that value is displayed as 3.14159265358979 to 15 significant digits. If we enter 3.14159265358979 manually into A2, the exact value is 3.14159265358979,000737349451810587197542190551757 8125. Consequently, =A1-A2=0 returns FALSE. (Yes, =A1=A2 returns TRUE. That is because Excel's heuristic for handling "close to zero" values treats them as equal, but only in some contexts. For a poor explanation, see http://support.microsoft.com/kb/78113/en-us. Anyway, that has nothing to do with my question.) So my question becomes, if I have the following: A1: =PI() A2: =doit1() A3: (filled in by macro doit2) B3: =A2-A3=0 (FALSE!) where in doit1() we have doit1 = Range("A1") and in doit2() we have Range("A3") = Range("A1") why do (that is, explain the mechanism by which) A2 and A3 have different values, which is demonstrated by the fact that B3 results in FALSE? And to be clear, I want the macro behavior to mimic the function behavior. That is, I want the macro to assign the exact internal binary representation found in A1. So no change to function doit1 is warranted. In another posting to this thread, you wrote: I'll eagerly <vbg await Mike's response. As I noted in another posting in this thread, I suspect that Jerry attempted to explain this anomaly (failure to replicate the exact internal binary representation) some time ago when I posted a similar observation about copy-and-paste-special-value. Unfortunately, I cannot find that response. And I don't recall if Jerry's response was dispositive or merely speculation. Well, I suspect I beat that horse to death. I only hope this has clarified the question, if not for you, then for someone else. ----- original message ----- "Dave Peterson" wrote in message ... I didn't miss the point this time. You want to know why excel does this. I don't think anyone will be able to answer that question. They may be able to explain the behavior, but not the why. JoeU2004 wrote: "Dave Peterson" wrote: On the other hand, these 4 all returned True: =A1=A2 =A3=A4 =A2=A3 =A1=A4 That form is affected by Excel's attempt to hide "infinitesimal" differences. I wanted to avoid Excel's heuristic. That is why I wrote the comparison in the form =A1-A2=0. So maybe it's the subtraction that's (partially??) causing the trouble and the comparison to 0 causing the other portion. You continue to miss the point. There is no "trouble" with the comparisons. The "trouble" is with the different behavior of assignment of a numeric value to cell in a macro v. assignment of the same numeric value to a function name (i.e. returning the value). I am asking: why does the macro assignment change the binary value -- __that__ is the "trouble" -- whereas the function assignment preserves the binary value -- that is the __desired__ behavior. You seem to be overwhelmed by the details of the binary representation. I suggest that you sit this one out. ----- original message ----- "Dave Peterson" wrote in message ... You're right. I missed your point. I thought you wanted something like: Function myFunc() As Variant Dim myVal As Variant myVal = CDec(".12341234123412341234123412341234") myFunc = myVal 'or myFunc = "'" & myVal End Function I'm sure Mike Middleton will be able to offer a reasonable explanation (if you can get his attention). On the other hand, these 4 all returned True: =A1=A2 =A3=A4 =A2=A3 =A1=A4 So maybe it's the subtraction that's (partially??) causing the trouble and the comparison to 0 causing the other portion. I'll eagerly <vbg await Mike's response. JoeU2004 wrote: "Dave Peterson" wrote: VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. I think you missed the point. Let me rephrase.... Suppose we have the following VBA procedures: Function doit1() doit1 = Range("a2") End Function Sub doit2() Range("a4") = Range("a2") End Sub Enter the following in Excel: A1: 0.28 A2: =0.28 + 2^-54 A3: =doit1() A4: (to be filled in by macro doit2) Execute the doit2() macro, then enter the following in Excel: B1: =A1-A2=0 (FALSE) B2: =A3-A4=0 (FALSE!) B3: =A2-A3=0 (TRUE) B4: =A1-A4=0 (TRUE!) The FALSE value of B1 demonstrates that the internal binary representation of A1 and A2 are different as intended, even though we cannot see that when formatting to 15 decimal places, Excel's conversion limit. The FALSE value of B2 demonstrates that function doit1 returns a different internal binary representation than macro doit2 returns. Why is that? They both get their value from A2. The TRUE value of B3 demonstrates that function doit1 faithfully duplicates the internal binary representation in A2. The TRUE value of B4 demonstrates that macro doit2 converts A2 to the internal binary representation of the constant 0.28 (A1). Can we coerce macro doit2 to faithfully duplicate the internal binary representation in A2, as function doit1 does? (Without employing some object property/method trick. I really want to use expressions on the right-hand side of the assignment, and not rely on cell references like Range("a2"). I am using the latter only for this example to rule out discussions of differences between Excel and VBA, I hope.) Excel keeps track of 15 significant digits when you treat the entry as a number. Ostensibly, this has nothing to do with Excel's 15-significant-digit conversion limit. Unless.... Are you confirming my WAG, to wit: in a macro, but not in a function, Excel (or VBA?!) first converts the binary value of an assignment to a cell to a numeric string, subject to its 15-significant-digit conversion limit; then Excel converts the numeric string to binary as if it were entered manually. (Of course, we cannot have "an assignment to a cell" in a function. What I mean by "not in a function" is: the same binary-to-string-to-binary conversion does not happen for a function return value -- an assignment to the function name.) That is only speculation at the moment. If it is true, where should I have learned about that difference between macros cell assignments and functions return values? Arguably, this probably matters to only two people in the whole world. (And I'm not sure that Jerry even cares.) Most people try to avoid thinking about the binary representation, and that's just fine. PS: I am a software architect, so I relate to things better if I have an understanding of the architectural relationships among the components of a system. I really do know how Excel and VBA interact architecturally. Is there someplace I can go to get that architectural knowledge? Is there a "devil book" for Excel/VBA? ----- original message ----- "Dave Peterson" wrote in message ... Excel keeps track of 15 significant digits when you treat the entry as a number. VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. with Cells(1 + i + 9, 2) .numberformat = "@" 'text .value = x 'or maybe '.value = cstr(x) end with Untested, though. JoeU2004 wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
How to strore exact double from macro into Excel cell?
Good luck.
JoeU2004 wrote: "Dave Peterson" wrote: I didn't miss the point this time. With all due respect, obviously you did. You wrote: "So maybe it's the subtraction that's (partially??) causing the trouble and the comparison to 0 causing the other portion". So clearly, you think my question has something to do with the arithmetic results. My question has nothing to do with that. I am merely using the differences in the arithmetic results -- the internal binary representation -- to demonstrate the problem I am asking about. You want to know why excel does this. I don't think anyone will be able to answer that question. They may be able to explain the behavior, but not the why. I am not asking "why?" in the sense of "what is the motivation?". I used the word "why?" in the sense of "what is going on?". Yes, I am interested in the explanation of "the" behavior. What you do not seem to understand is what "the" refers to. I am asking about the difference in how VBA functions and macros communicate results back to Excel. In the one case, exact binary results are communicated; in the other case, a conversion takes place. I do not mean to keep beating you up for your lack of understanding of the question. But if I am not making myself clear to you, I am probably not making myself clear to others, especially the person who can indeed answer my question. ----- Having said all that, I am satisfied with my own "explanation" (rationalization). As for "why?" (motivation), I usually do not try to answer that question. But.... I suspect it is an accident of implementation. But if there was a conscious thought given to this, I suspect the macro behavior is an attempt to make the assignment of values to cells mimic data entry, which is indeed limited to 15 significant digits. (However, we are able to assign 1.79769313486231E+308 to Range("A1") in a macro, whereas we are limited to 9.99999999999999E+307 for manual data entry in Excel 2003. Details, details! ;-) On the other hand, with functions, we are accustomed to getting exact binary results. The 15-significant-digit limitation applies only to how Excel displays the value, not to the internal binary representation. For example, PI() returns the exactly 3.14159265358979,311599796346854418516159057617187 5 (in A1). But that value is displayed as 3.14159265358979 to 15 significant digits. If we enter 3.14159265358979 manually into A2, the exact value is 3.14159265358979,000737349451810587197542190551757 8125. Consequently, =A1-A2=0 returns FALSE. (Yes, =A1=A2 returns TRUE. That is because Excel's heuristic for handling "close to zero" values treats them as equal, but only in some contexts. For a poor explanation, see http://support.microsoft.com/kb/78113/en-us. Anyway, that has nothing to do with my question.) So my question becomes, if I have the following: A1: =PI() A2: =doit1() A3: (filled in by macro doit2) B3: =A2-A3=0 (FALSE!) where in doit1() we have doit1 = Range("A1") and in doit2() we have Range("A3") = Range("A1") why do (that is, explain the mechanism by which) A2 and A3 have different values, which is demonstrated by the fact that B3 results in FALSE? And to be clear, I want the macro behavior to mimic the function behavior. That is, I want the macro to assign the exact internal binary representation found in A1. So no change to function doit1 is warranted. In another posting to this thread, you wrote: I'll eagerly <vbg await Mike's response. As I noted in another posting in this thread, I suspect that Jerry attempted to explain this anomaly (failure to replicate the exact internal binary representation) some time ago when I posted a similar observation about copy-and-paste-special-value. Unfortunately, I cannot find that response. And I don't recall if Jerry's response was dispositive or merely speculation. Well, I suspect I beat that horse to death. I only hope this has clarified the question, if not for you, then for someone else. ----- original message ----- "Dave Peterson" wrote in message ... I didn't miss the point this time. You want to know why excel does this. I don't think anyone will be able to answer that question. They may be able to explain the behavior, but not the why. JoeU2004 wrote: "Dave Peterson" wrote: On the other hand, these 4 all returned True: =A1=A2 =A3=A4 =A2=A3 =A1=A4 That form is affected by Excel's attempt to hide "infinitesimal" differences. I wanted to avoid Excel's heuristic. That is why I wrote the comparison in the form =A1-A2=0. So maybe it's the subtraction that's (partially??) causing the trouble and the comparison to 0 causing the other portion. You continue to miss the point. There is no "trouble" with the comparisons. The "trouble" is with the different behavior of assignment of a numeric value to cell in a macro v. assignment of the same numeric value to a function name (i.e. returning the value). I am asking: why does the macro assignment change the binary value -- __that__ is the "trouble" -- whereas the function assignment preserves the binary value -- that is the __desired__ behavior. You seem to be overwhelmed by the details of the binary representation. I suggest that you sit this one out. ----- original message ----- "Dave Peterson" wrote in message ... You're right. I missed your point. I thought you wanted something like: Function myFunc() As Variant Dim myVal As Variant myVal = CDec(".12341234123412341234123412341234") myFunc = myVal 'or myFunc = "'" & myVal End Function I'm sure Mike Middleton will be able to offer a reasonable explanation (if you can get his attention). On the other hand, these 4 all returned True: =A1=A2 =A3=A4 =A2=A3 =A1=A4 So maybe it's the subtraction that's (partially??) causing the trouble and the comparison to 0 causing the other portion. I'll eagerly <vbg await Mike's response. JoeU2004 wrote: "Dave Peterson" wrote: VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. I think you missed the point. Let me rephrase.... Suppose we have the following VBA procedures: Function doit1() doit1 = Range("a2") End Function Sub doit2() Range("a4") = Range("a2") End Sub Enter the following in Excel: A1: 0.28 A2: =0.28 + 2^-54 A3: =doit1() A4: (to be filled in by macro doit2) Execute the doit2() macro, then enter the following in Excel: B1: =A1-A2=0 (FALSE) B2: =A3-A4=0 (FALSE!) B3: =A2-A3=0 (TRUE) B4: =A1-A4=0 (TRUE!) The FALSE value of B1 demonstrates that the internal binary representation of A1 and A2 are different as intended, even though we cannot see that when formatting to 15 decimal places, Excel's conversion limit. The FALSE value of B2 demonstrates that function doit1 returns a different internal binary representation than macro doit2 returns. Why is that? They both get their value from A2. The TRUE value of B3 demonstrates that function doit1 faithfully duplicates the internal binary representation in A2. The TRUE value of B4 demonstrates that macro doit2 converts A2 to the internal binary representation of the constant 0.28 (A1). Can we coerce macro doit2 to faithfully duplicate the internal binary representation in A2, as function doit1 does? (Without employing some object property/method trick. I really want to use expressions on the right-hand side of the assignment, and not rely on cell references like Range("a2"). I am using the latter only for this example to rule out discussions of differences between Excel and VBA, I hope.) Excel keeps track of 15 significant digits when you treat the entry as a number. Ostensibly, this has nothing to do with Excel's 15-significant-digit conversion limit. Unless.... Are you confirming my WAG, to wit: in a macro, but not in a function, Excel (or VBA?!) first converts the binary value of an assignment to a cell to a numeric string, subject to its 15-significant-digit conversion limit; then Excel converts the numeric string to binary as if it were entered manually. (Of course, we cannot have "an assignment to a cell" in a function. What I mean by "not in a function" is: the same binary-to-string-to-binary conversion does not happen for a function return value -- an assignment to the function name.) That is only speculation at the moment. If it is true, where should I have learned about that difference between macros cell assignments and functions return values? Arguably, this probably matters to only two people in the whole world. (And I'm not sure that Jerry even cares.) Most people try to avoid thinking about the binary representation, and that's just fine. PS: I am a software architect, so I relate to things better if I have an understanding of the architectural relationships among the components of a system. I really do know how Excel and VBA interact architecturally. Is there someplace I can go to get that architectural knowledge? Is there a "devil book" for Excel/VBA? ----- original message ----- "Dave Peterson" wrote in message ... Excel keeps track of 15 significant digits when you treat the entry as a number. VBA keeps track of lots(?) more if you use cDec() (28 I think). Maybe you could use cdec() or just try returning text. with Cells(1 + i + 9, 2) .numberformat = "@" 'text .value = x 'or maybe '.value = cstr(x) end with Untested, though. JoeU2004 wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
How to strore exact double from macro into Excel cell?
Sorry for the long delayed response. When I briefly scanned this thread a
while back, I assumed that it had been put to bed, and so didn't read it carefully and didnt give it any thought. Tonight, I accidentally typed September instead of October into my Google message filter, and ended up reading it a bit more carefully. My empirical observation is that prior to Excel 2007, there were certain values that for unexplained reasons were not permitted as an Excel constant, even though they were permitted as the result of calculations. What typically happens, is what you have observed here, all the binary variants for the 15 digit display collapse into a single binary value for each of the 'unpermitted' constants. Consider the following VBA function to convert from a string to a floating point Function D2F(x) D2F = CDbl(x) End Function In a worksheet cell, =D2F("0.279999999999999527044991509683313779532909 393310546875") will give a floating point value in Excel that has exactly the intended value. If you Copy and Paste Special|Values from this cell into another, you will find that the destination cell contains 0.280000000000000026645352591003756970167160034179 6875 Thus, the issue is not with your VBA code, but is due to the fact that Excel simply does not permit these binary values as constants in cells. Excel 2007 seemed to remove this restriction http://groups.google.com/group/micro...31dabc74c22d38 but it appears that you (like me) do not use Excel 2007. Jerry "JoeU2004" wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! |
How to strore exact double from macro into Excel cell?
Jerry,
I have your formula in A1 and it returns 0.28 When I Copy/ Paste Special | Values to C1 I get 0.28 No amount of tapping the Increase decimal will give anything but 0.2800000000..... Am I missing something? -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jerry W. Lewis" wrote in message ... Sorry for the long delayed response. When I briefly scanned this thread a while back, I assumed that it had been put to bed, and so didn't read it carefully and didn't give it any thought. Tonight, I accidentally typed September instead of October into my Google message filter, and ended up reading it a bit more carefully. My empirical observation is that prior to Excel 2007, there were certain values that for unexplained reasons were not permitted as an Excel constant, even though they were permitted as the result of calculations. What typically happens, is what you have observed here, all the binary variants for the 15 digit display collapse into a single binary value for each of the 'unpermitted' constants. Consider the following VBA function to convert from a string to a floating point Function D2F(x) D2F = CDbl(x) End Function In a worksheet cell, =D2F("0.279999999999999527044991509683313779532909 393310546875") will give a floating point value in Excel that has exactly the intended value. If you Copy and Paste Special|Values from this cell into another, you will find that the destination cell contains 0.280000000000000026645352591003756970167160034179 6875 Thus, the issue is not with your VBA code, but is due to the fact that Excel simply does not permit these binary values as constants in cells. Excel 2007 seemed to remove this restriction http://groups.google.com/group/micro...31dabc74c22d38 but it appears that you (like me) do not use Excel 2007. Jerry "JoeU2004" wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! |
How to strore exact double from macro into Excel cell?
"Bernard Liengme" wrote:
No amount of tapping the Increase decimal will give anything but 0.2800000000..... Am I missing something? Yes, the fact that Excel will display only the first 15 significant digits, rounding the 16th, whereas Jerry and I are looking at the internal binary representation. For example, when we enter 0.28 into A1 and format with 15 decimal places (or more), Excel will display 0.280...0. But the exact internal value is 0.280000000000000,02664535259100375697016716003417 96875. (I use a comma to demarcate the first 15 significant digits.) Now enter =0.28+2^-54 into A2. Excel will still display only 0.280...0. But you can verify it is not the same as 0.28 by entering =A1-A2=0. It will return FALSE. In fact, A2 is exactly 0.280000000000000,08215650382226158399134874343872 0703125. ----- original message ----- "Bernard Liengme" wrote in message ... Jerry, I have your formula in A1 and it returns 0.28 When I Copy/ Paste Special | Values to C1 I get 0.28 No amount of tapping the Increase decimal will give anything but 0.2800000000..... Am I missing something? -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jerry W. Lewis" wrote in message ... Sorry for the long delayed response. When I briefly scanned this thread a while back, I assumed that it had been put to bed, and so didn't read it carefully and didn't give it any thought. Tonight, I accidentally typed September instead of October into my Google message filter, and ended up reading it a bit more carefully. My empirical observation is that prior to Excel 2007, there were certain values that for unexplained reasons were not permitted as an Excel constant, even though they were permitted as the result of calculations. What typically happens, is what you have observed here, all the binary variants for the 15 digit display collapse into a single binary value for each of the 'unpermitted' constants. Consider the following VBA function to convert from a string to a floating point Function D2F(x) D2F = CDbl(x) End Function In a worksheet cell, =D2F("0.279999999999999527044991509683313779532909 393310546875") will give a floating point value in Excel that has exactly the intended value. If you Copy and Paste Special|Values from this cell into another, you will find that the destination cell contains 0.280000000000000026645352591003756970167160034179 6875 Thus, the issue is not with your VBA code, but is due to the fact that Excel simply does not permit these binary values as constants in cells. Excel 2007 seemed to remove this restriction http://groups.google.com/group/micro...31dabc74c22d38 but it appears that you (like me) do not use Excel 2007. Jerry "JoeU2004" wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! |
How to strore exact double from macro into Excel cell?
Sorry, Bernard, I didn't consider that a more general audence might still be
following such an extremely specialized thread. You can use my D2D VBA function from http://groups.google.com/group/micro...fb95785d1eaff5 to see the decimal equivalent of the internal binary representation. Cheers, Jerry "Bernard Liengme" wrote: Jerry, I have your formula in A1 and it returns 0.28 When I Copy/ Paste Special | Values to C1 I get 0.28 No amount of tapping the Increase decimal will give anything but 0.2800000000..... Am I missing something? -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jerry W. Lewis" wrote in message ... Sorry for the long delayed response. When I briefly scanned this thread a while back, I assumed that it had been put to bed, and so didn't read it carefully and didn't give it any thought. Tonight, I accidentally typed September instead of October into my Google message filter, and ended up reading it a bit more carefully. My empirical observation is that prior to Excel 2007, there were certain values that for unexplained reasons were not permitted as an Excel constant, even though they were permitted as the result of calculations. What typically happens, is what you have observed here, all the binary variants for the 15 digit display collapse into a single binary value for each of the 'unpermitted' constants. Consider the following VBA function to convert from a string to a floating point Function D2F(x) D2F = CDbl(x) End Function In a worksheet cell, =D2F("0.279999999999999527044991509683313779532909 393310546875") will give a floating point value in Excel that has exactly the intended value. If you Copy and Paste Special|Values from this cell into another, you will find that the destination cell contains 0.280000000000000026645352591003756970167160034179 6875 Thus, the issue is not with your VBA code, but is due to the fact that Excel simply does not permit these binary values as constants in cells. Excel 2007 seemed to remove this restriction http://groups.google.com/group/micro...31dabc74c22d38 but it appears that you (like me) do not use Excel 2007. Jerry "JoeU2004" wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! |
How to strore exact double from macro into Excel cell?
Sorry for misreading the message late last night!
Now in D1 I have =D2D(C1) and I get 0.280000000000000026645352591 I except you have a newer D2D which displays more digits. Now I will butt out Jerry: what are your views on the new Excel 2010 stats functions? Bernard "Jerry W. Lewis" wrote in message ... Sorry, Bernard, I didn't consider that a more general audence might still be following such an extremely specialized thread. You can use my D2D VBA function from http://groups.google.com/group/micro...fb95785d1eaff5 to see the decimal equivalent of the internal binary representation. Cheers, Jerry "Bernard Liengme" wrote: Jerry, I have your formula in A1 and it returns 0.28 When I Copy/ Paste Special | Values to C1 I get 0.28 No amount of tapping the Increase decimal will give anything but 0.2800000000..... Am I missing something? -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jerry W. Lewis" wrote in message ... Sorry for the long delayed response. When I briefly scanned this thread a while back, I assumed that it had been put to bed, and so didn't read it carefully and didn't give it any thought. Tonight, I accidentally typed September instead of October into my Google message filter, and ended up reading it a bit more carefully. My empirical observation is that prior to Excel 2007, there were certain values that for unexplained reasons were not permitted as an Excel constant, even though they were permitted as the result of calculations. What typically happens, is what you have observed here, all the binary variants for the 15 digit display collapse into a single binary value for each of the 'unpermitted' constants. Consider the following VBA function to convert from a string to a floating point Function D2F(x) D2F = CDbl(x) End Function In a worksheet cell, =D2F("0.279999999999999527044991509683313779532909 393310546875") will give a floating point value in Excel that has exactly the intended value. If you Copy and Paste Special|Values from this cell into another, you will find that the destination cell contains 0.280000000000000026645352591003756970167160034179 6875 Thus, the issue is not with your VBA code, but is due to the fact that Excel simply does not permit these binary values as constants in cells. Excel 2007 seemed to remove this restriction http://groups.google.com/group/micro...31dabc74c22d38 but it appears that you (like me) do not use Excel 2007. Jerry "JoeU2004" wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! |
How to strore exact double from macro into Excel cell?
New topic: Excel 2010
Your question is the first thing I have heard about Excel 2010. I would be very interested in testing, but stopped getting replies from my former MS contacts shortly after 2007 was released. I was very vocal that 2007 should give concrete improvements (such as improved statistical functions) to offset the re-learning curve of the new interface; but apparently I had no impact on that point (though I would be glad to see it in 2010). I did not expect the larger worksheet and formula limits of 2007 to accomplish that offset with corporate IT decision makers: - the larger limits would be viewed as encouraging uses of Excel that IT would probably prefer to discourage, as better suited to other tools - improved statistical functions would be viewed as improving the reliability of existing uses, regardless of whether IT wanted those uses to ultimately remain in Excel or not I dont know about all industries, but I am not aware of any companies in my industry that have migrated. Jerry "Bernard Liengme" wrote: .... Jerry: what are your views on the new Excel 2010 stats functions? Bernard |
How to strore exact double from macro into Excel cell?
"Jerry W. Lewis" wrote:
My empirical observation is that prior to Excel 2007, there were certain values that for unexplained reasons were not permitted as an Excel constant, even though they were permitted as the result of calculations. Well, that would come as no surprise to me. We are used to the fact that for manual data entry, Excel stops converting after the 15th significant digit, filling in the integer part with zeros as needed until the decimal point. Example #1.... Both 0.2799999999999995 (16 sig digits) and 0.279999999999999 (15 sig digits) have the same internal representation when entered manually in Excel, namely the representation of the shorter (0.279999999999999,0274446304283628705888986587524 4140625). In VBA, they have different internal representations; the longer constant is represented internally as 0.279999999999999,52704499150968331377953290939331 0546875. Thus, the issue is not with your VBA code, but is due to the fact that Excel simply does not permit these binary values as constants in cells. And that would have come as no surprise to me if I had written Range("A3")="0.2799999999999995". I would expect that to have the same behavior as manual data entry. The surprise for me is that Range("A4")=CDbl("0.2799999999999995") also seems to be treated like manual data entry (i.e. with the same limitations). It has the same internal representation as 0.279999999999999, not the internal representation of CDbl("0.2799999999999995"). But okay, I can see your point: it is a constant, not a value returned by a formula. So arguably, it should be limited by the rules for constants. However.... There are examples where the cell value assignment in a Sub does indeed preserve the internal binary representation, even though it can __not__ be entered manually as a constant in Excel. Example #2.... When 0.1111111111111112 (16 sig digits) is entered into a cell in Excel, it has the same binary representation as 0.1111111111111110 (15 sig digits), namely 0.111111111111110,99392090295623347628861665725708 0078125. The same is true for the result of Range("A3")="0.1111111111111112" in VBA. But the result of Range("A4")=CDbl("0.1111111111111112") has the same binary representation as CDbl("0.1111111111111112"), namely 0.111111111111111,20208772007345032761804759502410 888671875. The same is true when we copy-and-paste-special-value A4 into A5: it preserves __that__ binary representation, even though it cannot be entered as a constant in Excel. Example #3.... When 0.27999999999999897 (17 sig digits) is entered into a cell in Excel, it has the same binary representation as 0.27999999999999897 (15 sig digits), namely 0.279999999999997,97273275703446415718644857406616 2109375. The same is true for the result of Range("A3")="0.27999999999999897" in VBA. But the result of Range("A4")=CDbl("0.27999999999999897") has the same binary representation as CDbl("0.27999999999999897"), namely 0.279999999999998,97193347919710504356771707534790 0390625. The same is true when we copy-and-paste-special-value A4 into A5: it preserves __that__ binary representation, even though it cannot be entered as a constant in Excel. For assignments of the form Range(...)=CDbl(...) or any numeric expression, I had previously speculated that, first, the binary representation is converted to a numeric string as Excel would do for cell value formatted to display 15 significant digits (i.e. effectively rounding the 16th significant digit), then the numeric string is converted the same way that data entry is (i.e. stop converting after 15 significant digits, filling in the integer part with zeros as needed until the decimal place). That rule fits Example #1. 0.279999999999999,52704499150968331377953290939331 0546875 is converted to "0.280000000000000", which is then converted to the binary representation of 0.28, namely 0.280000000000000,02664535259100375697016716003417 96875. But that rule does __not__ fit Examples #2 and #3. I have a contorted rule that seems to fit all three examples. But it flies in the face of Occam's Razor. I am still looking for a simple explanation. I assumed that it had been put to bed Yes, I believe it has, at least for the purpose of this NG, even though I still do not have a complete explanation to my satisfaction. The only point was: the assignment of a value to a cell in a Sub may or may not behave differently than the assignment of a value to a function name. Sometimes (perhaps most of the time), the assignment in a Sub does not store exactly the same binary representation. This probably has little or no impact on the vast majority of users, almost all of whom are probably oblivious to the internal binary representation anyway, except when it surfaces as a numerical aberration that adversely affects arithmetic and comparisons. ----- original message ----- "Jerry W. Lewis" wrote in message ... Sorry for the long delayed response. When I briefly scanned this thread a while back, I assumed that it had been put to bed, and so didn't read it carefully and didnt give it any thought. Tonight, I accidentally typed September instead of October into my Google message filter, and ended up reading it a bit more carefully. My empirical observation is that prior to Excel 2007, there were certain values that for unexplained reasons were not permitted as an Excel constant, even though they were permitted as the result of calculations. What typically happens, is what you have observed here, all the binary variants for the 15 digit display collapse into a single binary value for each of the 'unpermitted' constants. Consider the following VBA function to convert from a string to a floating point Function D2F(x) D2F = CDbl(x) End Function In a worksheet cell, =D2F("0.279999999999999527044991509683313779532909 393310546875") will give a floating point value in Excel that has exactly the intended value. If you Copy and Paste Special|Values from this cell into another, you will find that the destination cell contains 0.280000000000000026645352591003756970167160034179 6875 Thus, the issue is not with your VBA code, but is due to the fact that Excel simply does not permit these binary values as constants in cells. Excel 2007 seemed to remove this restriction http://groups.google.com/group/micro...31dabc74c22d38 but it appears that you (like me) do not use Excel 2007. Jerry "JoeU2004" wrote: I know this is "stoopid" because I'm sure I have done this before with no problem. But for some reason, today -- with the stars, sun, moon and earth aligned such as they are -- the following macro is not doing what I expect. What am I doing wrong? Sub doit() Dim x As Double, s As String, i As Long For i = -9 To 8 x = 0.28 + i * 2 ^ -54 Debug.Print dbl2dec(x) Cells(1 + i + 9, 2) = x Next i End Sub dbl2dec() is my function that formats binary floating point exactly. The debug.print output is what I expect, namely: i=-9: 0.279999999999999,52704499150968331377953290939331 0546875 i=-8: 0.279999999999999,58255614274094114080071449279785 15625 i=-7: 0.279999999999999,63806729397219896782189607620239 2578125 i=-6: 0.279999999999999,69357844520345679484307765960693 359375 i=-5: 0.279999999999999,74908959643471462186425924301147 4609375 i=-4: 0.279999999999999,80460074766597244888544082641601 5625 i=-3: 0.279999999999999,86011189889723027590662240982055 6640625 i=-2: 0.279999999999999,91562305012848810292780399322509 765625 i=-1: 0.279999999999999,97113420135974592994898557662963 8671875 i=0: 0.280000000000000,02664535259100375697016716003417 96875 i=1: 0.280000000000000,08215650382226158399134874343872 0703125 i=2: 0.280000000000000,13766765505351941101253032684326 171875 i=3: 0.280000000000000,19317880628477723803371191024780 2734375 i=4: 0.280000000000000,24868995751603506505489349365234 375 i=5: 0.280000000000000,30420110874729289207607507705688 4765625 i=6: 0.280000000000000,35971225997855071909725666046142 578125 i=7: 0.280000000000000,41522341120980854611843824386596 6796875 i=8: 0.280000000000000,47073456244106637313961982727050 78125 So "x" is indeed getting the values that I expect. But the results in B1:B18 are all the same binary representation of the constant 0.28 (see i=0 above), not the 18 different binary representations that I generate in the macro. (C1 is the formula =dbl2dec(B1), which is copied down through C18.) As a double-check, I do get the 18 different binary representations if A1:A18 are the values -9 through 8, and I put the following formula into B1 and copy down through B18: =0.28 + A1 * 2^-54 I think that rules out any user errors w.r.t calculation modes (manual v. auto; "precision as displayed"; etc). I know that I can work around the problem by changing the cell assignment in the macro to: Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54" But why can't I return the exact binary representation in cells(...).value directly? As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Please thump me on the head and tell me what it is. ----- Aha! I wrote: As I said, I'm certain I have done this before. So I must be having a "senior moment" and doing something "stoopid". Actually, what I have probably done before is: Function doit2(i As Long) As Double Dim x As Double, s As String x = 0.28 + i * 2 ^ -54 Debug.Print "i=" & i & ": " & dbl2dec(x) doit2 = x End Function where I have =doit3(A1) in B1 and copy down through B18, and A1:A18 are the values -9 through 8. And __that__ does work as expected. So perhaps my question is: why doesn't this work using a macro? And more to the point: how do I make it work using a macro, other than storing a formula into the cell? ----- WAG: When I store into .value in a macro, I wonder if Excel is converting the double to a numeric string, subject to its display limit of 15 significant digits, then treating it as data entry and converting it back to a number. Say what?! |
How to strore exact double from macro into Excel cell?
A follow-up to be sure that we have communicated:
This phenomenon is different than truncation on input. For example, all the binary variants of 0.283 are permitted as Excel constants. My vague recollection is that this phenomenon is more complex than simply replacing x with VALUE(x&"") when x&"" has no more than 2 decimal places, but that was so many years ago that I cannot be certain. Also, even though VBA and Excel both only display 15 digits, they are different in the way that they handle input with more than 15 digits: - Excel always truncates input to 15 decimal digits before converting the input to binary, thus input of 0.12399999999999961 will be stored as the binary representation of 0.123999999999999, and then correctly displayed to 15 decimal digits as 0.123999999999999 - VBA converts the full input to binary, thus input of 0.12399999999999961 will be stored as the binary representation of 0.12399999999999961, and then correctly displayed to 15 decimal digits as 0.124. If you subsequently edit that line of code, then the underlying value will actually change to the binary representation of 0.124 As a result, if I am doing accuracy checks in a worksheet, then my reference values would be given to full precision by =D2F("0.12399999999999961") If I am doing numerical work in VBA that involves floating point constants, then I either use CDbl("0.12399999999999961") or maintain the code in a text editor to be insure that the full precision of the numeric constants is not lost. Jerry |
How to strore exact double from macro into Excel cell?
"Jerry W. Lewis" wrote:
A follow-up to be sure that we have communicated: I agree that this is a good summary of the points that I tried to make. This phenomenon is different than truncation on input. Right. Initially, you had written: "all the binary variants for the 15 digit display collapse into a single binary value for each of the 'unpermitted' constants", and "the issue is not with your VBA code, but is due to the fact that Excel simply does not permit these binary values as constants in cells". I understood you to be asserting that the problem relates to how Excel handles entering constants into cells manually; that the "unpermitted constants" are constants with digits beyond the first 15 significant digits. I refuted that assertion with examples "where the cell value assignment in a Sub does indeed preserve the internal binary representation, even though it can __not__ be entered manually as a constant in Excel". If, instead, your term "unpermitted constants" is meant to refer to some list or category of constants, I would be very surprised if that is the case. It is more likely explained by an algorithm (or heuristic or rule). If you have a theory about the algorithm, I'd be interested in hearing it. all the binary variants of 0.283 are permitted as Excel constants. Fascinating example. I believe you are trying to say that the following preserves the VBA binary representation for all constants that are displayed as 0.283000000000000 when formatted to 15 significant digits, namely for all "i" equal to -8 to 9: Range("A1") = 0.283 + i*2^-54 I agree. And I believe the examples for "i" equal to 1 to 9 contradict even the contorted rule that I had devised to explain all this. Back to the drawing boards! (Counter-example: CDbl("'0.28000000000000047"), 17 sig digits, whose binary representation is not preserved when assigned to a cell in VBA.) Nitpick: I would not say that these "variants" are "permitted as Excel constants" per se. To me, that means we can enter them into a cell as a constant. I know that is not what you mean, because I know you understand that for manual data entry, Excel "truncates input to 15 [significant] digits before converting the input to binary". (You said that so much better than I did.) I would say, as I presume you mean, that the assignment of these values to a cell in a macro preserves the VBA binary representation. ----- original message ----- "Jerry W. Lewis" wrote in message ... A follow-up to be sure that we have communicated: This phenomenon is different than truncation on input. For example, all the binary variants of 0.283 are permitted as Excel constants. My vague recollection is that this phenomenon is more complex than simply replacing x with VALUE(x&"") when x&"" has no more than 2 decimal places, but that was so many years ago that I cannot be certain. Also, even though VBA and Excel both only display 15 digits, they are different in the way that they handle input with more than 15 digits: - Excel always truncates input to 15 decimal digits before converting the input to binary, thus input of 0.12399999999999961 will be stored as the binary representation of 0.123999999999999, and then correctly displayed to 15 decimal digits as 0.123999999999999 - VBA converts the full input to binary, thus input of 0.12399999999999961 will be stored as the binary representation of 0.12399999999999961, and then correctly displayed to 15 decimal digits as 0.124. If you subsequently edit that line of code, then the underlying value will actually change to the binary representation of 0.124 As a result, if I am doing accuracy checks in a worksheet, then my reference values would be given to full precision by =D2F("0.12399999999999961") If I am doing numerical work in VBA that involves floating point constants, then I either use CDbl("0.12399999999999961") or maintain the code in a text editor to be insure that the full precision of the numeric constants is not lost. Jerry |
How to strore exact double from macro into Excel cell?
PS....
I wrote: "Jerry W. Lewis" wrote: all the binary variants of 0.283 are permitted as Excel constants. [....] I believe you are trying to say that the following preserves the VBA binary representation Previously, I wrote: "okay, I can see your point: it is a constant, not a value returned by a formula. So arguably, it should be limited by the rules for constants", referring to the contraints on entering constants into an Excel cell manually. I intended to paraphrase what I thought you were saying. But now that I understand what you mean by "permitted as Excel constants" in this context, perhaps you were never suggesting that "point" in the first place. Too bad. I thought it was an excellent theory, even though I demonstrated that it is wrong. If I were implementing some constraint on VBA binary representations assigned to cells as constants (but I wouldn't), that is the rule I would specify. It is simple to implement; and it makes good sense. Two dead give-aways that should tell us MS would never have implemented it ;-). Anyway, sorry if I misunderstood your original point. I did not mean to be nitpicky about your terminology. It's just that I realized that it was the crux of my original misunderstanding. Other than that, I think we are on the same page. ----- original message ----- "JoeU2004" wrote in message ... "Jerry W. Lewis" wrote: A follow-up to be sure that we have communicated: I agree that this is a good summary of the points that I tried to make. This phenomenon is different than truncation on input. Right. Initially, you had written: "all the binary variants for the 15 digit display collapse into a single binary value for each of the 'unpermitted' constants", and "the issue is not with your VBA code, but is due to the fact that Excel simply does not permit these binary values as constants in cells". I understood you to be asserting that the problem relates to how Excel handles entering constants into cells manually; that the "unpermitted constants" are constants with digits beyond the first 15 significant digits. I refuted that assertion with examples "where the cell value assignment in a Sub does indeed preserve the internal binary representation, even though it can __not__ be entered manually as a constant in Excel". If, instead, your term "unpermitted constants" is meant to refer to some list or category of constants, I would be very surprised if that is the case. It is more likely explained by an algorithm (or heuristic or rule). If you have a theory about the algorithm, I'd be interested in hearing it. all the binary variants of 0.283 are permitted as Excel constants. Fascinating example. I believe you are trying to say that the following preserves the VBA binary representation for all constants that are displayed as 0.283000000000000 when formatted to 15 significant digits, namely for all "i" equal to -8 to 9: Range("A1") = 0.283 + i*2^-54 I agree. And I believe the examples for "i" equal to 1 to 9 contradict even the contorted rule that I had devised to explain all this. Back to the drawing boards! (Counter-example: CDbl("'0.28000000000000047"), 17 sig digits, whose binary representation is not preserved when assigned to a cell in VBA.) Nitpick: I would not say that these "variants" are "permitted as Excel constants" per se. To me, that means we can enter them into a cell as a constant. I know that is not what you mean, because I know you understand that for manual data entry, Excel "truncates input to 15 [significant] digits before converting the input to binary". (You said that so much better than I did.) I would say, as I presume you mean, that the assignment of these values to a cell in a macro preserves the VBA binary representation. ----- original message ----- "Jerry W. Lewis" wrote in message ... A follow-up to be sure that we have communicated: This phenomenon is different than truncation on input. For example, all the binary variants of 0.283 are permitted as Excel constants. My vague recollection is that this phenomenon is more complex than simply replacing x with VALUE(x&"") when x&"" has no more than 2 decimal places, but that was so many years ago that I cannot be certain. Also, even though VBA and Excel both only display 15 digits, they are different in the way that they handle input with more than 15 digits: - Excel always truncates input to 15 decimal digits before converting the input to binary, thus input of 0.12399999999999961 will be stored as the binary representation of 0.123999999999999, and then correctly displayed to 15 decimal digits as 0.123999999999999 - VBA converts the full input to binary, thus input of 0.12399999999999961 will be stored as the binary representation of 0.12399999999999961, and then correctly displayed to 15 decimal digits as 0.124. If you subsequently edit that line of code, then the underlying value will actually change to the binary representation of 0.124 As a result, if I am doing accuracy checks in a worksheet, then my reference values would be given to full precision by =D2F("0.12399999999999961") If I am doing numerical work in VBA that involves floating point constants, then I either use CDbl("0.12399999999999961") or maintain the code in a text editor to be insure that the full precision of the numeric constants is not lost. Jerry |
How to strore exact double from macro into Excel cell?
"JoeU2004" wrote:
.... Other than that, I think we are on the same page. Great! As a final summary/communication check, consider I know of only 5 ways to get a constant into a cell 1. Enter the value 2. Paste the value from the clipboard 3. Paste Special|Values that were copied from an Excel cell 4. Poke the value directly into the cell from VBA (as your code does) 5. Create the workbook in another application that will reliably allow the full IEEE DP values as constants. For instance, while http://www.byedesign.co.uk/s32/spre32en.zip does filter input values, its Paste Special|Values preserves unchanged the binary value calculated by a formula The following observations on these methods are based primarily on experience with Excel 2003 and earlier. I know from a period of testing that there are some differences in 2007; but I do not currently have access to 2007, and so cannot verify its behavior. 1. Data entry in Excel appears to always truncate to 15 digits before converting to binary. Even in instances where the display of that converted value is wrong, such as http://support.microsoft.com/kb/161234 the underlying binary value is consistent with the preceding statement. 2. I would expect pasting from the clipboard to use the same conversion routine as data entry, and have seen nothing to contradict this expectation. 3. From its name, I would expect Copy and Paste Special|Values to place the original binary value in the cell as a constant instead of a formula. My expectation is usually met when the 15 digit display contains more than 2 decimal places. My expectation is rarely met when the 15 digit display is not an integer but contains 1 or 2 decimal places. I conclude that Paste Special|Values basically does what I expect, but that an additional filter is applied. 4. For traceability of results, I prefer live formulas (possibly calculated from VBA functions), so I dont have much experience to draw on; but so far, it seems to behave like #3. 5. If you open a workbook that already has constants that would have been changed by the additional filter of #3 & #4, they are not changed simply by opening the file. It would be interesting to see whether Excel 2007 (which does not seem to natively use the filter of #3 & #4) employs the filter of #3 & #4 when saving in 2003 format. Cheers, Jerry |
How to strore exact double from macro into Excel cell?
Jerry, sorry to be off topic but further to Office 2010 PLEASE contact me
directly - email addy from my website. -- Bernard "Jerry W. Lewis" wrote in message ... "JoeU2004" wrote: ... Other than that, I think we are on the same page. Great! As a final summary/communication check, consider I know of only 5 ways to get a constant into a cell 1. Enter the value 2. Paste the value from the clipboard 3. Paste Special|Values that were copied from an Excel cell 4. Poke the value directly into the cell from VBA (as your code does) 5. Create the workbook in another application that will reliably allow the full IEEE DP values as constants. For instance, while http://www.byedesign.co.uk/s32/spre32en.zip does filter input values, its Paste Special|Values preserves unchanged the binary value calculated by a formula The following observations on these methods are based primarily on experience with Excel 2003 and earlier. I know from a period of testing that there are some differences in 2007; but I do not currently have access to 2007, and so cannot verify its behavior. 1. Data entry in Excel appears to always truncate to 15 digits before converting to binary. Even in instances where the display of that converted value is wrong, such as http://support.microsoft.com/kb/161234 the underlying binary value is consistent with the preceding statement. 2. I would expect pasting from the clipboard to use the same conversion routine as data entry, and have seen nothing to contradict this expectation. 3. From its name, I would expect Copy and Paste Special|Values to place the original binary value in the cell as a constant instead of a formula. My expectation is usually met when the 15 digit display contains more than 2 decimal places. My expectation is rarely met when the 15 digit display is not an integer but contains 1 or 2 decimal places. I conclude that Paste Special|Values basically does what I expect, but that an additional filter is applied. 4. For traceability of results, I prefer live formulas (possibly calculated from VBA functions), so I don't have much experience to draw on; but so far, it seems to behave like #3. 5. If you open a workbook that already has constants that would have been changed by the additional filter of #3 & #4, they are not changed simply by opening the file. It would be interesting to see whether Excel 2007 (which does not seem to natively use the filter of #3 & #4) employs the filter of #3 & #4 when saving in 2003 format. Cheers, Jerry |
How to strore exact double from macro into Excel cell?
"Jerry W. Lewis" wrote:
Other than that, I think we are on the same page. Great! As a final summary/communication check, consider Well, you seem to have wandered off-page and returned to the Table of Contents :-). But just to refine your understanding.... 2. I would expect pasting from the clipboard to use the same conversion routine as data entry, and have seen nothing to contradict this expectation. That depends on the source of the copy (i.e. the type of clipboard object). Certainly that is the case when the source is Notepad or Word, for example, because what is pasted (ctrl+V) is text. But that is not the case when pasting from an Excel 2003 object. To verify, use your example of "variants" of 0.283. In one instance of Excel 2003, enter the following into 3 cells: =0.283 - 2^-54 =0.283 =0.283 + 2^54 Copy (ctrl+C) the 3 cells. In a separate instance of Excel[*], paste (ctrl+V) into A1:A3. Now enter: B1: =A1-A2=0 B2: =A3-A2=0 B3: =A1-A3=0 B1, B2 and B3 are all FALSE, demonstrating that paste preserved the binary representations in this case. (Technically, it only verifies that something is different. But I did verify that the pasted binary representations are indeed the same as the original ones.) That seems to contradict your stated expectation. Of course, paste fails to preserve the binary representation in the cases that I previously demonstrated a cell assignment in VBA fails to, for example 0.28 +/- 2^-54. So, pasting (ctrl+V) from the clipboard appears to use the same "filter"' (as you put it) that VBA cell assignments and paste-special-value (within the same Excel instance) use. (It is anyone's guess whether it is truly the same filter or merely a filter with apparently similar behavior.) Note: By "cell assignment in VBA", I mean assignments of the form Range(...)=numericExpression. There are other ways to assign values to cells in VBA. I have not tested with them at all. I probably should, just in case one of them solves my original problem. But my ability to do so is limited by the extent of my knowledge of VBA. [*] To confirm a common understanding of "separate instance of Excel", copy a cell in the first instance, then paste-special-value into the second instance. If you truly have separate instances of Excel, the choices presented by PSV will be objects. If, instead, you mistakenly opened two workbooks in the same instance of Excel, PSV will permit you to paste Value et al. The experiment would still work as described. But the results are meaningless relative to what I am trying to demonstrate. 3. From its name, I would expect Copy and Paste Special|Values to place the original binary value in the cell as a constant instead of a formula. My expectation is usually met when the 15 digit display contains more than 2 decimal places. My expectation is rarely met when the 15 digit display is not an integer but contains 1 or 2 decimal places. Ding, ding, ding! By jove, I think you've got it! Barring a counter-example, perhaps the rule is: whenever the converted binary representation is a numeric string of 15 digits or less (not counting trailing zero fractional digits), and it has 0, 1 or 2 decimal places, PSV uses the binary representation of the converted numeric string instead of the original binary representation. Examples: 12345, 12345.6 and 12345.67 plus 2^-39, which adds 1 to the least-significant bit of each base number. Otherwise, PSV uses the original binary representation. Example: 12345.678 + 2^-39. The same rule applies to a cell assignment in VBA (as defined above) and to pasting (ctrl+V) from an Excel object on the clipboard. Well done! It is exactly the kind of simple rule that I was expecting: simple to specify, and straight-forward to implement. Damn! It's not that simple, of course. But that does seem to be the case for numbers between 0.01 and 5368709.12 (!). I did not test every number in that range, of course. My methodology was to try the numbers 1, 12, 123, 1234, ..., 123456789012345 with 0, 1, 2 and 3 decimal places, adding and subtracting an appropriate power of 2 so as to add or subtract one from the least-significant bit of each base number. Now I don't, for one second, believe that Excel has an IF statement of the form "if (x < 5368709.13) applyHeuristic(&x)". But I must admit: I do not see any obvious difference between 5368709.12 and 5368709.13 that would explain why the heuristic should no longer be applied. Well, okay: I cannot resist the speculation. Since 5368709.13 is the 64-bit hex value 0x41547AE1,4851EB85 and 5368709.12 is 0x41547AE1,47AE147B, someone might have written: "if (x < 0x41547AE148000000) applyHeuristic(&x)". But I still cannot imagine why that bit pattern would be a reasonable "break" point. Oh well, I'm content to leave that as one of life's little mysteries. Enough's enough! 4. For traceability of results, I prefer live formulas (possibly calculated from VBA functions), so I dont have much experience to draw on; but so far, it seems to behave like #3. I presume "it" refers to a cell assignment in a VBA macro. I think that needs to be broken down into two categories. 4a. For cell assignments of the form Range(...)=numericExpression, yes, I previously demonstrated that they behave the same as paste-special-value. Actually, I explained the behavior for this paradigm and demonstrated that PSV behaves the same way. FYI, my testing for #3 above used the form Range(...)=numericExpression. So arguably, I have not proved that PSV behaves the same throughout the entire range. However, I did spot-check using PSV, including the end points and some intermediate points. So I think it is reasonable to assume that PSV does indeed behave the same. 4b. For cell assignments of the form Range(...)=stringExpression, I demonstrated that they behave the same as #1, manual data entry. I must admit that I was surprised that #4b results in a numeric constant in the cell instead of text. It's not that I cannot see the logic behind the behavior. It's just that I had always thought I have to "convert" the stringExpression to a numericExpression explicitly by including some arithmetic operation, e.g. --(stringExpression). Well, this has been fun. But I really think the horse is rolling over in its grave ;-). ----- original message ----- "Jerry W. Lewis" wrote in message ... "JoeU2004" wrote: ... Other than that, I think we are on the same page. Great! As a final summary/communication check, consider I know of only 5 ways to get a constant into a cell 1. Enter the value 2. Paste the value from the clipboard 3. Paste Special|Values that were copied from an Excel cell 4. Poke the value directly into the cell from VBA (as your code does) 5. Create the workbook in another application that will reliably allow the full IEEE DP values as constants. For instance, while http://www.byedesign.co.uk/s32/spre32en.zip does filter input values, its Paste Special|Values preserves unchanged the binary value calculated by a formula The following observations on these methods are based primarily on experience with Excel 2003 and earlier. I know from a period of testing that there are some differences in 2007; but I do not currently have access to 2007, and so cannot verify its behavior. 1. Data entry in Excel appears to always truncate to 15 digits before converting to binary. Even in instances where the display of that converted value is wrong, such as http://support.microsoft.com/kb/161234 the underlying binary value is consistent with the preceding statement. 2. I would expect pasting from the clipboard to use the same conversion routine as data entry, and have seen nothing to contradict this expectation. 3. From its name, I would expect Copy and Paste Special|Values to place the original binary value in the cell as a constant instead of a formula. My expectation is usually met when the 15 digit display contains more than 2 decimal places. My expectation is rarely met when the 15 digit display is not an integer but contains 1 or 2 decimal places. I conclude that Paste Special|Values basically does what I expect, but that an additional filter is applied. 4. For traceability of results, I prefer live formulas (possibly calculated from VBA functions), so I dont have much experience to draw on; but so far, it seems to behave like #3. 5. If you open a workbook that already has constants that would have been changed by the additional filter of #3 & #4, they are not changed simply by opening the file. It would be interesting to see whether Excel 2007 (which does not seem to natively use the filter of #3 & #4) employs the filter of #3 & #4 when saving in 2003 format. Cheers, Jerry |
How to strore exact double from macro into Excel cell?
I don't intentionally work with separate instances of Excel (as opposed to
separate workbooks in the same instance of Excel) so I have no prior experience to draw on, but you are correct, pasting a formula from one instance to another is different from pasting from another application, and seems to be equivalent to Paste Special|Value within the same instance. You are also correct that while binary variants of 5368709.12 collapse as constants into a single binary value, binary variants of 5368709.13 do not. I spent some time a few years ago trying to figure it out, and never arrived at a consistent rule. If further testing shows that you have hit upon a cutoff that defines the consistency, then well done! The Excel MOD() function seems to have a cutoff value where its behavior changes http://groups.google.com/group/micro...92e5f8c86e65c8 though that cutoff is a simple binary value of 2^27. Thanks for an interesting discussion, Jerry "JoeU2004" wrote: "Jerry W. Lewis" wrote: Other than that, I think we are on the same page. Great! As a final summary/communication check, consider Well, you seem to have wandered off-page and returned to the Table of Contents :-). But just to refine your understanding.... 2. I would expect pasting from the clipboard to use the same conversion routine as data entry, and have seen nothing to contradict this expectation. That depends on the source of the copy (i.e. the type of clipboard object). Certainly that is the case when the source is Notepad or Word, for example, because what is pasted (ctrl+V) is text. But that is not the case when pasting from an Excel 2003 object. To verify, use your example of "variants" of 0.283. In one instance of Excel 2003, enter the following into 3 cells: =0.283 - 2^-54 =0.283 =0.283 + 2^54 Copy (ctrl+C) the 3 cells. In a separate instance of Excel[*], paste (ctrl+V) into A1:A3. Now enter: B1: =A1-A2=0 B2: =A3-A2=0 B3: =A1-A3=0 B1, B2 and B3 are all FALSE, demonstrating that paste preserved the binary representations in this case. (Technically, it only verifies that something is different. But I did verify that the pasted binary representations are indeed the same as the original ones.) That seems to contradict your stated expectation. Of course, paste fails to preserve the binary representation in the cases that I previously demonstrated a cell assignment in VBA fails to, for example 0.28 +/- 2^-54. So, pasting (ctrl+V) from the clipboard appears to use the same "filter"' (as you put it) that VBA cell assignments and paste-special-value (within the same Excel instance) use. (It is anyone's guess whether it is truly the same filter or merely a filter with apparently similar behavior.) Note: By "cell assignment in VBA", I mean assignments of the form Range(...)=numericExpression. There are other ways to assign values to cells in VBA. I have not tested with them at all. I probably should, just in case one of them solves my original problem. But my ability to do so is limited by the extent of my knowledge of VBA. [*] To confirm a common understanding of "separate instance of Excel", copy a cell in the first instance, then paste-special-value into the second instance. If you truly have separate instances of Excel, the choices presented by PSV will be objects. If, instead, you mistakenly opened two workbooks in the same instance of Excel, PSV will permit you to paste Value et al. The experiment would still work as described. But the results are meaningless relative to what I am trying to demonstrate. 3. From its name, I would expect Copy and Paste Special|Values to place the original binary value in the cell as a constant instead of a formula. My expectation is usually met when the 15 digit display contains more than 2 decimal places. My expectation is rarely met when the 15 digit display is not an integer but contains 1 or 2 decimal places. Ding, ding, ding! By jove, I think you've got it! Barring a counter-example, perhaps the rule is: whenever the converted binary representation is a numeric string of 15 digits or less (not counting trailing zero fractional digits), and it has 0, 1 or 2 decimal places, PSV uses the binary representation of the converted numeric string instead of the original binary representation. Examples: 12345, 12345.6 and 12345.67 plus 2^-39, which adds 1 to the least-significant bit of each base number. Otherwise, PSV uses the original binary representation. Example: 12345.678 + 2^-39. The same rule applies to a cell assignment in VBA (as defined above) and to pasting (ctrl+V) from an Excel object on the clipboard. Well done! It is exactly the kind of simple rule that I was expecting: simple to specify, and straight-forward to implement. Damn! It's not that simple, of course. But that does seem to be the case for numbers between 0.01 and 5368709.12 (!). I did not test every number in that range, of course. My methodology was to try the numbers 1, 12, 123, 1234, ..., 123456789012345 with 0, 1, 2 and 3 decimal places, adding and subtracting an appropriate power of 2 so as to add or subtract one from the least-significant bit of each base number. Now I don't, for one second, believe that Excel has an IF statement of the form "if (x < 5368709.13) applyHeuristic(&x)". But I must admit: I do not see any obvious difference between 5368709.12 and 5368709.13 that would explain why the heuristic should no longer be applied. Well, okay: I cannot resist the speculation. Since 5368709.13 is the 64-bit hex value 0x41547AE1,4851EB85 and 5368709.12 is 0x41547AE1,47AE147B, someone might have written: "if (x < 0x41547AE148000000) applyHeuristic(&x)". But I still cannot imagine why that bit pattern would be a reasonable "break" point. Oh well, I'm content to leave that as one of life's little mysteries. Enough's enough! 4. For traceability of results, I prefer live formulas (possibly calculated from VBA functions), so I dont have much experience to draw on; but so far, it seems to behave like #3. I presume "it" refers to a cell assignment in a VBA macro. I think that needs to be broken down into two categories. 4a. For cell assignments of the form Range(...)=numericExpression, yes, I previously demonstrated that they behave the same as paste-special-value. Actually, I explained the behavior for this paradigm and demonstrated that PSV behaves the same way. FYI, my testing for #3 above used the form Range(...)=numericExpression. So arguably, I have not proved that PSV behaves the same throughout the entire range. However, I did spot-check using PSV, including the end points and some intermediate points. So I think it is reasonable to assume that PSV does indeed behave the same. 4b. For cell assignments of the form Range(...)=stringExpression, I demonstrated that they behave the same as #1, manual data entry. I must admit that I was surprised that #4b results in a numeric constant in the cell instead of text. It's not that I cannot see the logic behind the behavior. It's just that I had always thought I have to "convert" the stringExpression to a numericExpression explicitly by including some arithmetic operation, e.g. --(stringExpression). Well, this has been fun. But I really think the horse is rolling over in its grave ;-). ----- original message ----- "Jerry W. Lewis" wrote in message ... "JoeU2004" wrote: ... Other than that, I think we are on the same page. Great! As a final summary/communication check, consider I know of only 5 ways to get a constant into a cell 1. Enter the value 2. Paste the value from the clipboard 3. Paste Special|Values that were copied from an Excel cell 4. Poke the value directly into the cell from VBA (as your code does) 5. Create the workbook in another application that will reliably allow the full IEEE DP values as constants. For instance, while http://www.byedesign.co.uk/s32/spre32en.zip does filter input values, its Paste Special|Values preserves unchanged the binary value calculated by a formula The following observations on these methods are based primarily on experience with Excel 2003 and earlier. I know from a period of testing that there are some differences in 2007; but I do not currently have access to 2007, and so cannot verify its behavior. 1. Data entry in Excel appears to always truncate to 15 digits before converting to binary. Even in instances where the display of that converted value is wrong, such as http://support.microsoft.com/kb/161234 the underlying binary value is consistent with the preceding statement. 2. I would expect pasting from the clipboard to use the same conversion routine as data entry, and have seen nothing to contradict this expectation. 3. From its name, I would expect Copy and Paste Special|Values to place the original binary value in the cell as a constant instead of a formula. My expectation is usually met when the 15 digit display contains more than 2 decimal places. My expectation is rarely met when the 15 digit display is not an integer but contains 1 or 2 decimal places. I conclude that Paste Special|Values basically does what I expect, but that an additional filter is applied. 4. For traceability of results, I prefer live formulas (possibly calculated from VBA functions), so I dont have much experience to draw on; but so far, it seems to behave like #3. 5. If you open a workbook that already has constants that would have been changed by the additional filter of #3 & #4, they are not changed simply by opening the file. It would be interesting to see whether Excel 2007 (which does not seem to natively use the filter of #3 & #4) employs the filter of #3 & #4 when saving in 2003 format. Cheers, Jerry |
| All times are GMT +1. The time now is 12:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com