How to strore exact double from macro into Excel cell?

[JoeU2004]

PS....

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

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

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]

:
....

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

 

[Bernard Liengme]

Jerry, sorry to be off topic but further to Office 2010 PLEASE contact me
directly - email addy from my website.

 

[JoeU2004]

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 don’t 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]

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/microsoft.public.excel.misc/browse_thread/thread/ef92e5f8c86e65c8
though that cutoff is a simple binary value of 2^27.

Thanks for an interesting discussion,
Jerry

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 don’t 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 -----

:
...

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