vba - How to extract all properties of an excel chart? - Stack Overflow

admin管理员组

文章数量:1287491

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?

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?

• excel
• vba
• charts
• vbe

Share Improve this question Follow edited Feb 24 at 15:51 Kram Kramer asked Feb 24 at 15:03 Kram KramerKram Kramer 11311 silver badge66 bronze badges 5

• 3 Can you share the end goal? – BigBen Commented Feb 24 at 15:07
• 2 You can display the locals window in the VBE with View->Locals Window (Alt+V, S). However, you can't access it's content using VBA, and there is no thing like "reflection" in VBA that enables you to list all properties. – FunThomas Commented Feb 24 at 15:12
• I know John Walkenbach created something that would give you details of a chart when you clicked on various parts of the chart. I think it was Chart Tools 2.0 that Jon Peltier now hosts on his site: peltiertech/jwalk-chart-tools-2 I might be wrong though. :) – Darren Bartrup-Cook Commented Feb 24 at 15:16
• 1 Add the line Dim ws: Set ws = ActiveSheet before End Sub , add a breakpoint on End Sub then run. Expand ws->shapes->item1->chart – CDP1802 Commented Feb 24 at 15:20
• @KramKramer when replying to people they'll definitely get notifications if you add an @ before their name. You also get an intellisense type drop-down. – Darren Bartrup-Cook Commented Feb 24 at 15:29

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

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.

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/articles/objectlister.aspx

本文标签： vbaHow to extract all properties of an excel chartStack Overflow