How to confirm a cell entry is a whole number

[Robert Flanagan]

Any suggestions on how to confirm that the value in a cell is a whole
number?

Bob

 

[Rick Rothstein]

I'm pretty sure you don't want the Abs function in there (at least not how
you used it). What is wrong with just this....

IsWholeNumber = MyValue = Int(MyValue)

 

[Rick Rothstein]

Okay, I see what you are doing... you are using the Abs value function to
filter out the negative values (forcing a False for them). Ok, as long as
the OP's understanding of "whole numbers" is the same as yours, your
statement is fine.

 

[Phil Hibbs]

My understanding of the definition of "whole numbers" is "the set of
positive integers, including zero". If the OP meant "integer number",
then I agree to remove the Abs wrapper in the test, as you suggest.

The definition is ambiguous:
http://en.wikipedia.org/wiki/Whole_number

Phil Hibbs.

 

[Bernd P]

Hello Bob,

Function IsWhole(r As Range) As Boolean
'TRUE if all values in range are whole numbers, FALSE if not.
Dim v, b As Boolean
For Each v In r
If v.Value <> CLng(v.Value) Then
IsWhole = False
Exit Function
End If
Next v
IsWhole = True
End Function

Here a whole number is a valid LONG number.
Change Clng to Int if you want to accept numbers > 1E15 as well.

Regards,
Bernd