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