Convert string to int if string is a number

Question

I need to convert a string, obtained from excel, in VBA to an interger. To do so I'm using CInt() which works well. However there is a chance that the string could be something other than a number, in this case I need to set the integer to 0. Currently I have:

If oXLSheet2.Cells(4, 6).Value <> "example string" Then
  currentLoad = CInt(oXLSheet2.Cells(4, 6).Value)
Else
  currentLoad = 0
End If

The problem is that I cannot predict all possible non numeric strings which could be in this cell. Is there a way I can tell it to convert if it's an integer and set to 0 if not?

Answer 1

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

Answer 2

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.

Answer 3

Try this: currentLoad = ConvertToLongInteger(oXLSheet2.Cells(4, 6).Value) with this function:

Function ConvertToLongInteger(ByVal stValue As String) As Long
 On Error GoTo ConversionFailureHandler
 ConvertToLongInteger = CLng(stValue)  'TRY to convert to an Integer value
 Exit Function           'If we reach this point, then we succeeded so exit

ConversionFailureHandler:
 'IF we've reached this point, then we did not succeed in conversion
 'If the error is type-mismatch, clear the error and return numeric 0 from the function
 'Otherwise, disable the error handler, and re-run the code to allow the system to 
 'display the error
 If Err.Number = 13 Then 'error # 13 is Type mismatch
      Err.Clear
      ConvertToLongInteger = 0
      Exit Function
 Else
      On Error GoTo 0
      Resume
 End If
End Function

I chose Long (Integer) instead of simply Integer because the min/max size of an Integer in VBA is crummy (min: -32768, max:+32767). It's common to have an integer outside of that range in spreadsheet operations.

The above code can be modified to handle conversion from string to-Integers, to-Currency (using CCur() ), to-Decimal (using CDec() ), to-Double (using CDbl() ), etc. Just replace the conversion function itself (CLng). Change the function return type, and rename all occurrences of the function variable to make everything consistent.

Answer 4

Just use Val():

currentLoad = Int(Val([f4]))

Now currentLoad has a integer value, zero if [f4] is not numeric.

Answer 5

To put it on one line:

currentLoad = IIf(IsNumeric(oXLSheet2.Cells(4, 6).Value), CInt(oXLSheet2.Cells(4, 6).Value), 0)

Answer 6

Here are a three functions that might be useful. First checks the string for a proper numeric format, second and third function converts a string to Long or Double.

Function IsValidNumericEntry(MyString As String) As Boolean
'********************************************************************************
'This function checks the string entry to make sure that valid digits are in the string.
'It checks to make sure the + and - are the first character if entered and no duplicates.
'Valid charcters are 0 - 9, + - and the .
'********************************************************************************
Dim ValidEntry As Boolean
Dim CharCode As Integer
Dim ValidDigit As Boolean
Dim ValidPlus As Boolean
Dim ValidMinus As Boolean
Dim ValidDecimal As Boolean
Dim ErrMsg As String

ValidDigit = False
ValidPlus = False
ValidMinus = False
ValidDecimal = False

ValidEntry = True
For x = 1 To Len(MyString)
    CharCode = Asc(Mid(MyString, x, 1))
    Select Case CharCode

    Case 48 To 57 ' Digits 0 - 9
        ValidDigit = True

    Case 43 ' Plus sign

    If ValidPlus Then 'One has already been detected and this is a duplicate
        ErrMsg = "Invalid entry....too many plus signs!"
        ValidEntry = False
        Exit For
    ElseIf x = 1 Then 'if in the first positon it is valide
        ValidPlus = True
    Else 'Not in first position and it is invalid
        ErrMsg = "Invalide entry....Plus sign not in the correct position! "
        ValidEntry = False
        Exit For
    End If

    Case 45 ' Minus sign

    If ValidMinus Then 'One has already been detected and this is a duplicate
        ErrMsg = "Invalide entry....too many minus signs! "
        ValidEntry = False
        Exit For
    ElseIf x = 1 Then 'if in the first position it is valid
        ValidMinus = True
    Else 'Not in first position and it is invalid
        ErrMsg = "Invalide entry....Minus sign not in the correct position! "
        ValidEntry = False
        Exit For
    End If

    Case 46 ' Period

    If ValidDecimal Then 'One has already been detected and this is a duplicate
        ErrMsg = "Invalide entry....too many decimals!"
        ValidEntry = False
        Exit For
    Else
        ValidDecimal = True
    End If

    Case Else
        ErrMsg = "Invalid numerical entry....Only digits 0-9 and the . + - characters are valid!"
        ValidEntry = False
        Exit For

    End Select

Next

    If ValidEntry And ValidDigit Then
        IsValidNumericEntry = True
    Else
        If ValidDigit = False Then
            ErrMsg = "Text string contains an invalid numeric format." & vbCrLf _
            & "Use only one of the following formats!" & vbCrLf _
            & "(+dd.dd  -dd.dd  +dd  -dd  dd.d or dd)! "
        End If
        MsgBox (ErrMsg & vbCrLf & vbCrLf & "You Entered:   " & MyString)
        IsValidNumericEntry = False
    End If

End Function

Function ConvertToLong(stringVal As String) As Long
'Assumes the user has verified the string contains a valide numeric entry.
'User should call the function IsValidNumericEntry first especially after any user input
'to verify that the user has entered a proper number.

 ConvertToLong = CLng(stringVal)


End Function
Function ConvertToDouble(stringVal As String) As Double
'Assumes the user has verified the string contains a valide numeric entry.
'User should call the function IsValidNumericEntry first especially after any user input
'to verify that the user has entered a proper number.

    ConvertToDouble = CDbl(stringVal)

End Function