Validating a cell contains an integer

I have written some code to validate that a user input field contains a whole number;

If VarType(Range("Quantity1")) = vbInteger Then

Else: MsgBox "Quantity must be a whole number, to rectify this please enter a whole number in the quantity field.", vbCritical, "Operator Response Required"
Range("Quantity1").ClearContents
Range("Quantity1").Select
Exit Sub
End If


This code returns an error message regardless of Quantity1 having an integer or not.
Can anyone help with this?

Quantity1 is the name of the cell to be validated.

Try vbdouble(not tested)

If VarType(Range("Quantity1")) = vbDouble Then

That's different issue.

Term Integer and vbInteger of VarType are different.

see

Sub test()
    Dim x As Integer, y As Variant
    [a1] = 15
    x = [a1]
    y = [a1]
    MsgBox "x =" & VarType(x), , "2 for vbInteger, 5 for vbDouble"
    MsgBox "y = " & VarType(y), , "2 for vbInteger, 5 for vbDouble"
End Sub

Originally Posted by cultofcargo

I have written some code to validate that a user input field contains a whole number;

Perhaps, OP wants to do like

Dim msg As String
If IsNumeric(Range("Quantity1")) Then
    If Range("Quantity1") Mod 1 <> 0 Then
         msg ="Quantity must be a whole number, to rectify this please enter a whole number in the quantity field.", vbCritical, "Operator Response Required"
    End if
Else
    msg = "Only Numbers allowed"
End if
If Len(msg) then
    msgbox msg
    Range("Quantity1").ClearContents
    Range("Quantity1").Select
End if

Originally Posted by skywriter

Is "Quantity1" a cell on a spreadsheet?
VarType is supposed to be used with a variable.
You would have to create a variable, let's say x.
x = Range("Quantity1").Value
If VarType(x)......

Yes Quantity1 is a cell

x = Range("Quantity1").Value
If VarType(x) = vbInteger Then
Else
MsgBox "Quantity must be a whole number, to rectify this please enter a whole number in the quantity field.", vbCritical, "Operator Response Required"
Range("Quantity1").ClearContents
Range("Quantity1").Select
Exit Sub
End If


With this written i am still getting the error message no matter what is in the cell.

And storing x (quantity1) using Dim will return messages based on what i store it as in the Dim statement (string, boolean, integer)

Originally Posted by shg

All numbers in Excel are stored as Doubles. If you want to test that one is a whole number, then

If Range("A1").Value2 = Int(Range("A1").Value2) Then 
   ' it's a whole number

Right: this isn't rocket science. Minor typo, corrected in red above (missing parenthesis). [1]

We might also point out that VBA Mod (jindon's Mod 1 expression) does not work for this purpose. As the help page tells us: VBA Mod works only with integer values. "If either number is a floating-point number, it is first rounded to an integer".


-----
[1] Personally, I would use the following; presumably more efficient.

Dim x As Double
x = Range("A1").Value2
If x = Int(x) Then
    ' it's an integer
End If

Duplicate.