excel - 如果返回或输出超出 FOR 循环，则 EXCEL 中的自定义函数不起作用

Question

Function DISCOUNT(quantity) ' "1-2,3,4,5-10,23" is the data in <quantity>
    Dim LString As String
    Dim LArray() As String
    Dim Daysfromto() As String
    Dim Size As Double
    Dim Days As Double
    Dim Totaldays As Double


        Totaldays = 0
        LString = quantity
        LArray = Split(LString, ",")
        Size = UBound(LArray) - LBound(LArray) + 1
                For i = 0 To Size
                        Contains = InStr(LArray(i), "-")
                        If Contains = 2 Then
                            Daysfromto = Split(LArray(i), "-")
                            Totaldays = Totaldays + Daysfromto(1) - Daysfromto(0) + 1
                        ElseIf Contains = 0 Then
                            Totaldays = Totaldays + 1
                        End If
                        MSGBOX Totaldays ' this works here
                Next i
    MSGBOX Totaldays ' this does not work here
    DISCOUNT = Totaldays ' this does not work here
End Function

Answer 1

LArray = Split(LString, ",")

默认情况下，这会创建一个从零开始的一维数组。对于您的示例，这将是 LArray(0 to 4)一共有5个数组元素。零、一、二、三、四是 5 个数组元素。

使用时，

Size = UBound(LArray) - LBound(LArray) + 1

...这与 Size = 4 - 0 + 1 相同正确显示了 5 个数组元素。但是，当您使用时，

For i = 0 To Size

...您尝试使用 LArray(i) 访问总共 6 个数组元素.零一二三四五是 六 数组元素，而不是 5。

解决方案:

一直使用，

for i = lbound(LArray) to ubound(LArray)
    ...
next i

使用这种方法，您将永远不会越界。

使用Option Explicit .

不要使用 On Error Resume Next .

“不工作”既不是有效的错误代码，也不是错误描述。

代码重写

Option Explicit

Sub main()
    Debug.Print DISCOUNT("1-2,3,4,5-10,23")
End Sub

Function DISCOUNT(quantity) ' "1-2,3,4,5-10,23" is the data in <quantity>
    Dim LString As String
    Dim LArray() As String
    Dim Daysfromto As Variant
    Dim Days As Double
    Dim Totaldays As Double
    Dim i As Long, Contains As Long


    Totaldays = 0
    LString = quantity
    LArray = Split(LString, ",")

    For i = LBound(LArray) To UBound(LArray)
            Contains = InStr(LArray(i), "-")
            If Contains > 0 Then
                Daysfromto = Split(LArray(i), "-")
                Totaldays = Totaldays + CLng(Daysfromto(1)) - CLng(Daysfromto(0)) + 1
            ElseIf Contains = 0 Then
                Totaldays = Totaldays + 1
            End If
            'Debug.Print Totaldays  ' this works here
    Next i

    'Debug.Print Totaldays
    DISCOUNT = Totaldays

End Function