CODEWITHSUNDEEP
excelvbacharts
YeOldHinnerk
YeOldHinnerk

Reputation: 89

Can't find chart object despite having inserted one

I'm just starting to work with charts in VBA. The manual seemed straight forward: All Charts are contained in Workbook.Charts and/or (well, not THAT clear for a beginner, as it turns out) Worksheets(x).ChartObjects. Only, I have now a graph in my workbook that I can not find in either collection.

Where is the object hiding?

I'm running a Little Marco, which should set the Colors of all Graphs according to my definitions. There are two Graphs in the workbook, on different spreadsheets. The second one (the one that is "vanished") is of type waterfall.

  1. ?ThisWorkbook.Charts = 0.
  2. ?ThisWorkbook.Worksheets("Graph").ChartObjects.Count = 1
  3. ?ThisWorkbook.Worksheets("Charts").ChartObjects.Count = 0

Well the first two results are fine, the third should have been a 1 as well.

I doubt any code would be usefull - since I'm may just be looking in the wrong Corners?

What is a safe way, to cycle through all Charts in a workbook? Cycling over each worksheet's Charts in chartobjects doesn'T do the Job.

Why can I not find the Chart in the worksheet? Is this some specialty of waterfall?

I'm sorry if this may seem like a very basic question, I'm just starting into Charts.

Edit: Added the Loop:

Public Sub hrFormatAllCharts()
    Debug.Print ("hrFormatAllCharts: Enter")
    Dim ws  As Worksheet
    Dim cht As ChartObject
    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ("Charts in worksheet " & ws.Name & " : " & ws.ChartObjects.Count & ".")
        For Each cht In ws.ChartObjects
            Call hrFormatChart(cht.Chart)
        Next cht
    Next ws
    Debug.Print ("hrFormatAllCharts: Exit")
End Sub

Bonus Point Question: Since I can see the Chart, is there a way through the GUI to ask the object "Where do you live?"?

PS: I verified that this issue is related to the Chart type (waterfall). I removed the waterfall and used another - now that other Chart is in the above collection. Deleted that one and put in again a waterfall: Can't find it in ChartObjects...

PPS: I found the following question in stack Overflow and now I wonder if it relates...: Error copying waterfall charts with Excel macro

Upvotes: 1

Views: 1161

Answers (1)

Mateusz_G
Mateusz_G

Reputation: 36

Hey please find my proposition of looping through charts.

Sub LoopThroughCharts()

Dim charts As ChartObjects
Dim chart As ChartObject
Dim sheetIterator As Integer
Dim loopSheet As Worksheet

For sheetIterator = 1 To ThisWorkbook.Worksheets.Count()
    Set loopSheet = ThisWorkbook.Worksheets(sheetIterator)
    Debug.Print loopSheet.Name

    Set charts = loopSheet.ChartObjects

    For Each chart In charts
        Debug.Print chart.Name
    Next chart


Next sheetIterator

End Sub

Upvotes: 1

Related Questions