excel - 当我在工作表中使用此自定义函数 (MultiplyBigNumbers) 时出现错误。代码或语法有问题吗？

Question

我编写了用于大数字乘法的代码（Excel VBA 自定义函数），并以字符串格式显示正确的结果。但是这段代码似乎有问题，我无法弄清楚。

图像以更好地理解逻辑。

上图显示了两种情况。矩阵顺序将根据位数改变。此外，数字 2 将仅为 1 位或 2 位数字，仅此而已。只有数字 1 会超过 20 或 30 位数字。

我已经写好了代码。

Public Function MultiplyBigNumbers(n1 As String, n2 As String) As String
    Dim i1 As Long, i2 As Long, i3 As Long, i4 As Long
    Dim i5 As Variant, i6 As Variant
    Dim i7 As Long, i8 As Long



i3 = Int(Log(n1) / Log(10))
i4 = Int(Log(n2) / Log(10))
i5 = 0
i6 = 0

    Select Case True
        Case i3 = i4
            ReDim Ad(1 To i3 + 1) As Long
            ReDim Bd(1 To i3 + 1) As Long
            For i2 = 1 To i3 + 1 Step 1
                Ad(i2) = (Mid(n1, i2, 1))
                Bd(i2) = (Mid(n2, i2, 1))
            Next

            For i1 = 1 To i3 + 1
                If Bd(i1) > Ad(i1) Then
                    i5 = n2
                    i6 = n1
                    Exit For
                ElseIf Bd(i1) <= Ad(i1) Then
                    i5 = n1
                    i6 = n2
                End If
            Next

        Case i3 > i4
            i5 = n1
            i6 = n2

        Case Else
            i5 = n2
            i6 = n1
    End Select


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

i3 = 0
i4 = 0
    Dim A() As Long
    ReDim A(1, 1 To i7) As Long
        For i3 = 1 To i7 Step 1
            A(1, i3) = Mid(i5, i3, 1)
        Next i3
i3 = 0
i4 = 0
    Dim B() As Long
    ReDim B(1 To i7, 1 To i7 + i8 - 1) As Long

    If i8 = 2 Then
            For i3 = 1 To i7 Step 1
                For i4 = 1 To i7 + i8 - 1 Step 1
                    If i3 = i4 Then
                        B(i3, i4) = Mid(i6, 1, 1)
                    ElseIf i4 - i3 = 1 Then
                        B(i3, i4) = Mid(i6, 2, 1)
                    Else
                        B(i3, i4) = 0
                    End If
                Next i4
            Next i3
     Else
            For i3 = 1 To i7 Step 1
                For i4 = 1 To i7 + i8 - 1 Step 1
                    If i4 - i3 = 1 Then
                        B(i3, i4) = Mid(i6, 1, 1)
                    Else
                        B(i3, i4) = 0
                    End If
                Next i4
            Next i3
     End If

i3 = 0
i4 = 0
Dim k As Long
k = 0
    Dim D() As Long
    ReDim D(1, 1 To i7 + i8 - 1) As Long
        For i3 = 1 To i7 + i8 - 1 Step 1
            For k = 1 To i7 Step 1
                D(1, i3) = D(1, i3) + A(1, k) * B(k, i3)
            Next k
        Next i3
i3 = 0
i4 = 0
    For i3 = i7 + i8 - 1 To 2 Step -1
       D(1, i3 - 1) = D(1, i3 - 1) + Int(D(1, i3) / 10)
       D(1, i3) = D(1, i3) - 10 * Int(D(1, i3) / 10)
    Next i3

    Dim C() As Variant
    ReDim C(1 To i7 + i8 - 1) As Variant
        For i3 = 1 To i7 + i8 - 1 Step 1
            C(i3) = D(1, i3)
        Next i3

    MultiplyBigNumbers = Join(C, "")

End Function

标签： excelvba

Answer 1

我承认我的Log数学不存在，如果一个人想要高度技术化的话。但是，我将您的错误捕获为因为这行代码引发了错误：i4 = Int(Log(n2) / Log(10))when n2=0。

这是一个示例测试来显示：

Sub testiWishiKnewMathBetterThis()

'this will work
    MsgBox Log(500) / Log(10)

'this one won't.
    MsgBox Log(0) / Log(10)

End Sub

这是您将收到的错误的屏幕截图：