Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to format Series with VBA?
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).MarkerBackgroundColor Index = xlNone objChart.SeriesCollection(k).MarkerForegroundColor Index = 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).MarkerBackgroundColor Index = sm objChart.SeriesCollection(k).MarkerForegroundColor Index = 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).Worksheet s _ (sn).Range("C47:C" & lr) objChart.SeriesCollection(k).ErrorBars.Border.Colo rIndex = eb objChart.SeriesCollection(k).ErrorBars.Border.Weig ht = xlThin objChart.SeriesCollection(k).ErrorBars.Border.Line Style = 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to format Series with VBA?
Hi,
deko wrote: 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).MarkerBackgroundColor Index = xlNone objChart.SeriesCollection(k).MarkerForegroundColor Index = 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).MarkerBackgroundColor Index = sm objChart.SeriesCollection(k).MarkerForegroundColor Index = 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).Worksheet s _ (sn).Range("C47:C" & lr) objChart.SeriesCollection(k).ErrorBars.Border.Colo rIndex = eb objChart.SeriesCollection(k).ErrorBars.Border.Weig ht = xlThin objChart.SeriesCollection(k).ErrorBars.Border.Line Style = 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to format Series with VBA?
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... 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).MarkerBackgroundColor Index = xlNone objChart.SeriesCollection(k).MarkerForegroundColor Index = 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).MarkerBackgroundColor Index = sm objChart.SeriesCollection(k).MarkerForegroundColor Index = 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).Worksheet s _ (sn).Range("C47:C" & lr) objChart.SeriesCollection(k).ErrorBars.Border.Colo rIndex = eb objChart.SeriesCollection(k).ErrorBars.Border.Weig ht = xlThin objChart.SeriesCollection(k).ErrorBars.Border.Line Style = 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 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to format Series with VBA?
I'll also embed my comments/responses
deko wrote: 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?) 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. 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... 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. 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).MarkerBackgroundColor Index = xlNone objChart.SeriesCollection(k).MarkerForegroundColor Index = 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).MarkerBackgroundColor Index = sm objChart.SeriesCollection(k).MarkerForegroundColor Index = 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).Worksheet s _ (sn).Range("C47:C" & lr) objChart.SeriesCollection(k).ErrorBars.Border.Colo rIndex = eb objChart.SeriesCollection(k).ErrorBars.Border.Weig ht = xlThin objChart.SeriesCollection(k).ErrorBars.Border.Line Style = 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 Good suggestion. But then I can't use 'For Each ser in objChart.SeriesCollection' You are correct. 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. 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? 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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format data series (Chart) | New Users to Excel | |||
How many data series can i format | Charts and Charting in Excel | |||
Default Series format | Charts and Charting in Excel | |||
1 Chart - Different series format | Charts and Charting in Excel | |||
format data series | Charts and Charting in Excel |