vba 如果字符串是数字，则将字符串转换为 int
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6202074/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert string to int if string is a number
提问by Captastic
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:
我需要将 VBA 中从 excel 获得的字符串转换为整数。为此，我正在使用CInt()哪个效果很好。但是，字符串可能不是数字，在这种情况下，我需要将整数设置为 0。目前我有：
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?
问题是我无法预测该单元格中可能存在的所有可能的非数字字符串。有没有一种方法可以告诉它如果是整数则进行转换，如果不是则设置为 0？
回答by ForEachLoop
Use IsNumeric. It returns true if it's a number or false otherwise.
使用IsNumeric. 如果是数字则返回真，否则返回假。
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
回答by PeterH
Cast to long or cast to int, be aware of the following.
转换为 long 或转换为 int，请注意以下事项。
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.
这些函数是 Excel VBA 中取决于系统区域设置的视图函数之一。因此，如果您像在欧洲的某些国家/地区那样在 double 中使用逗号，则在美国会遇到错误。
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:
例如，在欧洲 excel 版本 0,5 中使用 CDbl() 会表现良好，但在美国版本中它会导致 5。所以我建议使用以下替代方案：
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.
当然，您也可以想到人们使用逗号和点的情况，例如，三千为 3,000.00。如果您需要针对此类情况的功能，则必须检查其他解决方案。
回答by Isaac Comer-Wyrd
Try this:
currentLoad = ConvertToLongInteger(oXLSheet2.Cells(4, 6).Value)with this function:
试试这个：
currentLoad = ConvertToLongInteger(oXLSheet2.Cells(4, 6).Value)用这个功能：
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.
我选择了 Long (Integer) 而不是简单的 Integer 因为 VBA 中整数的最小/最大大小很糟糕（最小：-32768，最大：+32767）。在电子表格操作中，有一个超出该范围的整数是很常见的。
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.
可以修改上面的代码来处理从字符串到整数、到货币（使用 CCur() ）、到十进制（使用 CDec() ）、到双（使用 CDbl() ）等的转换。只需替换转换函数本身 (CLng)。更改函数返回类型，并重命名所有出现的函数变量，使所有内容保持一致。
回答by ray
To put it on one line:
把它放在一行上：
currentLoad = IIf(IsNumeric(oXLSheet2.Cells(4, 6).Value), CInt(oXLSheet2.Cells(4, 6).Value), 0)
回答by Excel Hero
Just use Val():
只需使用Val()：
currentLoad = Int(Val([f4]))
Now currentLoadhas a integer value, zero if [f4]is not numeric.
现在currentLoad有一个整数值，如果[f4]不是数字则为零。
回答by Doug
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.
这里有三个可能有用的函数。首先检查字符串的正确数字格式，第二个和第三个函数将字符串转换为 Long 或 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
