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