About Us

joeu2004

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

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

Gary''s Student

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

joeu2004

"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

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

joeu2004

"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

joeu2004

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

Dave Peterson

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

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

joeu2004

"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

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

Jerry W. Lewis

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

Bernard Liengme[_3_]

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

joeu2004

"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?!

Jerry W. Lewis

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

Bernard Liengme[_3_]

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

Jerry W. Lewis

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

joeu2004

"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?!

Jerry W. Lewis

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

joeu2004

"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

joeu2004

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

Jerry W. Lewis

"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

Bernard Liengme[_3_]

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

joeu2004

"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

Jerry W. Lewis

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to run macro in double click on a particular cell	Khizhavan	Excel Programming	4	October 6th 08 02:32 PM
Double click cell to execute macro	JDaywalt	Excel Programming	12	August 15th 07 02:47 PM
Double Lookup Without Exact Match-Price Breaks	[email protected]	Excel Programming	1	July 17th 07 01:03 AM
Macro to double values in a cell	MS	Excel Worksheet Functions	9	May 13th 07 03:20 AM
Launching a macro by double-clicking on active cell	aca	Excel Programming	5	August 4th 05 10:08 PM

Menu

About Us