VBA Tip: Testing an Integer

The following function will allow you to check if a value is an integer (in numeric or text format).

isInt Function

Start by copying this function into a module:

Function isInt(number)
    'Returns True if the value is an integer or False if it is not
    'Source: https://www.excel-pratique.com/en/vba_tricks/integer
    isInt = False
    If Not IsNumeric(number) Then Exit Function
    If Int(number) = number Or Int(number) & "" = number Then isInt = True
End Function

You can then use it very simply in your VBA codes:

Sub example()

    testValue = 123
    
    If isInt(testValue) Then
        MsgBox "Integer number!"
    End If

End Sub

Examples with Other Values

Here are some examples with different values:

Sub example()

    MsgBox isInt(2) 'Returns: True
    MsgBox isInt("3") 'Returns: True
    MsgBox isInt(-61) 'Returns: True
    MsgBox isInt("ABCD") 'Returns: False
    MsgBox isInt(14.76128) 'Returns: False
    MsgBox isInt(12 / 4) 'Returns: True
    MsgBox isInt(12 / 5) 'Returns: False
    MsgBox isInt(4.65) 'Returns: False
    MsgBox isInt(Int(4.65)) 'Returns: True

End Sub