"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