Vba convert string to int if string is a number

Question

I need to convert a string retrieved from excel to VBA in interger. For this, I use CInt (), which works well. However, there is a possibility that the string may be something other than a number, in which 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 the possible numeric strings that may be in this cell. Is there a way I can say to convert if it is an integer and set to 0 if not?

Cheers Cap

Answer 1

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

 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

Pass to long or cast to int, remember the following.

These functions are one of the presentation functions in Excel VBA, which depend on the regional settings of the system. Therefore, if you use a comma in your double, as in some countries in Europe, you will experience a mistake in the USA.

For example, in the European version, excel version 0.5 will work well with CDbl (), but in the US version this will result in 5. Therefore, I recommend using 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 recall cases when people use commas and periods, for example, three thousand, like 3000.00. If you need functions for such cases, you need to check out another solution.

Answer 3

Try the following: 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 a simple Integer, because the minimum / maximum size of Integer in VBA is crummy (min: -32768, max: +32767). Typically, a spreadsheet integer exists outside this range.

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

Answer 4

To put it on one line:

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

Answer 5

Here are three features that may be helpful. First it checks the string for the correct number format, the second and third functions convert the 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