How do I determine if a variant is a whole number in VBA?

Question

Hellow

I am looking for an elegant solution to determine if a variant is a whole number in VBA. Unfortunately, if the variant was created from a string, I am stuck. Here's a little test script:

dim v as variant
v = "42"
if v <> round(v) then
   msgBox("<>")
end if

Here, the msgBox pops up, probably because the variant was created from a string, although I would have expected v to be = round(v).

Answer 1

You should write something like:

if cDbl(v) <> round(cDbl(v)) Then

Where cDbl is a function converting any data to a double-type number. You might have to treat cases where v cannot be converted to a number with the isNumeric() function before calling the cDbl function. You can even use the cInt function for your comparisons:

if isnumeric(v) then
    if cDbl(v) - cInt(v) <> 0 Then
    ....
    endif
else
   debug.print "data cannot be converted to a number"
endif

Answer 2

Sub test()

    Dim v As Variant
    v = "42"
    If Val(v) <> Int(Val(v)) Then
       MsgBox ("<>")
    End If

End Sub

If you use Val(), it will try its best to convert to a number. If it can't, it will return zero and Val(v) will always equal Int(Val(v)) in that case.