How to format Series with VBA?

[deko]

I need to format about 30 series on a number of charts. Sometimes I have
100 charts to loop through, which means 3000 series.

I'm wondering if there's a way to specify a group of series to format at
once rather than looping through each one individually. For example, is
there some way to say:

objChart.Series(3, 30).ColorIndex = 3

so that formatting is applied to series 3 through 30 at once?

Here's what I'm doing now, which seems rather slow:

Do While k < (objChart.SeriesCollection.Count)
k = k + 1
If k > 2 Then
objChart.SeriesCollection(k).Border.ColorIndex = st
objChart.SeriesCollection(k).Border.Weight = xlThin
objChart.SeriesCollection(k).Border.LineStyle = xlNone
objChart.SeriesCollection(k).MarkerBackgroundColorIndex = xlNone
objChart.SeriesCollection(k).MarkerForegroundColorIndex = st
objChart.SeriesCollection(k).MarkerStyle = xlDot
objChart.SeriesCollection(k).Smooth = False
objChart.SeriesCollection(k).MarkerSize = 2
objChart.SeriesCollection(k).Shadow = False
Else
If k = 2 Then
objChart.SeriesCollection(k).Delete
Else
objChart.SeriesCollection(k).Border.ColorIndex = sm
objChart.SeriesCollection(k).Border.Weight = xlThin
objChart.SeriesCollection(k).Border.LineStyle = xlContinuous
objChart.SeriesCollection(k).MarkerBackgroundColorIndex = sm
objChart.SeriesCollection(k).MarkerForegroundColorIndex = sm
objChart.SeriesCollection(k).MarkerStyle = xlDiamond
objChart.SeriesCollection(k).Smooth = False
objChart.SeriesCollection(k).MarkerSize = 3
objChart.SeriesCollection(k).Shadow = False
objChart.SeriesCollection(k).ErrorBar Direction:=xlY,
Include:=xlBoth, _
Type:=xlCustom, Amount:=xlapp.Workbooks _
(strXlsFile).Worksheets(sn).Range("C47:C" & lr), _
MinusValues:=xlapp.Workbooks(strXlsFile).Worksheets _
(sn).Range("C47:C" & lr)
objChart.SeriesCollection(k).ErrorBars.Border.ColorIndex = eb
objChart.SeriesCollection(k).ErrorBars.Border.Weight = xlThin
objChart.SeriesCollection(k).ErrorBars.Border.LineStyle =
xlContinuous
End If
End If
objChart.Axes(xlCategory).TickLabels.NumberFormat = "mm/dd/yyyy hh:mm;@"
objChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
Loop

Thanks in advance.

 

[scattered]

Hi,

I need to format about 30 series on a number of charts. Sometimes I have
100 charts to loop through, which means 3000 series.

I'm wondering if there's a way to specify a group of series to format at
once rather than looping through each one individually. For example, is
there some way to say:

objChart.Series(3, 30).ColorIndex = 3

so that formatting is applied to series 3 through 30 at once?

Use a For ... Next Loop:
For k = 3 to 30
objChart.Series(k).Border.ColorIndex = 3
Next k

Also, if you want to loop over *all* of the series then a For Each loop
is good:

For Each ser in objChart.SeriesCollection
ser.Border.ColorIndex = 3
Next Ser

Here's what I'm doing now, which seems rather slow:

Do While k < (objChart.SeriesCollection.Count)
k = k + 1
If k > 2 Then
objChart.SeriesCollection(k).Border.ColorIndex = st
objChart.SeriesCollection(k).Border.Weight = xlThin
objChart.SeriesCollection(k).Border.LineStyle = xlNone
objChart.SeriesCollection(k).MarkerBackgroundColorIndex = xlNone
objChart.SeriesCollection(k).MarkerForegroundColorIndex = st
objChart.SeriesCollection(k).MarkerStyle = xlDot
objChart.SeriesCollection(k).Smooth = False
objChart.SeriesCollection(k).MarkerSize = 2
objChart.SeriesCollection(k).Shadow = False
Else
If k = 2 Then
objChart.SeriesCollection(k).Delete
Else
objChart.SeriesCollection(k).Border.ColorIndex = sm
objChart.SeriesCollection(k).Border.Weight = xlThin
objChart.SeriesCollection(k).Border.LineStyle = xlContinuous
objChart.SeriesCollection(k).MarkerBackgroundColorIndex = sm
objChart.SeriesCollection(k).MarkerForegroundColorIndex = sm
objChart.SeriesCollection(k).MarkerStyle = xlDiamond
objChart.SeriesCollection(k).Smooth = False
objChart.SeriesCollection(k).MarkerSize = 3
objChart.SeriesCollection(k).Shadow = False
objChart.SeriesCollection(k).ErrorBar Direction:=xlY,
Include:=xlBoth, _
Type:=xlCustom, Amount:=xlapp.Workbooks _
(strXlsFile).Worksheets(sn).Range("C47:C" & lr), _
MinusValues:=xlapp.Workbooks(strXlsFile).Worksheets _
(sn).Range("C47:C" & lr)
objChart.SeriesCollection(k).ErrorBars.Border.ColorIndex = eb
objChart.SeriesCollection(k).ErrorBars.Border.Weight = xlThin
objChart.SeriesCollection(k).ErrorBars.Border.LineStyle =
xlContinuous
End If
End If
objChart.Axes(xlCategory).TickLabels.NumberFormat = "mm/dd/yyyy hh:mm;@"
objChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
Loop

