On Mar 24, 1:30*pm, "Joe User" <joeu2004 wrote:
Embellishment....

"zxcv" wrote:
I tried rounding the numbers to do a test like this:
If Int(inval * 100) < inval * 100 Then

but this gets a rounding error with certain values like 2.22.

The reason that does not work is because most numbers with decimal fractions
cannot be represented exactly. *Instead, they are represented by a sum of 53
consecutive powers of two (bits), for example 2*2^1 + 0*2^0 + 0*2^-1 +
0*2^-2 + 1*2^-3 + etc.

Consequently, 2.22 is represented by exactly
2.22000000000000,019539925233402755111455917358398 4375. *Int(2.22*100) is
exactly 222. *But 2.22*100 is
222.000000000000,028421709430404007434844970703125 , preserving the
additional bits used to approximate 0.22 in this context.

In contrast, Round(inval,2) results in inval exactly as it would be
represented internally if it were entered with 2 decimal places. *So if
inval is 2.22, Round(inval,2) results in
2.22000000000000,019539925233402755111455917358398 4375. *But if inval were
2.22+2^-51 (the smallest value larger than 2.22), it would be represented
internally as 2.22000000000000,063948846218409016728401184082031 25, and
Round(inval,2) does not equal inval.

Note: *You cannot enter the
2.220000000000000639488462184090167284011840820312 5 as a constant in Excel;
however, it can be the result of a calculation. *Also, you can enter that
constant in VBA, including as input to an InputBox. *Caveat: *If you write
that constant in a VBA statement, the VBA editor might change it later when
you edit the line. *It would be more reliable to write
Cdbl("2.220000000000000639488462184090167284011840 8203125").

----- original message -----

"Joe User" <joeu2004 wrote in message

...

"zxcv" wrote:
I need to do some input validation so to check if a
value has no more than 2 decimal digits. *So 14.12
is valid but 14.123 is not.

Try:

If Round(inval,2) = inval Then

Normally, I would opt for WorksheetFunction.Round or even
Evaluate("round(...)") instead of the VB Round function. *There are
functional differences. *In this case, I do not think it makes a
difference. Nevertheless, you might want to use one of those alternatives
instead, just to be sure.

----- original message -----

"zxcv" wrote in message
...
I need to do some input validation so to check if a value has no more
than 2 decimal digits. *So 14.12 is valid but 14.123 is not.

I have tried doing a check like this:

* * If Int(inval * 100) < inval * 100 Then

but this gets a rounding error with certain values like 2.22. *If I
subtract one side above from the other I get a difference on the order
of 10^-21.

So I tried rounding the numbers to do a test like this:

* * If Round(Int(inval * 100), 10) < Round(inval * 100, 10) Then

and this does something weird like rounding Round(Int(0.29 * 100), 10)
to 28.

Is there some simpler way to check that a number does not have too
many decimal digits?

Thanks. That is a great bit of information. I never knew why the
rounding errors happened.