+ Reply to Thread
Results 1 to 3 of 3

Dynamically changing Border-Color of all series() to "0"

  1. #1
    Registered User
    Join Date
    09-20-2005
    Location
    Mönchengladbach, Germany
    Posts
    24

    Dynamically changing Border-Color of all series() to "0"

    Hi,

    I had to create a script that transfers data out of ascii-files into Excel-Sheets. After transfering I wanted to create a Chart with that data using VBA. So far it worked.

    Now I'm stuck. I tried to change the color of all the lines to black.
    Please Login or Register  to view this content.
    It works, but I have 190 Series in average so it takes about 5 minutes to change the color. I could draw them with permanent marker on my screen faster.

    So, is it possible to speed that up?

    Thank you,

    Simon

  2. #2
    Jon Peltier
    Guest

    Re: Dynamically changing Border-Color of all series() to "0"

    Simon -

    There are lots of tricks to speed up code. One is to put

    Application.ScreenUpdating = False

    before the code and

    Application.ScreenUpdating = True

    after it, especially since Excel handcuffs itself by redrawing the
    entire chart and all of its data points when it changes a single one.
    And I bet 190 series takes a while. What will have a smaller effect is
    to avoid selecting everything before you change it. That means change
    this pair of lines

    NewChart.SeriesCollection(c).Select
    With Selection.Border

    to this

    With NewChart.SeriesCollection(c).Border

    Also, this might be redundant, as it looks like a default to me

    .LineStyle = xlContinuous

    So all in all, you should use code adapted like so:

    Application.ScreenUpdating = False
    For i = 2 To NewChart.SeriesCollection.count 'First DataSeries is "2"
    With NewChart.SeriesCollection(c).Border
    .ColorIndex = 1
    .Weight = xlMedium
    '.LineStyle = xlContinuous
    End With
    Next i
    Application.ScreenUpdating = True

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

    moondark wrote:
    > Hi,
    >
    > I had to create a script that transfers data out of ascii-files into
    > Excel-Sheets. After transfering I wanted to create a Chart with that
    > data using VBA. So far it worked.
    >
    > Now I'm stuck. I tried to change the color of all the lines to black.
    >
    > Code:
    > --------------------
    >
    > For i = 2 To NewChart.SeriesCollection.count 'First DataSeries is "2"
    > NewChart.SeriesCollection(c).Select
    > With Selection.Border
    > .ColorIndex = 1
    > .Weight = xlMedium
    > .LineStyle = xlContinuous
    > End With
    > Next i
    > --------------------
    >
    >
    > It works, but I have 190 Series in average so it takes about 5 minutes
    > to change the color. I could draw them with permanent marker on my
    > screen faster.
    >
    > So, is it possible to speed that up?
    >
    > Thank you,
    >
    > Simon
    >
    >


  3. #3
    Registered User
    Join Date
    09-20-2005
    Location
    Mönchengladbach, Germany
    Posts
    24
    Thanks alot. That's it.
    Time it took before deactivating ScreenUpdate: 5min 47sec
    Time it took after deactivating ScreenUpdate: *tada* 21sec
    Now that is fast.

    Thx,
    Simon

+ 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