excelvbachartsvbe

How to extract all properties of an excel chart?


Please run the following code for testing needs.

    Public Sub Macro1()
    
    'Delete all charts
    For i = ActiveSheet.Shapes.Count To 1 Step -1
        If ActiveSheet.Shapes(i).Type = msoChart Then
            ActiveSheet.Shapes(i).Delete
        End If
    Next i
    
    'Add a chart.
    With ActiveSheet.ChartObjects.Add(Left:=10, Top:=10, Width:=400, Height:=200)
        .Name = "myChart"
    End With
    
    'Add a serie.
    With ActiveSheet.ChartObjects("myChart").Chart.SeriesCollection.NewSeries
        .ChartType = xlLine
        .XValues = Array(10, 20, 30, 40, 50)
        .Values = Array(1, 2, 3, 4, 5)
    End With
    
    'Apply a new chart style template
    ActiveSheet.ChartObjects("myChart").Chart.ClearToMatchStyle
    ActiveSheet.ChartObjects("myChart").Chart.ChartStyle = 233
    
    End Sub

I want to extract all properties of above chart.

The following link provides a good answer but I dont know how to use VBE Locals window.

How to list properties of a chart object in VBA?


Solution

  • To display something in the locals window, you need to have a local variable pointing to whatever you are interested in.

    Use a code like this (maybe you have to adapt it slightly)

    Sub showChartInfo()
        Dim ws As Worksheet, co As ChartObject, ch As Chart
        
        Set ws = ThisWorkbook.Sheets(1)
        Set co = ws.ChartObjects(1)
        Set ch = co.Chart
    End Sub
    

    Set a breakpoint on the End Sub-line and let the code run.

    enter image description here