Hi all,

I have a fairly mysterious problem.

It is hard to explain here, but basically what I did is declare a variable X
as single, then assign a cell content containing decimal digits to X, then
write X back to another cell.

Problem is, let say, suppose the original cell contains 0.0111773. After
transferring this number to X and then copy back to another cell, that cell
now contains 0.0111772995442152. Though the cell content is very near to the
original one, but it does cause problem to me. Anybody can guess what
actually happened and what is the solution? Thanks a lot.

Frederick Chow

Use Double instead of Single. Single just doesn't have enough precision. See
VBA help for the limits of Data types

--
Kind regards,

Niek Otten

"Frederick Chow" wrote in message
...
Hi all,

I have a fairly mysterious problem.

It is hard to explain here, but basically what I did is declare a variable
X as single, then assign a cell content containing decimal digits to X,
then write X back to another cell.

Problem is, let say, suppose the original cell contains 0.0111773. After
transferring this number to X and then copy back to another cell, that
cell now contains 0.0111772995442152. Though the cell content is very near
to the original one, but it does cause problem to me. Anybody can guess
what actually happened and what is the solution? Thanks a lot.

Frederick Chow

If you require greater accuracy use a double. The problem is in how floating
point numbers are stored. Given the amount of memory that a single occupies
that is the best it could do (oversimplified). Even a double will crap out
eventually. That is why banking systems don't use floating point numbers.
They use integers with decimals thrown into the user interface.
--
HTH...

Jim Thomlinson


"Frederick Chow" wrote:

Hi all,

I have a fairly mysterious problem.

It is hard to explain here, but basically what I did is declare a variable X
as single, then assign a cell content containing decimal digits to X, then
write X back to another cell.

Problem is, let say, suppose the original cell contains 0.0111773. After
transferring this number to X and then copy back to another cell, that cell
now contains 0.0111772995442152. Though the cell content is very near to the
original one, but it does cause problem to me. Anybody can guess what
actually happened and what is the solution? Thanks a lot.

Frederick Chow

0.0111773 cannot be exactly represented in a fixed number of floating
point binary digits. By declaring your variable as Single, VBA will
truncate the value passed to it by XL (which is always a Double for
numeric values). Declaring X as a Double works for me.

Unless you have a specific reason for using a Single, I'd recommend
declaring all your floating point variables as doubles. Especially since
the value will be coerced to a Double by XL when it's copied.



In article ,
"Frederick Chow" wrote:

Hi all,

I have a fairly mysterious problem.

It is hard to explain here, but basically what I did is declare a variable X
as single, then assign a cell content containing decimal digits to X, then
write X back to another cell.

Problem is, let say, suppose the original cell contains 0.0111773. After
transferring this number to X and then copy back to another cell, that cell
now contains 0.0111772995442152. Though the cell content is very near to the
original one, but it does cause problem to me. Anybody can guess what
actually happened and what is the solution? Thanks a lot.

Frederick Chow

Frederick Chow wrote:
Hi all,

I have a fairly mysterious problem.

It is hard to explain here, but basically what I did is declare a variable X
as single, then assign a cell content containing decimal digits to X, then
write X back to another cell.

Problem is, let say, suppose the original cell contains 0.0111773. After
transferring this number to X and then copy back to another cell, that cell
now contains 0.0111772995442152. Though the cell content is very near to the
original one, but it does cause problem to me. Anybody can guess what
actually happened and what is the solution? Thanks a lot.

Frederick Chow




Declare X as double

Hi,

Really thanks to all of your advices. The problem is really solved by
declaring X to Double!

I originally thought that Single data type should hav been enough as it
holds astronomical numbers already, without thinking about the decimal
points issue.

Frederick Chow
"Frederick Chow" wrote in message
...
Hi all,

I have a fairly mysterious problem.

It is hard to explain here, but basically what I did is declare a variable
X as single, then assign a cell content containing decimal digits to X,
then write X back to another cell.

Problem is, let say, suppose the original cell contains 0.0111773. After
transferring this number to X and then copy back to another cell, that
cell now contains 0.0111772995442152. Though the cell content is very near
to the original one, but it does cause problem to me. Anybody can guess
what actually happened and what is the solution? Thanks a lot.

Frederick Chow

Glad the immediate problem is solved. Most terminating decimal fractions are
nonterminating binary fractions that can only be approximated with finite
precision. If you want to learn more about the internal representation of
numbers, you might find my functions at
http://groups.google.com/group/micro...06871cf92f8465
to be useful.

D2B(CSng(0.0111773)) shows that the single precision binary representation
(24 bits) is
1.01101110010000011111110B-7
D2B(0.0111773) shows that the double precision binary representation (24
bits) is
1.011011100100000111111100111110101001001000000001 1111B-7
You can use B2D to show that while this equals 0.0111773 to 15 digits, it is
not exactly 0.0111773. You can use B2D to show that 0.111773 is bracketed by
1.011011100100000111111100111110101001001000000001 11110101101000B-7 and
1.011011100100000111111100111110101001001000000001 11110101101001B-7

Jerry

"Frederick Chow" wrote:

Hi,

Really thanks to all of your advices. The problem is really solved by
declaring X to Double!

I originally thought that Single data type should hav been enough as it
holds astronomical numbers already, without thinking about the decimal
points issue.

Frederick Chow
"Frederick Chow" wrote in message
...
Hi all,

I have a fairly mysterious problem.

It is hard to explain here, but basically what I did is declare a variable
X as single, then assign a cell content containing decimal digits to X,
then write X back to another cell.

Problem is, let say, suppose the original cell contains 0.0111773. After
transferring this number to X and then copy back to another cell, that
cell now contains 0.0111772995442152. Though the cell content is very near
to the original one, but it does cause problem to me. Anybody can guess
what actually happened and what is the solution? Thanks a lot.

Frederick Chow