none
how can I iterate through an existing collection in an Office application? RRS feed

  • Question

  • I'm trying to figure out how to iterate through an object model collection of an Office application, in this specific case, the SeriesCollection of an excel 2010 chart.

    The following code selects the worksheet the chart is in and the chart itself:

        Sheets("stackedBarChart").Select
        Sheets("stackedBarChart").ChartObjects("chart 1").Select

    Now I want to iterate through the chart's SeriesCollection collection.

    I created a Collection variable and a Variant variable (for the For Each construct to iterate through the collection):

        Dim myCollection As Collection
        Dim myVariant As Variant

    I think the first thing I need to do is store/bind a copy of SeriesCollection in myCollection, but I'm not sure how to do it.  I tried the following statement:

        myCollection = Sheets("stackedBarChart").ChartObjects("chart 1").Chart.SeriesCollection(1)

    but it's throwing the following error: "Compile error: Argument not optional"

    Assuming that I can store/bind SeriesCollection to myCollection, then I assume I should be able to do something like this to return the values of a selected property:

        For Each myVariant In myCollection
            Debug.Print .Format.Fill.ForeColor.RGB
        Next myVariant

    Am I heading in the right direction on this?  Thanks for any assistance.

    Christian Bahnsen

    Wednesday, July 6, 2016 4:52 PM

Answers

  • There's no need to use a variable of type Collection here, nor do you need to select the worksheet or the chart. You can use code like this:

    Sub Test()
        Dim c As Chart
        Dim s As Series
        Set c = Worksheets("stackedBarChart").ChartObjects("Chart 1").Chart
        For Each s In c.SeriesCollection
            Debug.Print s.Format.Fill.ForeColor.RGB
        Next s
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, July 6, 2016 6:54 PM

All replies

  • There's no need to use a variable of type Collection here, nor do you need to select the worksheet or the chart. You can use code like this:

    Sub Test()
        Dim c As Chart
        Dim s As Series
        Set c = Worksheets("stackedBarChart").ChartObjects("Chart 1").Chart
        For Each s In c.SeriesCollection
            Debug.Print s.Format.Fill.ForeColor.RGB
        Next s
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, July 6, 2016 6:54 PM
  • Hans,

    Thanks!  Knowing which objects to use/reference is the rub.  I see you don't have to start at the topmost layer; rather you zeroed in on the Chart class and the Series/SeriesCollection sub-class via the SeriesCollection method.

    This helped a lot.

    Christian

    Thursday, July 7, 2016 6:08 PM