如果 string 是数字，则将 string 转换为 int

我需要将 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

问题是，我不能预测所有可能的非数字字符串可能在这个单元格中。有没有一种方法，我可以告诉它转换，如果它是一个整数，并设置为0，如果没有？

653355 次浏览
小开
最佳答案

使用 IsNumeric，否则返回 true，如果是数字或 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
小开

把它放在一句话里:

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

试试这个: 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

我选择 Long (Integer)而不是简单的 Integer，因为 VBA 中 Integer 的最小/最大大小很糟糕(min: -32768，max: + 32767)。在电子表格操作中，常常有一个超出该范围的整数。

可以修改上面的代码来处理从字符串到-Integers、到-Currency (使用 CCur ())、到-Decimal (使用 CDec ())、到-Double (使用 CDbl ())的转换，等等。只需替换转换函数本身(CLng)。更改函数返回类型，并重命名函数变量的所有匹配项，以使所有内容一致。

小开

这里有三个可能有用的函数。首先检查字符串是否有合适的数字格式，第二个和第三个函数将字符串转换为 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
小开

强制转换为 long 或强制转换为 int，请注意以下内容。

这些函数是 ExcelVBA 中依赖于系统区域设置的视图函数之一。因此，如果你像欧洲的一些国家那样，在你的 double 中使用逗号，你将在美国经历一个错误。

例如，在欧洲的 excel-version 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

当然，你也可以想到人们使用逗号和点的情况，例如，3000.00就是3000.00。如果您需要这些情况下的功能，那么您必须检查另一个解决方案。

小开

使用 Val():

currentLoad = Int(Val([f4]))

现在 currentLoad有一个整数值，如果 [f4]不是数字，则为零。


提交答案