Declaring and initializing a dynamic array of sheets in VBA

Question

I am trying to create an array of sheets in Excel. Each sheet has several columns and rows that need to be searched, compared, and filled accordingly. I am having trouble creating the array of sheets. I keep getting a Subscript out of range error on line #27. It happens on all 4 of the sheets if I comment out the preceding ones.

Sub news()

    'activate sheets
    Sheet1.Activate
    Sheet2.Activate
    Sheet3.Activate
    Sheet4.Activate

    'array of letters for the columns
    Dim alpha(1 To 13) As String
    alpha(1) = "a"
    alpha(2) = "b"
    alpha(3) = "c"
    alpha(4) = "d"
    alpha(5) = "e"
    alpha(6) = "f"
    alpha(7) = "g"
    alpha(8) = "h"
    alpha(9) = "i"
    alpha(10) = "j"
    alpha(11) = "k"
    alpha(12) = "l"
    alpha(13) = "m"

    'array of sheets
    Dim shets() As Sheets, sheetCount As Integer
    Set shets(1) = Sheets("Sheet1")
    Set shets(2) = Sheets("Sheet2")
    Set shets(3) = Sheets("Sheet3")
    Set shets(4) = Sheets("Sheet4")

    'used to make sure i am not shifted and photos goes to photos, videos to videos, and compliance to compliance
    Dim newShift As Integer
    newShift = 7

    'for loop counter variables
    Dim i, j, k As Integer

    'goes through the sheets
    For i = 2 To sheetCount
        'goes through the columns
        For j = 3 To 7 Step 2
            'goes through the rows
            For k = 2 To ThisWorksheet.Rows.count
                If (Sheets(shets(i - 1)).Cells(k, alpha(j)) = Sheets(shets(i)).Cells(k, alpha(j))) Then
                    Sheets(shets(i)).Cells(k, alpha(j + newShift)) = False
                ElseIf (Sheets(shets(i - 1)).Cells(k, alpha(j)) < Sheets(shets(i)).Cells(k, alpha(j))) Then
                    Sheets(shets(i)).Cells(k, alpha(j + newShift)) = True
                Else
                    Sheets(shets(i)).Cells(k, alpha(j + newShift)) = "ERROR"
                End If
            Next
            newShift = newShift - 1
        Next
    Next

End Sub

Answer 1

There were quite a few questionable bits of code in what you posted above. I have gone through and re-written the offending lines, and included comments justifying why in the below code.

Not only should this fix your "out of range" error (because you didn't declare the size of your array) but it will fix the other errors you were yet to encounter (not declaring variable values, looping through every row in each worksheet, not referencing sheet objects properly, ...).

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