0

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

Improve this question
4
  • 1
    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 Commented Dec 16, 2015 at 15:51
  • "The problematic line of code is highlighted." <-- where? Commented Dec 16, 2015 at 15:52
  • @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. Commented Dec 16, 2015 at 15:56
  • 3
    Two words: Option Explicit. You've declared pointIteratEr but then used pointIteratOr as the index. Commented Dec 16, 2015 at 16:16

1 Answer 1

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Arrays are not zero indexed if you declare them as 1 To x though.
I've never heard of that in 15 years of using VB. Can you provide a reference?
The VBA language spec. It states, in part, "If the <lower-bound> is present, its <constant-expression> provides the lower bound Long data value for the corresponding dimension." Frankly I'm amazed you haven't come across this in 15 years.
95% of my "VB" coding over that time has been done in VB.NET where the lower bound for arrays is always zero. The fact that VBA (or possibly other flavors of VB) allows something other than zero is one of those quirky and esoteric features that is understandably lost in the shuffle.
It's only VB.Net that doesn't allow it. ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.