CODEWITHSUNDEEP
excelvbachartsvbe
Kram Kramer
Kram Kramer

Reputation: 113

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?

Upvotes: 1

Views: 83

Answers (2)

jkpieterse
jkpieterse

Reputation: 3006

The tlbinf32.dll allows listing all properties of an object, but as its name says it only works in 32 bit office (I think). See: https://jkp-ads.com/articles/objectlister.aspx

Upvotes: 1

FunThomas
FunThomas

Reputation: 29586

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

Upvotes: 2

Related Questions