Charting Defaults

[Dave Marden]

I created a macro for creating 70 charts and was
wondering how I would know what lines are actually
default values and which are not. I would like to reduce
the number of lines of code for this application.

The following is the code:

Sub CreateGraph()

'Application.ScreenUpdating = False

Dim ChartNames As Variant
Dim SeriesRanges As Variant
Dim SeriesCollection As Variant
Dim AlarmLevels As Variant
'First Number in Range is First Column(up/down) Next
is Row(side/side)
ChartNames = Range("'DataCollection'!A1503:BR1503")
SeriesRanges = Range("'DataCollection'!A1505:BR1505")
SeriesCollection = Range("'DataCollection'!A1507")
AlarmLevels = Range("'DataCollection'!A1510:BR1510")
For i = 1 To 70
Sheets("DataCollection").Select
Charts.Add
With ActiveChart
.SetSourceData Range(SeriesRanges(1, i)),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Range
(SeriesCollection)
.SeriesCollection(1).Name = ChartNames(1, i)
.Location Where:=xlLocationAsNewSheet,
Name:=ChartNames(1, i)
.HasTitle = True
If AlarmLevels(1, i) > 0 Then
.Axes(xlCategory, xlPrimary).HasTitle =
True
.Axes(xlCategory,
xlPrimary).AxisTitle.Characters.Text = "Alarm Level is "
& AlarmLevels(1, i)
.Axes(xlCategory).AxisTitle.Font.Name
= "Arial"
.Axes
(xlCategory).AxisTitle.Font.FontStyle = "Regular"
.Axes(xlCategory).AxisTitle.Font.Size = 8
.Axes
(xlCategory).AxisTitle.Font.Strikethrough = False
.Axes
(xlCategory).AxisTitle.Font.Superscript = False
.Axes
(xlCategory).AxisTitle.Font.Subscript = False
.Axes
(xlCategory).AxisTitle.Font.OutlineFont = False
.Axes(xlCategory).AxisTitle.Font.Shadow =
False
.Axes
(xlCategory).AxisTitle.Font.Underline =
xlUnderlineStyleNone
.Axes
(xlCategory).AxisTitle.Font.ColorIndex = 3
.Axes
(xlCategory).AxisTitle.Font.Background = xlAutomatic
End If
.Axes(xlCategory).TickLabels.Alignment =
xlCenter
.Axes(xlCategory).TickLabels.Offset = 100
.Axes(xlCategory).TickLabels.ReadingOrder =
xlContext
.Axes(xlCategory).TickLabels.Orientation = 45
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue,
xlPrimary).AxisTitle.Characters.Text = "Average Daily
Amperage"
.ChartTitle.Characters.Text = ChartNames(1, i)
'.ChartTitle.AutoScaleFont = True
.ChartTitle.Font.Size = 20
.ChartTitle.Font.ColorIndex = 5
.ChartTitle.Font.Background = xlAutomatic
.ChartTitle.Font.Bold = True
.ChartType = xlLineMarkers
.PlotArea.Border.ColorIndex = 16
.PlotArea.Border.Weight = xlThin
.PlotArea.Border.LineStyle = xlContinuous
.PlotArea.Fill.OneColorGradient
Style:=msoGradientHorizontal, Variant:=1,
degree:=0.231372549019608
.PlotArea.Fill.Visible = True
.PlotArea.Fill.ForeColor.SchemeColor = 20
.HasDataTable = False
End With
Next i
'Application.ScreenUpdating = True

End Sub


Thanks,
Dave Marden

 

[Jon Peltier]

Dave -

The recorder can pump out lots of gibberish, eh?

The Font.StirkeThrough, .SuperScript, .Subscript, .OutlineFont, .Shadow,
and .Underline can all go. Anything that = xlAutomatic can probably go.
If you define a custom chart type and apply it, most of the other
formatting is probably redundant. HasDataTable is false by default.

My own personal preference when making an embedded chart is to use
Sheet("Whatever").ChartObjects.Add L, T, W, H. Rather than use
SetSourceData, I add each series using SeriesCollection.NewSeries, then
set the .Values, .XValues, and .Name explicitly. I have some details on
my web site:
http://www.geocities.com/jonpeltier/Excel/ChartsHowTo/QuickChartVBA.html

- Jon