在VBA中声明和初始化动态图纸数组

在你上面发布的内容中有相当多的可疑代码。我已经阅读并重新编写了这些冒犯的行,并在下面的代码中包含了解释原因的注释。

这不仅可以修复“超出范围”的错误(因为您没有声明数组的大小),还可以修复您尚未遇到的其他错误(没有声明变量值、在每个工作表中的每一行循环、没有正确引用工作表对象……)。

Sub news()
    ' No need to activate sheets
    ' No need for array of letters for the columns: '.Cells(row,col)' can take a number for 'col'
    ' Integers replaced by Longs, no real incentive to use Integer type and Long can be larger

    ' Array of sheets: use WorkSheet objects, not a Sheets object 
    Dim shets() As WorkSheet
    ' Remember to assign a value to sheetCount
    Dim sheetCount As Long: sheetCount = 4
    ' Must declare the size of your array, this method keeps it generic
    ' could have used 'Dim shets(1 To 4) As WorkSheet'
    Dim n As Long
    ReDim shets(1 To sheetCount)
    ' Keeping with generic theme, loop over shets to define sheets, makes expanding easier
    For n = 1 To sheetCount
        ' Fully qualify sheets by using workbook object
        Set shets(n) = ThisWorkbook.Sheets("Sheet" & n)
    Next n
    ' Used to make sure photos goes to photos, videos to videos, and compliance to compliance
    Dim newShift As Long: newShift = 7
    ' For loop counter variables: Must specify EACH type, 'Dim i, j, k As Long' declares i and j as Variants
    Dim i As Long, j As Long, k As Long
    ' Go through the sheets
    For i = 2 To sheetCount
        ' Go through the columns
        For j = 3 To 7 Step 2
            ' Go through the rows. Don't just use '.Rows' object as that includes all unused rows in sheet!
            ' Also using one of the sheet objects, as 'ThisWorksheet' doesn't exist
            For k = 2 To shets(i).UsedRange.Rows.Count
                ' Don't access sheet objects using 'Sheets(shets(..))', simply use 'shets(..)'
                If shets(i - 1).Cells(k, j) = shets(i).Cells(k, j) Then
                    shets(i).Cells(k, j + newShift).Value = False
                ElseIf shets(i - 1).Cells(k, j) < shets(i).Cells(k, j) Then
                    shets(i).Cells(k, j + newShift).Value = True
                Else
                    shets(i).Cells(k, j + newShift).Value = "ERROR"
                End If
            Next
            newShift = newShift - 1
        Next
    Next
End Sub