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?

This worked pretty good. You can adapt it to your needs.

Sub dk()

For Each c In Range("A2:A6")
If Len(c) - InStr(c, ".") > 2 Then
MsgBox c.Address & " More than 2 decimal places"
End If
Next
End Sub



"zxcv" <(E-Mail Removed)> wrote in message
news:3466ddcc-9050-4873-9377-(E-Mail Removed)...
>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?

Your code will fail if the number is a whole number without a decimal point.
If you change your If..Then statement to the following, then your code will
work correctly...

If Len(c) - InStr(c & ".", ".") > 2 Then

--
Rick (MVP - Excel)



"JLGWhiz" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> This worked pretty good. You can adapt it to your needs.
>
> Sub dk()
>
> For Each c In Range("A2:A6")
> If Len(c) - InStr(c, ".") > 2 Then
> MsgBox c.Address & " More than 2 decimal places"
> End If
> Next
> End Sub
>
>
>
> "zxcv" <(E-Mail Removed)> wrote in message
> news:3466ddcc-9050-4873-9377-(E-Mail Removed)...
>>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?
>
>

"zxcv" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:3466ddcc-9050-4873-9377-(E-Mail Removed)...
>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?

Hi Rick, I ran a test and it did not error out. But it does not hurt to be
safe.


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Your code will fail if the number is a whole number without a decimal
> point. If you change your If..Then statement to the following, then your
> code will work correctly...
>
> If Len(c) - InStr(c & ".", ".") > 2 Then
>
> --
> Rick (MVP - Excel)
>
>
>
> "JLGWhiz" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
>> This worked pretty good. You can adapt it to your needs.
>>
>> Sub dk()
>>
>> For Each c In Range("A2:A6")
>> If Len(c) - InStr(c, ".") > 2 Then
>> MsgBox c.Address & " More than 2 decimal places"
>> End If
>> Next
>> End Sub
>>
>>
>>
>> "zxcv" <(E-Mail Removed)> wrote in message
>> news:3466ddcc-9050-4873-9377-(E-Mail Removed)...
>>>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?
>>
>>

By fail I meant it will return the wrong result, not error out. I assumed
from the OP's posting that whole numbers as well as floating point numbers
with one or two decimal places were okay... your original If...Then
statement reported one and two digits after the decimal point as being okay,
but listed whole numbers as having more than two decimal places (if the
whole number had more than two digits in it).

--
Rick (MVP - Excel)



"JLGWhiz" <(E-Mail Removed)> wrote in message
news:u5$(E-Mail Removed)...
> Hi Rick, I ran a test and it did not error out. But it does not hurt to
> be safe.
>
>
> "Rick Rothstein" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Your code will fail if the number is a whole number without a decimal
>> point. If you change your If..Then statement to the following, then your
>> code will work correctly...
>>
>> If Len(c) - InStr(c & ".", ".") > 2 Then
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>>
>> "JLGWhiz" <(E-Mail Removed)> wrote in message
>> news:#(E-Mail Removed)...
>>> This worked pretty good. You can adapt it to your needs.
>>>
>>> Sub dk()
>>>
>>> For Each c In Range("A2:A6")
>>> If Len(c) - InStr(c, ".") > 2 Then
>>> MsgBox c.Address & " More than 2 decimal places"
>>> End If
>>> Next
>>> End Sub
>>>
>>>
>>>
>>> "zxcv" <(E-Mail Removed)> wrote in message
>>> news:3466ddcc-9050-4873-9377-(E-Mail Removed)...
>>>>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?
>>>
>>>
>
>

On Mar 24, 12:04*pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Your code will fail if the number is a whole number without a decimal point.
> If you change your If..Then statement to the following, then your code will
> work correctly...
>
> If Len(c) - InStr(c & ".", ".") > 2 Then
>
> --
> Rick (MVP - Excel)
>
> "JLGWhiz" <JLGW...@cfl.rr.com> wrote in message
>
> news:#(E-Mail Removed)...
>
> > This worked pretty good. *You can adapt it to your needs.
>
> > Sub dk()
>
> > *For Each c In Range("A2:A6")
> > * *If Len(c) - InStr(c, ".") > 2 Then
> > * * * MsgBox c.Address & " *More than 2 decimal places"
> > * *End If
> > *Next
> > End Sub
>
> > "zxcv" <zxcvnos...@yahoo.com> wrote in message
> >news:3466ddcc-9050-4873-9377-(E-Mail Removed)...
> >>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. A combination of the 2 above approaches is working.

On Mar 24, 12:31*pm, "Joe User" <joeu2004> wrote:
> "zxcv" <zxcvnos...@yahoo.com> 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" <zxcvnos...@yahoo.com> wrote in message
>
> news:3466ddcc-9050-4873-9377-(E-Mail Removed)...
>
> >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 for the input but I need to do this in VBA as I have no control
over the input and cannot put any formulas in the sheet. Someone else
enters the data and then another person hits a button that I created.

Embellishment....

"zxcv" <(E-Mail Removed)> 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,0195399252334027551114559173583984375. 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,0195399252334027551114559173583984375. But if inval were
2.22+2^-51 (the smallest value larger than 2.22), it would be represented
internally as 2.22000000000000,06394884621840901672840118408203125, and
Round(inval,2) does not equal inval.

Note: You cannot enter the
2.2200000000000006394884621840901672840118408203125 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.2200000000000006394884621840901672840118408203125").


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

"Joe User" <joeu2004> wrote in message
news:%(E-Mail Removed)...
> "zxcv" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
> news:3466ddcc-9050-4873-9377-(E-Mail Removed)...
>>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?
>

"zxcv" <(E-Mail Removed)> wrote:
On Mar 24, 12:31 pm, "Joe User" <joeu2004> wrote:
> > Try:
> > If Round(inval,2) = inval Then
[....]
> Thanks for the input but I need to do this in VBA
> as I have no control over the input and cannot put
> any formulas in the sheet. Someone else enters the
> data and then another person hits a button that I
> created.

I don't understand your comment. What I wrote is for VBA, and it is
intended to deal with exactly the situation that you describe. I think you
misunderstand my comments. Perhaps you should just give it a try.

PS: Sorry, I wrote "=" where you wanted "<>". That's a simple change, heh?


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

"zxcv" <(E-Mail Removed)> wrote in message
news:6d74dc36-1fae-4269-8bba-(E-Mail Removed)...
On Mar 24, 12:31 pm, "Joe User" <joeu2004> wrote:
> "zxcv" <zxcvnos...@yahoo.com> 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" <zxcvnos...@yahoo.com> wrote in message
>
> news:3466ddcc-9050-4873-9377-(E-Mail Removed)...
>
> >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 for the input but I need to do this in VBA as I have no control
over the input and cannot put any formulas in the sheet. Someone else
enters the data and then another person hits a button that I created.