Looping through embedded charts in a workbook in an order according to their location (e.g. left-right & up-down order)

Question

I'm trying to loop through charts in a specific worksheet and then move them to a new chart sheet at the end of all the sheets with the following code:

Dim ws As Worksheet, co As ChartObject, c As Chart

Set ws = ThisWorkbook.Sheets("nameofSheet")
ws.Activate

For Each co In ws.ChartObjects
    Set c = co.Chart
    'do stuff with c
Next co

The problem is, this follows the order in which they were created. I built my workbook by slowly adding more "features" here and there over time, so it doesn't make sense to process the charts in the order they were created.

Is there a way to loop through charts on a worksheet according to their location in the sheet? For example, left to right then up to down. I at least need some sort of known order so that I can process the charts properly.

If there isn't one, will simply changing the chart names individually to "Chart 1", "Chart 2", ..., "Chart n" make the above code I used work?

Answer 1

A solution to your need would be to run through the list and build an array with the content of co.BottomRightCell.Address;

If your graphs overlap and start on same cell, you also have solution to use

 co.Top 
 co.Left

Then order this list, e.g. column first, then row, and you have your "position-ordered" list.

My solution - not optimal, because I don't know how to make hashmap in vba:

Sub macro()
    Dim ws As Worksheet, co As ChartObject, c As Chart
    Dim arr As Object
    Set arr = CreateObject("System.Collections.ArrayList")
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Activate

    For Each co In ws.ChartObjects
        ' Initialise the ArrayList, for instance by taking values from a range:
        arr.Add co.Top + 10000 * co.Left
    Next co

    arr.Sort
    ' Optionally reverse the order
    'arr.Reverse

    For Each x In arr
        For Each co In ws.ChartObjects
            If (co.Top + 10000 * co.Left = x) Then
                'MsgBox x
                Set c = co.Chart
                ' do stuff with c
                co.Select
                co.Activate
            End If
        Next co
    Next x
End Sub