excel - VBA 变体索引超出范围

Question

我是 VBA 新手，我需要扩展现有工作表并保持其格式。需要完成 7 个长度可变(以行为单位)和 14 列宽度的部分。所以我想做的是以下几点:

找到部分开始的行

从每个部分中选择数据并将其保存到一个数组中(我认为这是一个长度为 7 的数组，每个条目包含一个 2 维数组，其中包含数据)

选择我的新数据并使用该新数据扩展现有数组(在最后一步中创建)

用我新创建的数组

覆盖当前工作表

添加格式

我设法完成了第 1 步，目前正在第 2 步苦苦挣扎:我需要创建一个长度可变的数组，我可以在其中插入数据。

到目前为止我的代码:

' this should create the array with the 7 entries
' "myArray" contains the row-numbers where the sections start
Function GenerateSheetArray(sheet As Worksheet, myArray As Variant) As Variant

    Dim finalArray As Variant
    Dim myInt As Integer

    'here each entry should be filled
    For i = 0 To 6
        myInt = myArray(i)
        finalArray(i) = GenerateArrayPart(sheet, myInt)
    Next

    GenerateSheetArray = finalArray

End Function

'This should fill each entry with the data of corresponding section
Function GenerateArrayPart(sheet As Worksheet, headline As Integer) As Variant
    Dim leftIndex As Integer, rightIndex As Integer, rowcount As Integer
    Dim sheetArray() As Variant

    rowcount = 0
    leftIndex = 1
    rightIndex = 14
    i = headline + 1

    Do While sheet.Cells(i, 1) <> ""
        rowcount = rowcount + 1
        i = i + 1
    Loop

    If (rowcount > 0) Then
        For colIndex = leftIndex To rightIndex
            For rowIndex = 1 To rowcount
                Row = headline + rowIndex
                sheetArray(rowIndex - 1, colIndex - 1) = sheet.Cells(Row, colIndex)
            Next
        Next
    End If

    GenerateArrayPart = sheetArray
End Function

现在我的问题是，VBA 在这一行抛出一个错误:

'atm rowIndex and colIndex are 1, Row is 40
'I know that there is data in that cell
sheetArray(rowIndex - 1, colIndex - 1) = sheet.Cells(Row, colIndex)

VBA 说:

Index out of range

在方法 GenerateArrayPart .
这怎么可能发生？我以为 variant几乎可以是所有东西，也不需要使用边界？

Answer 1

您在数组中没有任何值。因此，数组只被声明而不被维度化。

尝试这个:

Dim finalArray As Variant
Redim finalArray(6)

现在，数组内部将有 7 个值。从 0 到 6。同样的错误发生在 Function GenerateArrayPart , 数组 sheetArray .在那里，您需要将数组声明为多维数组。例如。 Redim sheetArray (N, M) .

要查看一些小的工作示例，请查看以下代码:

Sub TestMe()

    Dim finalArr As Variant
    ReDim finalArr(6)

    Dim i As Long
    For i = LBound(finalArr) To UBound(finalArr)
        finalArr = GenerateArrPart(i)
    Next i

    For i = LBound(finalArr) To UBound(finalArr)
        Debug.Print i; finalArr(i, i)
    Next i

End Sub

Public Function GenerateArrPart(a As Long) As Variant

    Dim i As Long
    Dim arrReturn As Variant
    ReDim arrReturn(a + 1, a + 1)

    For i = LBound(arrReturn) To UBound(arrReturn)
        arrReturn(i, i) = a * i
    Next i

    GenerateArrPart = arrReturn

End Function

这是输出:

 0  0 
 1  6 
 2  12 
 3  18 
 4  24 
 5  30 
 6  36 
 7  42