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  lt  gt   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

User · Answer

Just use Val     currentLoad   Int Val  f4      Now currentLoad has a integer value  zero if  f4  is not numeric

User · Answer

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    amp  vbCrLf                amp   Use only one of the following formats    amp  vbCrLf                amp     dd dd  -dd dd   dd  -dd  dd d or dd             End If         MsgBox  ErrMsg  amp  vbCrLf  amp  vbCrLf  amp   You Entered       amp  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

User · Answer

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

User · Answer

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

User · Answer

To put it on one line   currentLoad   IIf IsNumeric oXLSheet2 Cells 4  6  Value   CInt oXLSheet2 Cells 4  6  Value   0

User · Answer

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   amp  vbCrLf  amp  vbCrLf  amp   Err      amp  Err Number  amp            vbCrLf  amp   Description     amp  Err Description End Sub

[excel] Convert string to int if string is a number

Examples related to excel

Examples related to vba