0

I would like to check if a value of a cell is an integer (number without comma) or decimal (number with comma). I tried:

1.)

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

-> true also for decimal

2.)

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

-> Types incompatiple

3.)

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

-> Types incompatiple

erik-stengel
  • 344
  • 2
  • 12

3 Answers3

2

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.

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
2

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

Update After freeflow's comment, I did some tests, see image. It seems that the formatting of a cell doesn't impact the type that is passed to VBA - except if it is a Date. The number 44614 shown as value is in fact the formula =Today() (so today's date, without time). Only when the cell is formatted as Date, the value is passed as a Date rather than a number. In all cases, when a number is passed, it is passed as Double.
The column IsInteger shows the result of the Function above.

(With passed I mean that a variant is passed containing a vartype of...)

enter image description here

FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • This is working, but returning also true for dates e.g. 31.12.2022 – erik-stengel Feb 22 '22 at 14:20
  • True: A date in Excel is nothing more than a number - just displayed differently. – FunThomas Feb 22 '22 at 15:15
  • Excel always passes back a Variant. Don't be fooled by implicit conversions. .Value returns the formatted output of the cell so in the case of a date vartype will correctly return a type of vbDate if the cell if formatted as a date. – freeflow Feb 22 '22 at 15:40
  • @freeflow It's clear that Excel passes Variants. `.Value` passes the real value of a cell, the formatted value is in `.Text` and is always a String. I don't think there is an implicit conversion going on, Excel really stores numbers as Double (including a Date). – FunThomas Feb 22 '22 at 16:57
  • No. Value passes the formatted output as a number, string, or date. Value 2 passes the actual value. I checked the documentation on this and verified it by getting different values for vartype for string, date and numbers. – freeflow Feb 22 '22 at 17:14
  • Disagree. Value and Value2 result in the same *except* for currency and date, see https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2/17363466#17363466. And a Date in VBA and Excel is a Double, just the VarType is set differently (but the binary representation is the same). – FunThomas Feb 22 '22 at 17:40
1

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
freeflow
  • 4,129
  • 3
  • 10
  • 18
  • Correct me if I'm wrong, but the VarType of a numeric cell is always 5 (double) – FunThomas Feb 22 '22 at 13:39
  • Very likely correct. I use Excel for maybe 30 minutes a year so don't rely on my output. Just change the case statement to use whatever numeric vartypes are used internally by excel. – freeflow Feb 22 '22 at 15:06
  • 1
    The point is that no matter if you have a value 1 or 1.5 in a cell, it will always give you vbDouble – FunThomas Feb 22 '22 at 15:16