Convert string to int if string is a number

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?

6 Answers

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.

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.

Just use Val():

currentLoad = Int(Val([f4])) 

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

To put it on one line:

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

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