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