check if cell value is an integer-CodePudding

I would like to check if a value of a cell is an integer or decimal. I tried:

1.)

IsNumeric(.Cells(row, column).Value)

-> True also for decimal

2.)

If CInt(.Cells(row, column).Value) / .Cells(row, column).Value = 1

-> Types incompatiple

CodePudding user response：

The simplest method would be to use the vartype method with a select case structure

Select case VarType(.cells(row,column).value)

    Case vbInteger, vbDecimal

        <do stuff>

    case Else

        <Do other stuff>

end select

CodePudding user response：

The MOD() function like this:

=MOD(A1,1)

will return 0 if integer and a value greater than 0 if the number has a decimal.

CodePudding user response：

When you pass a numeric value from Excel to VBA, the value is always passed as Double, therefore the check for VarType will not work.

Try the following function: It first checks if the value is numeric at all. If yes, it checks if the integer-part of the number is equal to the number itself. It uses CLng to avoid numeric overflows.

Function isInteger(c As Variant) As Boolean
    If Not IsNumeric(c) Then Exit Function
    isInteger = (c = CLng(c))
End Function