Exact Object Model for Chart Formatting in VBA

WernerGg

WernerGg

New Member
Joined
Oct 19, 2010
Messages
43
I have to do with Excel 2007 xy scatter diagrams containing many data series, some of which shall have equal formatting. Actually these charts are some sort of technical drawings consisting of polygons, points, data labels and dimensioning arrows. All of them shall dynamically update from calculated table cells. The formatting for each part shall be defined once manually and then copied to all similar parts.

Unfortunately it is not possible to copy the formatting of a data series to another series. Copy - Paste Special Format is not usable for that. Hence I am writing a VBA program. It deals with Series, Point, DataLabel, ChartFormat, FillFormat, LineFormat, Color, Border, Interior, Font, etc.

The object model for formatting is poor for my opinion. There are many redundancies (from elder excel versions i suppose), call sequence dependencies, attribute interdependencies and so on. The worst thing is that the object models semantic is unclear. A simple question like "what is a line" is incredibly difficult to answer. And the macro recorder does not record those formatting operations. From an OO point of view essential operations are missing. Things like "isequal", "clone/copy", "deep copy" for example.

The MS documentation in VBA object browser, Excel Help and MSN is not sufficient.

Can somebody point me to a good documentation of the model? I read a lot meanwhile. I suppose, Jon Peltier and others in this forum could easily tell me what to read or even have the VBA-code I need.

Thanks,
Werner
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Is nobody interested in the question "what is a line?" or does nobody know it? Or does everybody know it apart from me? :)
 
Upvote 0
OK, everybody apart from me knows what a line is. I still struggle with it.

I am so far:
g5igei1lgc
:)confused: insert image does not work, see the following link: http://www.box.net/shared/g5igei1lgc)




The data series in the left diagram are the sources. They are formatted manually through the Excel 2007 GUI. The series in the right diagram are created by VBA from them, data sources are manipulated thereby. Then the formatting of each left series is copied to the right series. As you can see, it works almost but not:
  • the thickness of marker lines
  • the font underline in data labels
  • other minor errors in data label formatting
