If x <> Int(x) always true, even if x=Int(x). Can't find out why

user_14140630 0
Viewed 99

I'm pretty amateur at coding, but I'm trying to develop some automated business sheets in excel to make my old man's life a little easier on the business. The intent is to create a table with range based on a number in a userform. Code looks like this: (textbox1 was renamed to nParc)

Private Sub Cmb1_Click()

Dim nP As Variant

    nP = UserForm1.nParc.Value

If nP = "" Then
    MsgBox "Error 0"
ElseIf Not IsNumeric(nP) Then
    MsgBox "Error 1"
ElseIf Not nP > 0 Then
    MsgBox "Error 2"
ElseIf nP <> Int(nP) Then
    MsgBox "Error 3"
Else
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1).Address(), Cells(nP, 14).Address()), xlYes).Name = "Business"
End If

End Sub

I always get

Error 3

when I click the "Proceed" button, no matter what. If I remove the msgbox "error 3" and the else after, it works.

I have already tried using ElseIf Not nP = Int(nP), but the issue remains.

Any help would be deeply appreciated.

vba excel
2 Answers

Your textbox is returning text, not a number. It is best to cast the value to a numeric type before attempting to do any sort of numeric comparison:

If Len(nP) = 0 Then
   MsgBox "Error 0" ' blank string
   Exit Sub
End If

If Not IsNumeric(nP) Then
   MsgBox "Error 1" ' not numeric
   Exit Sub
End If

' We've established it's numeric
Dim testValue As Double
testValue = CDbl(nP)

If testValue <> Int(testValue) Then
   MsgBox "Error 3"  ' has decimals
   Exit Sub
End If

' You could also possibly check if it's a valid row number
' i.e. that it's not too big
user_92458530

Let me elaborate on the answer @bigben has provided and explain what exactly is going on.

The Comparison

If you look at the value types of np and Int(np) using the VarType function, you will see that VarType(np) = 8, i.e. np is a String, and VarType(Int(np)) = 5, i.e. Int(np) is a Double. Since both are declared as a Variant---Int returns a Variant according to the paragraph 6.1.2.3.1.18 of the language specs---, the special case at the end of paragraph 5.6.9.5 of the language specs is triggered.

There is an exception to the rules in the preceding table when both operands have a declared type of Variant, with one operand originally having a value type of String, and the other operand originally having a numeric value type. In this case, the numeric operand is considered to be less than (and not equal to) the String operand, regardless of their values.

This is why the comparison returns False

The Numeric Check

You might wonder why IsNumeric(np) = True although np is a String. This is actually a bug in the VBA standard library. According to paragraph 6.1.2.7.1.8 of the language specs, IsNumeric should return False whenever the value type of the variable is String. However, in reality it seems to try to convert to Double and returns whether that was successful.

The Remediation

As @bigben already mentioned, you should cast the input from the TextBox to another type. It works, if you just declare np as String, although assigning to a Variable of type Double after the IsNumeric check would be more appropriate.

user_55368020
Related