Thread: VB6 & Excel 2007: Setting Color of Plot Line in Chart

The Situation:
We graph data from data files in a Visual Basic 6 app by creating an Excel application object and having it generate the 2-D line chart. This application was developed about 6 years ago and the engineer who originally wrote it is long gone, so I have inherited it.

It has worked fine until we tried to use it on Excel 2007. We go with a lot of the default chart settings. When it was written, Excel 97 and Excel 2000 were current and it still works fine with Excel 2003. But now with Excel 2007, some of the default settings seem to have changed. For example, the chart title's default color has changed from black to white. I've solved that problem by explicitly setting the color to black, thus overriding the new default.

It's the other change that I can't get a handle on. The plot line itself used to be thin and black, but now it's thicker and blue. Blue isn't so much a problem, but we want to make it thin again so as to not hide the details.

The Visual Studio 6 help files provide no information on working with Excel objects. I have been using the Excel 2003 help files to see what objects are available, but when I try most of what it provides VB6 chokes on it. Here's the latest that I've been able to find and try:

Code:

    With wkbExcel.ActiveChart
      .ChartType = xlXYScatterLinesNoMarkers
      .SetSourceData Source:=wkbExcel.Worksheets(1).Range(strSourceData), _
                     PlotBy:=xlColumns
      GoSub AdjustProgress
      .Location Where:=xlLocationAsNewSheet
      .HasTitle = True
      .Shapes.SelectAll
      sr = Selection.ShapeRange
      sr.Line.ForeColor.RGB = RGB(255, 0, 0)

sr is Dim'd as ShapeRange, as per the help file. It chokes on the Selection.ShapeRange, which was also written precisely as per the help file.

BTW, it's just for test purposes that I'm trying to set the line to red, so that I can see whether the change I'm trying to make does actually appear. Of course I'll change it back to black once I'm there.

This will make the lines thin.

Code:

    Dim objSeries As Series
    Dim wkbExcel As Workbook
    
    Set wkbExcel = ActiveWorkbook
    
    With wkbExcel.ActiveChart
        For Each objSeries In .SeriesCollection
            With objSeries.Format.Line
                .Visible = msoCTrue
                .Weight = 1
                .ForeColor.RGB = RGB(255, 0, 0)
            End With
        Next
    End With

Thank you for the reply. Didn't know that VB6 had the For Each. By the time I was able to line up a class, .NET was all they offered.

Unfortunately, it doesn't seem to like the .Format.Line, saying that the object doesn't support it. The Excel 2003 help lists Series' properties as:

Application Property
ApplyPictToEnd Property
ApplyPictToFront Property
ApplyPictToSides Property
AxisGroup Property
BarShape Property
Border Property
BubbleSizes Property
ChartType Property
Creator Property
ErrorBars Property
Explosion Property
Fill Property
Formula Property
FormulaLocal Property
FormulaR1C1 Property
FormulaR1C1Local Property
Has3DEffect Property
HasDataLabels Property
HasErrorBars Property
HasLeaderLines Property
Interior Property
InvertIfNegative Property
LeaderLines Property
MarkerBackgroundColor Property
MarkerBackgroundColorIndex Property
MarkerForegroundColor Property
MarkerForegroundColorIndex Property
MarkerSize Property
MarkerStyle Property
Name Property
Parent Property
PictureType Property
PictureUnit Property
PlotOrder Property
Shadow Property
Smooth Property
Type Property
Values Property
XValues Property

and its methods as:

ApplyCustomType Method
ApplyDataLabels Method
ClearFormats Method
Copy Method
DataLabels Method
Delete Method
ErrorBar Method
Paste Method
Points Method
Select Method
Trendlines Method

Sorry for the copy-and-paste. Also, the object-model graphic on the Series page shows it containing the Border, Points, and Interior objects.

Is there a better reference on-line than the help files? Also, does this mean that each version of Excel will have a different object model, thus rendering what I do inoperative with the next version? Part of the requirements I'm working to is for this to work with each version of Excel; most machines here have 2003, but the newer ones have 2007.

I do appreciate your help. I usually work in C and C++. I will be so glad when I can put this one to bed.

The chart object is new in xl2007. Some stuff is the same as previous versions but a lot is new, in order to handle the new rendering engine.

If the pc you are testing on has excel 2003 then yes the code will fail.
You need to check the Application version and then use the appropriate code.

Documentation of the new OM in xl2007 for charting is patchy at best. The lack of macro recording also means discovery by doing is not possible.

Finally I'm starting to see the tunnel at the end of the light!

If the version number is 12 or greater (for 2007 and hopefully even after), then I use your code, else we go with the default that works fine up to 2002 (turns out I have v10; will test it on 2003 v11 in about an hour when that machine frees up). The only weird thing was that I specified the plot line to be black (RGB(0,0,0)), but it instead comes out blue. But that's not a problem since the line is thin again and it prints out fine.

Still, it seems weird to me that there's no way to do this prior to Excel 2007.

Also, I'd like to render a back-of-the-hand salute up the side of Microsoft's collective head for their complete redesign of the user interface. It amazed me how deeply and deviously they'd hidden things, such as the version number. Makes the product almost impossible to use. Reminds me of Desmond Morris' "The Human Zoo" in which he listed the rules for a baboon leader to live by, rules which also apply to human leaders. One rule was to change things arbitrarily and unnecessarily so as to inconvenience everybody, just to remind them that you're still there and in power.

Thank you. I couldn't have completed this task without your help.