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

Question

I am looking to determine if a variant created from a string is a whole 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).

Kibler

It might be worth asking yourself if you really need to handle any variant and any whole number. What about a 1000-digit string? What about integral Doubles outside the range of a Long? It will be a lot easier if you spec out exactly what you need and then code just that. It's not the same problem, but see this answer by Eric Lippert to another question here on Stackoverflow: https://mcmap.net/q/93858/-how-can-i-ensure-that-a-division-of-integers-is-always-rounded-up/926806#926806 – Freezer

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

Hugibert

That will not quite work in some cases, for example: Dim v As Currency v = -123456.0000006 – Gilberto

Well, my proposition was more 'write something like that' than 'this is guaranteed bullet proof code', but the main idea is here, even if it needs some extra testing. – Hugibert

I'm sorry, but I have to say -1, even given your caveat about "something like that". Either of your answers as they are coded have bugs. If you present code, it should be correct and not leave finding the bugs as an excercise. – Freezer

Well I don't really agree with your last point "not leave finding bugs as an exercise". Basically finding bugs is a daily exercise for each one of us, as writing code with bugs is our daily reality! Anyway next time I will add all legal mentions to indicate that my answer is presented 'as is', and do not include any garantee of any kind for the user, who seemed to find my answer helpful enough to accept it. – Hugibert

-1 for defending instead of improving. The example is fine if you just use Clng instead of CInt – Salientian

... and I should also add that I do not abandon any of my rights, among them the right to defend my position, when publishing answers and comments on stackoverflow! Sorry guys but I really find your 'offended' position quite excessive. And if you have a better answer or proposal than mine, why don't you post it? – Hugibert

@PhilippeGrondier after reading the comments I am disappointed that people dared to downvote your excellent answer. You answered my question in the spirit I needed and I am completly happy with it. – Herzberg

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.

Theorize

Answer 3

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.

Birkner

Answer 4

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.

Puleo