Hi,
I was trying to create a macro that replaces every series in a line
chart with a moving average (other kinds of trendlines could be added
later with a form), easing the creation of a series of smoothed charts
out of raw data far simpler (currently you have to manually make the
moving averages in new ranges and then chart, which takes time if you
have lots of series in lots of charts, or right click in every series
in a chart, configure and add the trendline, then select each of the
original series and hide it, delete the original labels, etc). I'm
having a little trouble with it, but since it nearly does what it is
intended for now and given its sheer usefulness I thought that maybe
with a little tweaking someone could help me finish it...
The macro should create trendlines that have the same format as the
series it replaces. (Alternatively, it could duplicate the chart and
then do what it currently does, in order to preserve the original in
case it fails). I'm having trouble with that, I manage to copy the
series color but I can't do the same with the line style (dashed,
dotted, etc) and weight (it copies something, but the result doesn't
have the exact same weight as the series it replaces). Here's the
code:
Sub ma_chart_seleccionado()
Dim serie As Series
periodos = inputbox()
If Not ActiveChart Is Nothing Then
cantidad_series = ActiveChart.SeriesCollection.Count
If Not cantidad_series = 0 Then
If ActiveChart.ChartType = xlLine Or
ActiveChart.ChartType = xlLineMarkers Or ActiveChart.ChartType =
xlLineMarkersStacked Or ActiveChart.ChartType =
xlLineMarkersStacked100 Or ActiveChart.ChartType = xlLineStacked Or
ActiveChart.ChartType = xlLineStacked100 Then
For Each serie In ActiveChart.SeriesCollection
ActiveChart.Legend.LegendEntries(1).Delete
serie.Smooth = False
color_serie = serie.Border.Color
estilo_linea = serie.Border.LineStyle
ancho_linea = serie.Border.Weight
serie.Border.ColorIndex = xlColorIndexNone
'serie.MarkerSize = 4
serie.MarkerForegroundColorIndex =
xlColorIndexNone
serie.MarkerBackgroundColorIndex =
xlColorIndexNone
If serie.Trendlines.Count = 0 Then
serie.Trendlines.Add
With serie.Trendlines(1)
.Type = xlMovingAvg
.Period = periodos
.Border.LineStyle = estilo_linea
.Border.Weight = ancho_linea
.Border.Color = color_serie
.Name = "MA(" & CStr(periodos) & ") de
" & serie.Name
leyenda_correspondiente =
ActiveChart.Legend.LegendEntries.Count
ActiveChart.Legend.LegendEntries
(leyenda_correspondiente).Font.Color = color_serie
ActiveChart.Legend.Position =
xlLegendPositionCorner
ActiveChart.Legend.IncludeInLayout =
False
End With
End If
Next
End If
End If
End If
End Sub
There are as usual a few drawbacks: adding other series later is not
possible, which makes it hard as hell to add helper series to shade
periods of time or limits or goals or whatever. All in all, so far it
has saved me a lot of time and I'd really like to see this working
properly with the help of a senior vba programmer.
Thanks in advance,
SA
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
