test if value is integer

[HilaryF]

Hi,
I am using the IsDate() and IsNumeric functions in my VBA code. Is there an equivalent function for testing if a value is an integer?

Thanks.

 

[TommyGun]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    i = 250
    j = 2500000
    
    <SPAN style="color:#00007F">If</SPAN> TypeName(i) = "Integer" <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Variable ""i"" is an integer."
    <SPAN style="color:#00007F">Else</SPAN>
        MsgBox "Variable ""i"" is a: " & TypeName(i)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> TypeName(j) = "Integer" <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Variable ""j"" is an integer."
    <SPAN style="color:#00007F">Else</SPAN>
        MsgBox "Variable ""j"" is a: " & TypeName(j)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[tzenekkik]

or try this: =TRUNC(A1;0)=A1
(provided A1 holds the value to be tested)

 

[Scott Huish]

Sub test()
x = Range("A1").Value
If Int(x) / x = 1 Then
MsgBox "Value is an Integer"
Else
MsgBox "Value is not an Integer"
End If
End Sub

 

[RalphA]

One more:

Assuming A1 has the number, put, in any other cell, the formula:
=IF(INT(A1)=A1,"True","False")

 

[tzenekkik]

so: INT(A1)=A1 is the shortest version to test for an integer.

 

[verblender]

since you are using VBA, not checking within the formulas of a given worksheet, you can check if a cell is of integer variable type using this syntax:

If VarType(my_variable) = vbInteger Then 'IF my_variable if is of type Integer ...


other types of comparisons for different kinds of data (date, text, etc) can be made using the following code:

If VarType(my_variable) = vbInteger Then 'IF my_variable is of type Integer ...
'Identical to :
If VarType(my_variable) = 2 Then 'IF my_variable is of type Integer ...

based on the following table:

vbEmpty 0
vbNull 1
vbInteger 2
vbLong 3
vbSingle 4
vbDouble 5
vbCurrency 6
vbDate 7
vbString 8
vbObject 9
vbError 10

i read this on from https://www.excel-pratique.com/en/vba/conditions_continued.php which basically covers this for VBA.

 

[SpillerBD]

=If (Trunc(A1)=A1,Integer,Non-Integer)
or
=If (Int(A1)=A1,Integer,Non-Integer)

I wouldn't be surprised if the "math" and the floating point issues have some of these fail.

In VBA, you have INT function.
The other functions are available through WorksheetFunction

 

[cubitmg]

If you can use a macro then I find this function works:
Public Function isInteger(varValue)
On Error GoTo Exit_isInteger 'if not numeric then int() will throw an error and take the default False return value
isInteger = False 'default False return value
If Int(varValue) = Val(varValue) Then isInteger = True
Exit_isInteger:
End Function