Convert string to int if string is a number

Use IsNumeric. It returns true if it's a number or false otherwise.

Public Sub NumTest()
    On Error GoTo MyErrorHandler

    Dim myVar As Variant
    myVar = 11.2 'Or whatever

    Dim finalNumber As Integer
    If IsNumeric(myVar) Then
        finalNumber = CInt(myVar)
    Else
        finalNumber = 0
    End If

    Exit Sub

MyErrorHandler:
    MsgBox "NumTest" & vbCrLf & vbCrLf & "Err = " & Err.Number & _
        vbCrLf & "Description: " & Err.Description
End Sub

Cast to long or cast to int, be aware of the following.

These functions are one of the view functions in Excel VBA that are depending on the system regional settings. So if you use a comma in your double like in some countries in Europe, you will experience an error in the US.

E.g., in european excel-version 0,5 will perform well with CDbl(), but in US-version it will result in 5. So I recommend to use the following alternative:

Public Function CastLong(var As Variant)

    ' replace , by .
    var = Replace(var, ",", ".")        

    Dim l As Long
    On Error Resume Next
    l = Round(Val(var))

    ' if error occurs, l will be 0
    CastLong = l

End Function

' similar function for cast-int, you can add minimum and maximum value if you like
' to prevent that value is too high or too low.
Public Function CastInt(var As Variant)

    ' replace , by .
    var = Replace(var, ",", ".")

    Dim i As Integer
    On Error Resume Next
    i = Round(Val(var))

    ' if error occurs, i will be 0
    CastInt = i

End Function

Of course you can also think of cases where people use commas and dots, e.g., three-thousand as 3,000.00. If you require functionality for these kind of cases, then you have to check for another solution.

Tags:

Excel

Vba