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

Answer 2

With a little modification of your initial syntax, you could select the charts in order, and the code will process them in the order you selected them.

Note that the order in which Excel loops through unselected chart objects is according to their Z-Order, from back to front. This is usually the order in which charts were inserted, but you can move charts forward and backward to affect this, and you can rearrange them on the Selection pane.

Here is a simple sheet with six charts. Their titles show the chart object names, Chart 1 through Chart 6 in the order they were created.

Here is the code I have used to rename the charts:

Sub RenameCharts()
  Dim sh As Shape
  Dim i As Long
  For Each sh In Selection.ShapeRange
    i = i + 1
    sh.Name = "CHART_" & Format(i, "00")
    sh.Chart.ChartTitle.Text = sh.Name
  Next
End Sub

I selected the charts in order, first row left to right, then second row, left to right; the first chart is selected by clicking, the second and subsequent charts by holding Ctrl while clicking, so all are selected. When all charts have been selected, I ran the code.

The result is shown below. The charts are named in the order I selected them. Note that I have not affected their Z-Order, just their names.

You can now use the chart object names to process your charts:

For i = 1 To 6 ' or whatever
  With ActiveSheet.ChartObjects("CHART_" & Format(i, "00")).Chart
    ' process the chart
  End With
Next

Alternatively, you can skip the renaming process, select your charts in order as I did to rename them, and process your charts in this order:

For Each sh In Selection.ShapeRange
  With sh.Chart
    ' process the chart
  End With
Next