VBA: Subscript Out of Range - Size of array index is larger than array size

Question

I created an array index (tickerIndex).

When I run the code, I get the error

subscript out of range

When I run the code, for some reason, the tickerIndex variable counts up to 13 which is 1 more than the size of the array.

The size of the tickers array is 12.

The tickerIndex variable is used to loop the tickers, tickerVolumes, tickerStartingPrices, and tickerEndingPrices arrays.

    Dim tickers(12) As String
    
    tickers(0) = "AY"
    tickers(1) = "CSIQ"
    tickers(2) = "DQ"
    tickers(3) = "ENPH"
    tickers(4) = "FSLR"
    tickers(5) = "HASI"
    tickers(6) = "JKS"
    tickers(7) = "RUN"
    tickers(8) = "SEDG"
    tickers(9) = "SPWR"
    tickers(10) = "TERP"
    tickers(11) = "VSLR"
    
    'Activate data worksheet
    Worksheets(yearValue).Activate
    
    'Get the number of rows to loop over
    RowCount = Cells(Rows.Count, "A").End(xlUp).Row
    
    
    Dim tickerIndex As Integer
    tickerIndex = 0
    

    
    Dim tickerVolumes(12) As Long
    Dim tickerStartingPrices(12) As Single
    Dim tickerEndingPrices(12) As Single
    
    
    For tickerIndex = 0 To 11
    ticker = tickers(tickerIndex)
    tickerVolumes(tickerIndex) = 0
    
        
       
        Worksheets(yearValue).Activate
        For i = 2 To RowCount
    
            'Increase volume for current ticker [error on this line]
            ***If Cells(i, 1).Value = tickers(tickerIndex) Then***
                tickerVolumes(tickerIndex) = tickerVolumes(tickerIndex) + Cells(i, 8).Value
            End If
        
        
            ' Check if the current row is the first row with the selected tickerIndex.
            If Cells(i - 1, 1).Value <> tickers(tickerIndex) And Cells(i, 1).Value = tickers(tickerIndex) Then
                tickerStartingPrices(tickerIndex) = Cells(i, 6).Value
            End If
        
            'check if the current row is the last row with the selected ticker
            'If the next row's ticker doesn't match, increase the tickerIndex.
            If Cells(i + 1, 1).Value <> tickers(tickerIndex) And Cells(i, 1).Value = tickers(tickerIndex) Then
                tickerEndingPrices(tickerIndex) = Cells(i, 6).Value
            
            End If

            'Increase the tickerIndex if the next row’s ticker doesn’t match the previous row’s ticker.
            If Cells(i + 1, 1).Value <> Cells(i - 1, 1).Value Then
                tickerIndex = tickerIndex + 1
            
            End If
    
        Next i
    Next tickerIndex

    
    'Loop through arrays to output the Ticker, Total Daily Volume, and Return.
    For i = 0 To 11
        
        Worksheets("AllStocksAnalysis").Activate
        Cells(4 + i, 1).Value = tickers(tickerIndex)
        Cells(4 + i, 2).Value = tickerVolumes(tickerIndex)
        Cells(4 + i, 3).Value = (tickerEndingPrices(tickerIndex) / tickerStartingPrices(tickerIndex)) - 1
        
    Next i

Answer 1

Don't hard code your array bounds.

Do this

For tickerIndex = LBound(tickers) To UBound(tickers)
    ticker = tickers(tickerIndex)
    ...

or better yet this

For Each ticker In tickers
    ...

instead of this

For tickerIndex = 0 To 11
    ticker = tickers(tickerIndex)
    ...