UtterAccess Discussion Forums > Error 9: Subscript out of range

lisa2004

Apr 23 2010, 02:46 PM

I want to run following code to set my chart data label, but I got error message "Subscript out of range", thanks.

CODE

Sub   chartLabel()
    Dim iPts As Integer
    Dim nPts As Integer
    Dim aVals As Variant
    Dim srs As Series
    Dim ws As Worksheet

    For Each srs In Charts(1).SeriesCollection

        With srs
            If .HasDataLabels Then
                nPts = .Points.Count
                aVals = .Values

                For iPts = 1 To nPts
                    If aVals(iPts) = 0 Then
                        ' If the value is blank or 0, then hide the chart labels
                        .Points(iPts).HasDataLabel = False
                    Else
                        ' If there is something great than 0, show all the stuff on the chart
                        .Points(iPts).ApplyDataLabels xlDataLabelsShowLabelAndPercent, True, False, True, False, True, True, True, True

                    End If
                Next

            End If
        End With
    Next
End sub

HiTechCoach

Apr 23 2010, 03:08 PM

QUOTE (lisa2004 @ Apr 23 2010, 02:46 PM)

I want to run following code to set my chart data label, but I got error message "Subscript out of range", thanks.

CODE

Sub   chartLabel()
    Dim iPts As Integer
    Dim nPts As Integer
    Dim aVals As Variant
    Dim srs As Series
    Dim ws As Worksheet

    For Each srs In Charts(1).SeriesCollection

        With srs
            If .HasDataLabels Then
                nPts = .Points.Count
                aVals = .Values

                For iPts = 1 To nPts
                    If aVals(iPts) = 0 Then
                        ' If the value is blank or 0, then hide the chart labels
                        .Points(iPts).HasDataLabel = False
                    Else
                        ' If there is something great than 0, show all the stuff on the chart
                        .Points(iPts).ApplyDataLabels xlDataLabelsShowLabelAndPercent, True, False, True, False, True, True, True, True

                    End If
                Next

            End If
        End With
    Next
End sub

I have not tested this, by my thinking is this:

Try changing:

CODE

For iPts = 1 To nPts

to

CODE

For iPts = 0 To (nPts - 1)

Example: count = 5 then you will have the subscripts of 0,1,2,3,4 since it starts counting from 0 not 1

lisa2004

Apr 28 2010, 02:47 PM

Thanks for the reply. But it still gave me the same error message.

Lisa

ipisors

May 12 2010, 11:48 AM

Lisa,

Subscript Out of Range can mean pretty much any object that can't be found. It would be helpful to post which line your code debugs on. This will provide clues as to what specifically is wrong.

Example, I try to run this same code in an excel workbook, that does have at least one chart in it.

and it debugs on the line

For Each srs In Charts(1).SeriesCollection

and tells me, when I hold my cursor over "srs", that "srs=empty".

It would seem you don't have the chart or charts to which you are referring, in your workbook.

Change it to just say For Each srs In ThisWorkbook.Charts

You don't need to say "charts(1)" because one chart does not have a series collection. Many /all charts MAKE UP the collection itself.

I changed the line as I mentioned above, and it works.