"Subscript out of range" When Using Array

Question

I am new to VBA and encountered the Error 9 issue "Subscript out of Range" when trying to use an array to calculate the greatest value in a list of numbers. I have a stacked bar chart across several teams (as x-axis values) and categories (as series). For each team, there are numbers from each category (incident ticket status), and I want to find which team has the greatest number of tickets and use that info to format my bar chart. The problematic line of code is highlighted. I've read many previous threads about Error 9 and VBA arrays, but the issue remains. Could anyone give me a hint about what goes wrong in my code? I'll greatly appreciate it.

Sub ChangeByTeamChartColor()

Workbooks.Application.ActiveWorkbook.Sheets("By Team - Incident State").Select
ActiveSheet.ChartObjects("By Team Chart").Select

Dim s As Series
Dim teamTotal() As Integer
Dim seriesCount As Integer, seriesIterator As Integer, pointIterater As Integer
**ReDim teamTotal(1 To ActiveChart.SeriesCollection(1).Points.Count)**

'Iterate through series and set colors
For seriesIterator = 1 To ActiveChart.SeriesCollection.Count

    Set s = ActiveChart.SeriesCollection(seriesIterator)
        If s.Name = "Active >24 h" Or s.Name = "Active" Then
            s.Format.Fill.ForeColor.RGB = RGB(192, 80, 77) 'Red Accent 2 Light 80
            ElseIf s.Name = "Active <24 h" Or s.Name = "New" Then
            s.Format.Fill.ForeColor.RGB = RGB(247, 150, 70) 'Orange
            ElseIf s.Name = "Pending Customer" Then
            s.Format.Fill.ForeColor.RGB = RGB(79, 129, 189) 'Blue
            ElseIf s.Name = "Pending Vendor" Then
            s.Format.Fill.ForeColor.RGB = RGB(128, 100, 162) 'Purple
            ElseIf s.Name = "Scheduled" Then
            s.Format.Fill.ForeColor.RGB = RGB(155, 187, 89) 'Green
            ElseIf s.Name = "Closed" Or s.Name = "Resolved" Then
            s.Format.Fill.ForeColor.RGB = RGB(148, 138, 84) 'Brown
            ElseIf s.Name = "Unassigned" Then
            s.Format.Fill.ForeColor.RGB = RGB(255, 192, 0) 'Yellow
        End If

    'Find the "Grand Total" datapoint in each series and hide it
    For pointIterater = 1 To s.Points.Count
        If s.XValues(pointIterater) = "Grand Total" Then
               s.Points(pointIterater).Interior.ColorIndex = xlNone
        End If
' The following line gives the error ===================================
        teamTotal(pointIterator) = teamTotal(pointIterator) + s.Values(pointIterator)
' ========================================================================
    Next pointIterater
Next seriesIterator

End Sub

Arrays are zero indexed. The first element is "0", and you have to be careful when looping to the end of a collection via .Count. You want to end it at .Count - 1 — rory.ap
@roryap I'm assuming it's the 3rd line from the bottom with the two asterisks, along with the ReDim of the array prior to the For Loop. — TMH8885
Two words: Option Explicit. You've declared pointIteratEr but then used pointIteratOr as the index. — Rory

rory.ap rory.ap · Accepted Answer · 2015-12-16T15:59:38

Arrays are zero indexed (by default -- see comments below). The first element is "0", and you have to be careful when looping to the end of a collection via .Count. You want to end it at .Count - 1

Change your code to something like this:

For seriesIterator = 0 To ActiveChart.SeriesCollection.Count - 1

    Set s = ActiveChart.SeriesCollection(seriesIterator)
        If s.Name = "Active >24 h" Or s.Name = "Active" Then
            s.Format.Fill.ForeColor.RGB = RGB(192, 80, 77) 'Red Accent 2 Light 80
            ElseIf s.Name = "Active <24 h" Or s.Name = "New" Then
            s.Format.Fill.ForeColor.RGB = RGB(247, 150, 70) 'Orange
            ElseIf s.Name = "Pending Customer" Then
            s.Format.Fill.ForeColor.RGB = RGB(79, 129, 189) 'Blue
            ElseIf s.Name = "Pending Vendor" Then
            s.Format.Fill.ForeColor.RGB = RGB(128, 100, 162) 'Purple
            ElseIf s.Name = "Scheduled" Then
            s.Format.Fill.ForeColor.RGB = RGB(155, 187, 89) 'Green
            ElseIf s.Name = "Closed" Or s.Name = "Resolved" Then
            s.Format.Fill.ForeColor.RGB = RGB(148, 138, 84) 'Brown
            ElseIf s.Name = "Unassigned" Then
            s.Format.Fill.ForeColor.RGB = RGB(255, 192, 0) 'Yellow
        End If

    'Find the "Grand Total" datapoint in each series and hide it
    For pointIterater = 0 To s.Points.Count - 1
        If s.XValues(pointIterater) = "Grand Total" Then
               s.Points(pointIterater).Interior.ColorIndex = xlNone
        End If
        teamTotal(pointIterator) = teamTotal(pointIterator) + s.Values(pointIterator)
    Next pointIterater
Next seriesIterator

"Subscript out of range" When Using Array

1 Answers