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.