I'm not quite sure what this code does but a few comments:

1) If the first series needs to be handled differently from the others
it is better to handle it by a separate block of code before the loop
rather than with if statements within the loop

2) You can use the With ... End With construction to make it more
readable and efficient:

With objChart.SeriesCollection(k)
.Border.ColorIndex = sm
.Border.Weight = xlThin
(other things go here)...
End With

3)If you got all these properties from the Macro Recorder then you can
probably delete many of them. There is no need to set a property to its
default (unless your code has previously changed it). For example, my
guess is that you are never changing the Smooth property, so those
lines are not needed

4)It wouldn't hurt to read a VBA book if you haven't done so yet. There
are many good ones to choose from. You can't go wrong with one of
Walkenbach's

Thanks in advance.

Hope this helps.

-John Coleman

 

[deko]

Hi and thanks for the detailed response!

My comments embedded below.

Use a For ... Next Loop:
For k = 3 to 30
objChart.Series(k).Border.ColorIndex = 3
Next k

Yes, but I am already iterating with a Do ... While loop (what's the diff?)

Also, if you want to loop over *all* of the series then a For Each loop
is good:

For Each ser in objChart.SeriesCollection
ser.Border.ColorIndex = 3
Next Ser

Hmmm. That's more elegant. But still the iteration...

I'm not quite sure what this code does but a few comments:

1) If the first series needs to be handled differently from the others
it is better to handle it by a separate block of code before the loop
rather than with if statements within the loop

Good suggestion. But then I can't use 'For Each ser in
objChart.SeriesCollection'

2) You can use the With ... End With construction to make it more
readable and efficient:

Yes, but since I am using automation, the best practice is to avoid 'With' -
there's a KB article out there somewhere about Excel not quitting when you
don't use the full path to the object. So what you see was a conscious
decision.

3)If you got all these properties from the Macro Recorder then you can
probably delete many of them. There is no need to set a property to its
default (unless your code has previously changed it). For example, my
guess is that you are never changing the Smooth property, so those
lines are not needed

I suppose I could do without 'Smooth = False' ... but my guess is I had
included it there for some reason I can't remember.

In any case, the answer to my original question appears to be "no" - there
is no way to apply formatting to a "batch" of series. I have to iterate.

 

[scattered]

I'll also embed my comments/responses

Hi and thanks for the detailed response!

My comments embedded below.


Yes, but I am already iterating with a Do ... While loop (what's the diff?)

Readability (which yields a higher chance of being correct in the first
place and an easier task if you need to modify the code later). It is
also possible (although I am not sure) that VBA can handle For loops
more efficiently than While loops by doing something like keeping the
loop index in a register.

Hmmm. That's more elegant. But still the iteration...

Not only more elegant, its more efficient. in the above fragment ser is
an object variable of type series; using such variables can make code
run noticably quicker if you are doing a lot of processing. Another
advantage is that the VBA editor can help you if you use such things:
if you just type "ser." a drop down list will give you the
properties/methods you can use whereas if you type
"objChart.Series(k)." nothing happens and you might need to run to the
online help to proceed. The only drawback of the For Each is that you
can't iterate over just some of the series.

Good suggestion. But then I can't use 'For Each ser in
objChart.SeriesCollection'

You are correct.

Yes, but since I am using automation, the best practice is to avoid 'With' -
there's a KB article out there somewhere about Excel not quitting when you
don't use the full path to the object. So what you see was a conscious
decision.

Interesting, I wasn't aware of that. It is possible that my suggestion
above of using series variables might be vulnerable to the same bug.
This sounds like a pretty serious bug. Has Microsoft fixed it?

I suppose I could do without 'Smooth = False' ... but my guess is I had
included it there for some reason I can't remember.

In any case, the answer to my original question appears to be "no" - there
is no way to apply formatting to a "batch" of series. I have to iterate.

I'm pretty sure that's right. If you find yourself needing to do this
sort of thing often you can of course encapsulate it in a procedure and
in effect extend VBA a bit.

Have a good day.

-John Coleman