The code which does the "copy format" for the series (not the data labels) is currently:
Code: 
Private Sub Copy_Series_Formats(DSeries As Series, newDSeries As Series)
' Copy formatting features from DSeries to newDSeries
'
' Note: It's stupid. Setting all formatting features properly is nearly impossible.
'       Horrible object model. Redundant attributes (elder and newer excel versions?).
'       Sequence dependencies.
'       Very bad documentation.
' Note: This was tested with xy-Scatter diagrams/lines only. See '[Test KSExcel.xlsm]!Diag DSeries'
' Note: Works fine apart from:
' - Markerline format width (Markierungslinienformat.Breite)
'
' Gg, 19.10.2010
 
    If EKS_Debuglevel > CKS_DB_NIX Then Stop
    Dim aFillFormat As FillFormat  ' <== unused. helps nothing
    Dim aLineFormat As LineFormat
 
    With newDSeries
        ' Reset
        .ClearFormats
 
        ' Format the series -------------------------------------------
        ' Line
        With .Border
            ' Line Style
            .LineStyle = DSeries.Border.LineStyle
            ' Line Color
            .Color = DSeries.Border.Color
            ' Line Weight
            .Weight = DSeries.Border.Weight
        End With
        ' Line formatting for the line or shape border
        '   Series.Format -> ChartFormat
        '   ChartFormat.Line -> LineFormat
        With .Format.Line
            Set aLineFormat = DSeries.Format.Line
            ' Line
            .Visible = aLineFormat.Visible
            If aLineFormat.Style <> msoLineStyleMixed Then
                .Style = aLineFormat.Style
                .Weight = aLineFormat.Weight        ' changes the marker border as well
                .DashStyle = aLineFormat.DashStyle  ' changes marker border as well
            End If
            ' Arrows
            If aLineFormat.BeginArrowheadStyle <> msoArrowheadStyleMixed Then
                .BeginArrowheadStyle = aLineFormat.BeginArrowheadStyle
                .BeginArrowheadLength = aLineFormat.BeginArrowheadLength
                .BeginArrowheadWidth = aLineFormat.BeginArrowheadWidth
            End If
            If aLineFormat.EndArrowheadStyle <> msoArrowheadStyleMixed Then
                .EndArrowheadStyle = aLineFormat.EndArrowheadStyle
                .EndArrowheadLength = aLineFormat.EndArrowheadLength
                .EndArrowheadWidth = aLineFormat.EndArrowheadWidth
            End If
        End With
        ' Markers
        .MarkerBackgroundColor = DSeries.MarkerBackgroundColor
        .MarkerForegroundColor = DSeries.MarkerForegroundColor
        .MarkerSize = DSeries.MarkerSize
        .MarkerStyle = DSeries.MarkerStyle
 
        ' Format the points -------------------------------------------
        Dim pt As Point, i As Long
        For i = 1 To .Points.Count
            If i > DSeries.Points.Count Then Exit For
            Set pt = DSeries.Points(i)
            With .Points(i)
                '.Select
                ' line formatting for the line or shape border
                '   Points.Format -> ChartFormat
                '   ChartFormat.Line -> LineFormat
                With .Format.Line
                    Set aLineFormat = pt.Format.Line
                    ' Line
                    .Visible = aLineFormat.Visible
                    If aLineFormat.Style <> msoLineStyleMixed Then
                        .Style = aLineFormat.Style
                        .Weight = aLineFormat.Weight
                        .DashStyle = aLineFormat.DashStyle
                    End If
                    ' Arrows
                    If aLineFormat.BeginArrowheadStyle <> msoArrowheadStyleMixed Then
                        .BeginArrowheadStyle = aLineFormat.BeginArrowheadStyle
                        .BeginArrowheadLength = aLineFormat.BeginArrowheadLength
                        .BeginArrowheadWidth = aLineFormat.BeginArrowheadWidth
                    End If
                    If aLineFormat.EndArrowheadStyle <> msoArrowheadStyleMixed Then
                        .EndArrowheadStyle = aLineFormat.EndArrowheadStyle
                        .EndArrowheadLength = aLineFormat.EndArrowheadLength
                        .EndArrowheadWidth = aLineFormat.EndArrowheadWidth
                    End If
                End With
                ' Marker
                .MarkerBackgroundColor = pt.MarkerBackgroundColor
                .MarkerForegroundColor = pt.MarkerForegroundColor
                .MarkerSize = pt.MarkerSize
                .MarkerStyle = pt.MarkerStyle
            End With ' new Point
        Next i ' next Point in new data series
    End With ' new data series
End Sub
 
Upvote 0
Werner

Why not upload a file?

That might be a little easier for people to work with than an image.:)

We'd also be able to see how your code works 'in situ' and perhaps somebody will spot something that's been overlooked.:)
 
Upvote 0
Norie said:
Werner

Why not upload a file?

That might be a little easier for people to work with than an image.:)

We'd also be able to see how your code works 'in situ' and perhaps somebody will spot something that's been overlooked.:)
Click to expand...

It's a big program that uses some add-ins as well. It would be a lot of work to strip it down to the line-formatting-theme. The essential code is above. Someone who knows about the problem can help me from viewing at the code or will provide his own code.

The question is: How must a VBA look like that does the same as a user formatting a series and its individual points through the "Format Dataseries" and "Format Datapoint" dialogs.

Actually - as i wrote at the beginning - i need no code but the exact semantics of a series and its points: "What is a line?"
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
VBA Email Formatting - Charts and Tables
Replies
0
Views
923
oddzac
O
M
  • Solved
Trouble deleting only freeform shapes in excel chart using VBA and not the chart.
Replies
5
Views
635
NdNoviceHlp
NdNoviceHlp
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
177
dilshod_k
D
B
  • Question
Excel VBA inconsistent errors when trying to copy and paste objects from Excel to Word
2
Replies
18
Views
33K
Jon Peltier
J
L
  • Question
Multiple Chart objects on one sheet
Replies
1
Views
900
starl
starl

Forum statistics

Threads
1,216,045
Messages
6,128,486
Members
449,455
Latest member
jesski

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top