vba - 无法在 VBA 或 Excel 公式中将字符串转换为整数(即​​使字符串全是数字，也无法转换或数字错误)

Question

我的单元格内有一个字符串:105501008962100001

单元格 A10 包含此文本。

当我运行 ISTEXT(A10) 时，它返回 TRUE。

我使用了 VALUE(A10)，但它返回了 105501008962100000

当我乘以 A10 * 1 时，我也会得到 105501008962100000。

我还创建了一个 VBA 函数来转换为整数，但是当我创建检查点时，它会创建一个错误，指出转换为整数失败。

我不明白为什么我无法将此单元格内的字符串完全由数字组成时转换为整数。该数字是从 CSV 文件导入的。

Function ConvertToInteger(v1 As Variant) As Integer
On Error GoTo 100:

    'MsgBox Len(v1)

    Dim tempArray As Variant
    Dim arraySize As Integer
    arraySize = (Len(v1) - 1)
    ReDim tempArray(arraySize)

    Dim tempText As String
    Dim finalNumber As Integer
    Dim i As Integer

    For i = 1 To Len(v1)
        tempArray(i - 1) = CStr(Mid(v1, i, 1))
    Next

    tempText = Join(tempArray, "")
    tempText = CStr(tempText)

    'MsgBox tempText

     finalNumber = CInt(tempText)

     MsgBox finalNumber

    'ConvertToInteger = finalNumber

Exit Function
100:
    MsgBox "Failed to convert """ & v1 & """ to an integer.", , "Aborting - Failed Conversion"
    End
End Function

我对代码进行了修改，但仍然在末尾得到 0，而不是数字中的 1:

Function ConvertToInteger(v1 As Variant) As Double

    Dim tempArray As Variant
    Dim arraySize As Double
    arraySize = (Len(v1) - 1)
    ReDim tempArray(arraySize)

    Dim tempText As String
    Dim finalNumber As Double
    Dim i As Integer

    For i = 1 To Len(v1)
        tempArray(i - 1) = CStr(Mid(v1, i, 1))
    Next

    tempText = Join(tempArray, "")
    tempText = CStr(tempText)

    finalNumber = CDbl(tempText)

    MsgBox finalNumber

    'I get an error here when assigning finalNumber to ConvertToInteger
    'ConvertToInteger = finalNumber

End Function

Answer 1

您可以使用内部 Err 对象的 Description 属性在错误处理 block 中识别此错误(溢出):

100:
MsgBox "Failed to convert """ & v1 & """ to an integer." & vbCrLf & Err.Description, , "Aborting - Failed Conversion" 
End

当您实现它时，错误处理程序可以工作，但不会显示用于调试问题的有用信息。

确定为溢出错误后，请注意值 105501008962100001 超出了 Integer 或 Long 数据类型允许的大小。将其声明为 Double。

Dim tempArray As Variant
Dim arraySize As Double
arraySize = (Len(v1) - 1)
ReDim tempArray(arraySize)

请注意 Excel 中使用大数字的限制。您的数据可能会超出此范围。

要处理非常大的数字，请参阅:

Handling numbers larger than Long in VBA

随后:

http://tushar-mehta.com/misc_tutorials/project_euler/LargeNumberArithmetic.htm

这是一个将两个大数相加的简单示例。您将能够使用 cLarge 类对这些“数字”(实际上是字符串)执行算术运算。您的函数需要将类型声明从 Long/Double 更改为 String。

在标准模块中，执行以下操作:

Sub foo()
Dim lrg As New cLarge

MsgBox lrg.LargeAdd("105501008962100001", "205501231962100003")
End Sub

创建一个名为cLarge的类模块，并在该模块中添加以下代码:

'### Class module for adding very large (> Decimal precision) values
'    http://tushar-mehta.com/misc_tutorials/project_euler/LargeNumberArithmetic.htm
'
'    Modified by David Zemens, 9 December 2015
Option Explicit

Public cDecMax As Variant, cDecMaxLen As Integer, cSqrDecMaxLen As Integer
Private pVal As String

Public Sub Class_Initialize()
    Static Initialized As Boolean
    If Initialized Then Exit Sub
    Initialized = True
    cDecMax = _
        CDec(Replace("79,228,162,514,264,337,593,543,950,335", ",", ""))
            'this is 2^96-1, the limit on Decimal precision data in Excel/VBA
    cDecMaxLen = Len(cDecMax) - 1
    cSqrDecMaxLen = cDecMaxLen \ 2
End Sub


Function Ceil(X As Single) As Long
    If X < 0 Then Ceil = Fix(X) Else Ceil = -Int(-X)
End Function

Private Function addByParts(ByVal Nbr1 As String, ByVal Nbr2 As String) _
    As String
    Dim NbrChunks As Integer
    If Len(Nbr1) > Len(Nbr2) Then _
        Nbr2 = String(Len(Nbr1) - Len(Nbr2), "0") & Nbr2 _
    Else _
        Nbr1 = String(Len(Nbr2) - Len(Nbr1), "0") & Nbr1
    NbrChunks = Ceil(Len(Nbr1) / cDecMaxLen)
    Dim I As Integer, OverflowDigit As String, Rslt As String
    OverflowDigit = "0"
    For I = NbrChunks - 1 To 0 Step -1
        Dim Nbr1Part As String
        Nbr1Part = Mid(Nbr1, I * cDecMaxLen + 1, cDecMaxLen)
        Rslt = CStr(CDec(Nbr1Part) _
            + CDec(Mid(Nbr2, I * cDecMaxLen + 1, cDecMaxLen)) _
            + CDec(OverflowDigit))
        If Len(Rslt) < Len(Nbr1Part) Then
            Rslt = String(Len(Nbr1Part) - Len(Rslt), "0") & Rslt
            OverflowDigit = "0"
        ElseIf I = 0 Then
        ElseIf Len(Rslt) > Len(Nbr1Part) Then
            OverflowDigit = Left(Rslt, 1): Rslt = Right(Rslt, Len(Rslt) - 1)
        Else
            OverflowDigit = "0"
            End If
        addByParts = Rslt & addByParts
        Next I
End Function

Function LargeAdd(ByVal Nbr1 As String, ByVal Nbr2 As String) As String
    'Initialize
    If Len(Nbr1) <= cDecMaxLen And Len(Nbr2) <= cDecMaxLen Then
        LargeAdd = CStr(CDec(Nbr1) + CDec(Nbr2))
        Exit Function
        End If
    If Len(Nbr1) > cDecMaxLen Then LargeAdd = addByParts(Nbr1, Nbr2) _
    Else LargeAdd = addByParts(Nbr2, Nbr1)
End Function

所以，长话短说，您实际上不能使用这些“数字”，并且严格不能将它们“转换”为整数/长整型值。您需要使用自定义类实现来对字符串值执行数学运算。