+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    03-03-2009
    Location
    California
    MS-Off Ver
    2000 & 2003
    Posts
    3

    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:

    Please Login or Register  to view this content.
    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.
    Last edited by unhappy_camper; 03-10-2009 at 01:30 PM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,244

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

    This will make the lines thin.

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    03-03-2009
    Location
    California
    MS-Off Ver
    2000 & 2003
    Posts
    3

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

    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.
    Last edited by unhappy_camper; 03-04-2009 at 01:07 PM.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,244

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

    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.

  5. #5
    Registered User
    Join Date
    03-03-2009
    Location
    California
    MS-Off Ver
    2000 & 2003
    Posts
    3

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

    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.
    Last edited by unhappy_camper; 03-05-2009 at 02:05 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1