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

[moondark]

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.

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

[Jon Peltier]

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
>
>

[moondark]

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