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

  • Thread starter Thread starter moondark
  • Start date Start date
M

moondark

Hi,

I had to create a script that transfers data out of ascii-files int
Excel-Sheets. After transfering I wanted to create a Chart with tha
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
-------------------


It works, but I have 190 Series in average so it takes about 5 minute
to change the color. I could draw them with permanent marker on m
screen faster. ;)

So, is it possible to speed that up?

Thank you,

Simo
 
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/
_______
 
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,
Simo
 
Back
Top