我在 VBA 中的代码使索引超出范围，但我确信数组的长度正确[重复]

我需要设置val1 and val2分配给变量的数字total。无论我改变变量什么totalto，0-8，表示索引越界。我还打印了我用 MsgBox 引用的每个列表的长度，它显示为 8。

我尝试将索引更改为该范围内的任何位置，但仍然会引发错误。

Sub dataValidation()
    Dim totalFraud As Range
    Dim leftTheBank As Range
    Dim stopped As Range
    Dim totalFraudCells As Variant
    Dim leftTheBankCells As Variant
    Dim stoppedCells As Variant
    Dim fraudCell As Variant
    Dim leftCell As Variant
    Dim stopCell As Variant
    Dim total As Integer
    Dim wb As Workbook
    Dim ws As Worksheet

    Set ws = Sheets("Sheet1")
    ws.Select

    Set totalFraud = ws.Range("TotalFraud")
    Set leftTheBank = ws.Range("LeftBank")
    Set stopped = ws.Range("Stopped")

    totalFraudCells = totalFraud.Value
    leftTheBankCells = leftTheBank.Value
    stoppedCells = stopped.Value

    total = 0

    For Count = 0 To UBound(totalFraudCells) - LBound(totalFraudCells) + 1
        Dim sum As Integer
        Dim val1 As Integer
        Dim val2 As Integer
    
        val1 = leftTheBankCells(3)
        val2 = stoppedCells(3)
    
        sum = val1 + val2
    
        total = total + 1 
    Next
End Sub

我希望在这种情况下val1 and val2设置为数组的索引 0，两者都应该是整数。

For Count = 0 To UBound(totalFraudCells) - LBound(totalFraudCells) + 1

您假设下限是0.

Dim totalFraud As Range
...
totalFraudCells = totalFraud.Value

But totalFraudCells是一个基于 1 的二维变体数组。

这些同样如此：

val1 = leftTheBankCells(3)
val2 = stoppedCells(3)

If you know或者希望处理一维数组，您可以使用Application.WorksheetFunction.Transpose让他们这样：

totalFraudCells = Application.WorksheetFunction.Transpose(totalFraud.Value)
leftTheBankCells = Application.WorksheetFunction.Transpose(leftTheBank.Value)
stoppedCells = Application.WorksheetFunction.Transpose(stopped.Value)

现在您正在查看一维数组，但它们仍然是基于一的。

根据经验，不要假设数组的基数是什么。使用LBound计算你的下限For loop:

For Count = LBound(totalFraudCells) To UBound(totalFraudCells)

有了这个每一个值Count将在范围内totalFraudCells array.

如果你是not处理单列范围，那么你must处理变体数组的二维性质；LBound and UBound可以采用第二个参数作为您想要的下限或上限的维度：

For Count = LBound(totalFraudCells, 1) To UBound(totalFraudCells, 1)

如果您需要迭代所有行和列，你需要嵌套循环，内循环迭代LBound(totalFraudCells, 2) To UBound(totalFraudCells, 2)对于第一维中的每个下标。