时间戳

首页 > 解决方案 > 为什么 Excel VBA 中的这个自定义函数不能正常工作。代码或算法有问题吗？

excel - 为什么 Excel VBA 中的这个自定义函数不能正常工作。代码或算法有问题吗？

问题描述

我为自定义函数编写了一个代码，用于在 Excel VBA 中添加数字（大于 15 位）。如果我运行此功能 1 次，这工作正常，但多次使用时会出错。我是这个领域的新手，所以真的很感激任何帮助。

我已经写好了代码。请看一看。

Public Function AddBigNumbers(n1 As String, n2 As String) As String
i1 = 0
i2 = 0
i3 = Int(Log(n1) / Log(10))
i4 = Int(Log(n2) / Log(10))
i5 = 0
i6 = 0

If i3 = i4 Then
    Dim Ad() As Long
    ReDim Ad(1 To i3 + 1) As Long
        For i2 = 1 To i3 + 1 Step 1
            Ad(i2) = (Mid(n1, i2, 1))
        Next i2

i1 = 0
i2 = 0
     Dim Bd() As Long
     ReDim Bd(1 To i3 + 1) As Long
        For i2 = 1 To i3 + 1 Step 1
            Bd(i2) = (Mid(n2, i2, 1))
        Next i2

Dim D() As Long
ReDim D(1 To i3 + 1) As Long

i1 = 0
     For i1 = 1 To i3 + 1 Step 1
        If Bd(i1) > Ad(i1) Then
            D(i1) = 1
        End If
     Next i1
i1 = 0
    For i1 = 1 To i3 + 1 Step 1
        If Bd(i1) = Ad(i1) Then
            D(i1) = 0
        End If
    Next i1
i1 = 0
    For i1 = 1 To i3 + 1 Step 1
        If Bd(i1) < Ad(i1) Then
            D(i1) = -1
        End If
    Next i1

i1 = 0
    For i1 = 1 To i3 + 1 Step 1
        If D(i1) > 0 Then i5 = n2 And i1 = i3 + 1 Else If D(i1) = 0 Then i1 = i1 Else i5 = n1
    Next i1
i1 = 0
    For i1 = 1 To i3 + 1 Step 1
        If D(i1) > 0 Then i6 = n1 And i1 = i3 + 1 Else If D(i1) = 0 Then i1 = i1 Else i6 = n2
    Next i1
End If

If i3 > i4 Then i5 = n1 Else i5 = n2
If i3 > i4 Then i6 = n2 Else i6 = n1


i7 = Int(Log(i5) / Log(10))
i8 = Int(Log(i6) / Log(10))

i1 = 0
i2 = 0
Dim A() As Long
ReDim A(1 To i7 + 1) As Long
    For i2 = 1 To i7 + 1 Step 1
        A(i2) = (Mid(i5, i2, 1))
    Next i2
i1 = 0
i2 = 0
    Dim B() As Variant
    ReDim B(1 To i7 + 1) As Variant
        If i7 > i8 Then
            For i1 = 1 To i7 - i8 Step 1
                B(i1) = 0
            Next i1

            For i2 = i7 - i8 + 1 To i7 + 1 Step 1
                B(i2) = Mid(i6, i2 - i7 + i8, 1)
            Next i2
        End If

         If i7 = i8 Then
             For i2 = 1 To i7 + 1 Step 1
                 B(i2) = Mid(i6, i2, 1)
             Next i2
         End If
i1 = 0
i2 = 0
    Dim C() As Variant
    ReDim C(1 To i7 + 1) As Variant
        For i2 = 1 To i7 + 1 Step 1
            C(i2) = CInt(A(i2)) + B(i2)
        Next i2

i1 = 0
i2 = 0
        For i2 = i7 + 1 To 2 Step -1
            C(i2 - 1) = C(i2 - 1) + Int(C(i2) / 10)
            C(i2) = C(i2) - 10 * Int(C(i2) / 10)
        Next i2
i9 = 0
i9 = Join(C, "")
'i9 = WorksheetFunction.Concat("'", Join(C, ""))
AddBigNumbers = i9
i1 = 0
i2 = 0
End Function

如果我运行它一次（意味着如果我使用它一次）它工作正常，但多次运行它会给出错误“#Value”。

标签： excelvbafunction

解决方案

请尝试以下代码。

Function SumBigNumbers(s1 As String, s2 As String) As String

    Dim n1 As Double, n2 As Double

    n1 = CDbl(s1)
    n2 = CDbl(s2)
    SumBigNumbers = CStr(n1 + n2)
End Function

该代码只是将两个长数字字符串转换为实际数字，将它们相加并将结果转换为字符串。VBA 将以科学计数法返回一串数字。但是请注意，这仅用于显示目的。在内部，它是一个正常（如果很长）的数字。

因此，在计算结果之后，即在调用过程中，会发生向可读内容的转换。

Private Sub CallSum()

    With ActiveSheet.Cells(16, 1)
        .Value = SumBigNumbers("12345678987654321", "34567890123987654")
        .NumberFormat = "0.00"
    End With
End Sub

事实证明，一个简单的 NumberFormat 就可以完成这项工作。我只是想知道这是否会像我的示例中那样处理更大的数字。

推荐阅读