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.

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
------------------------------------------------------------------------
moondark's Profile: http://www.excelforum.com/member.php...o&userid=27390
View this thread: http://www.excelforum.com/showthread...hreadid=469118

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

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


--
moondark
------------------------------------------------------------------------
moondark's Profile: http://www.excelforum.com/member.php...o&userid=27390
View this thread: http://www.excelforum.com/showthread...hreadid=469118