How do I determine if a variant created from a string is a whole number?

I am looking to determine if a variant created from a string is a 开发者_如何学JAVAwhole number.

Here's a test script:

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

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

---

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

---

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.

---

what about checking that the floor function matches the ceiling function?

Private Function isWhole(value As Variant) As Boolean

    If WorksheetFunction.Ceiling_Math(value) = WorksheetFunction.Floor_Math(value) Then
        isWhole = True
    Else: isWhole = False
    End If

End Function

I had a similar issue and this code is working for me.

---

I like to use the simple +0 trick if I suspect numbers might come in as a string. For example for index/match things. Like so:

Dim v As Variant
v = "42"

If IsNumeric(v) Then
    If v + 0 <> Round(v + 0) Then
       MsgBox ("<>")
    End If
End If

This should work for dates, text, numbers, numbers as text. Not sure if or when this breaks, should be fine.