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

Never modify the counter variable of an for-loop within the loop. — FunThomas
– FunThomas, Commented May 3, 2021 at 7:22
Why do tickerIndex = tickerIndex + 1 while the looping is already done by For tickerIndex = 0 To 11? — shahkalpesh
– shahkalpesh, Commented May 3, 2021 at 7:25
Side notes: Declare your variables and use Option Explicit to force variable declarations. Fully qualify your range references instead of activating worksheets. — T.M.
– T.M., Commented May 3, 2021 at 7:28
Thanks @shahkalpesh, I removed the part that was incrementing the tickerIndex ad the code worked fine — ola
– ola, Commented May 3, 2021 at 15:05

Nicholas Hunter · Accepted Answer · 2021-05-03 13:45:15Z

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)
    ...

answered May 3, 2021 at 13:45

Nicholas Hunter

1,8561 gold badge12 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ola Over a year ago

Thanks @NicholasHunter, I referenced the array bounds the way you said, I also removed the block of code that was incrementing the tickerIndex (that was throwing off the count by 1)

T.M. Over a year ago

@NicholasHunter Fyi c.f. Mathieu Guindon at SO post regarding an array function citing "Also I'd warmly recommend changing your loop to a For...Next loop. Arrays don't want to be iterated with For Each - see this article."

Nicholas Hunter Over a year ago

@T.M. New info to me. Thanks for that.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related