Efficient Chart Macro

[tschng]

I am using the below procedure (copy from Record Macro) to generate 1
graphs, each graph has 4 rows X 50 columns, by supplying ranges o
data.

In my opinion, the code is not very efficient because it take quite
wait to plot and during the process of plotting a chart, it redraws th
chart many times.

Is there any codes that I can be omitted or rearranged to make it plo
faster?

Appreciate your help.


Sub PlotProgressCurve(pSect As String, pRangeDate As String, pRangeN
As String)
Dim tRangeDate, tRangeNo As String

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(pRangeNo)
PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!" & pRangeDate
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!" & pRangeDate
ActiveChart.SeriesCollection(3).XValues = "=Sheet1!" & pRangeDate
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=pSect
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Drawing Progress Curve of "
pSect
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "No. o
Drawings"
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlTop
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = False
ActiveChart.DataTable.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 4
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 5
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.Orientation = xlUpward
End With
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlDot
End With
ActiveChart.PlotArea.Select
Selection.Top = 32
Selection.Height = 405
ActiveChart.Legend.Select
Selection.Left = 50 '74
Selection.Top = 50 '54
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
Selection.Shadow = False
Selection.Interior.ColorIndex = xlAutomatic
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

With ActiveChart.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = "&D"
.LeftMargin = Application.InchesToPoints(0.4)
.RightMargin = Application.InchesToPoints(0.4)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.3)
.ChartSize = xlFullPage
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.BlackAndWhite = False
.Zoom = 100
End With
End Sub

 

[Tushar Mehta]

I haven't gone through the unformatted code, but one suggestion that
comes to mind is to include a Application.ScreenUpdating=False
statement at the top of the code and a complementary =True at the end.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I am using the below procedure (copy from Record Macro) to generate 12
graphs, each graph has 4 rows X 50 columns, by supplying ranges of
data.

In my opinion, the code is not very efficient because it take quite a
wait to plot and during the process of plotting a chart, it redraws the
chart many times.

Is there any codes that I can be omitted or rearranged to make it plot
faster?

Appreciate your help.


Sub PlotProgressCurve(pSect As String, pRangeDate As String, pRangeNo
As String)
Dim tRangeDate, tRangeNo As String

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(pRangeNo),
PlotBy _

{snip of unformatted code}

 

[Toby Erkson]

Yup. This really does speed things up!

--
Toby Erkson
Oregon, USA
Excel 2002 in Windows XP

I haven't gone through the unformatted code, but one suggestion that
comes to mind is to include a Application.ScreenUpdating=False
statement at the top of the code and a complementary =True at the end.

....

 

[Jon Peltier]

Toby -

You also have a number of constructions like this:

ActiveChart.Legend.Select
Selection.Position = xlTop

You generally don't need to waste time selecting an object before
changing it, so the above can be shortened to this:

ActiveChart.Legend.Position = xlTop

Lines like this merely reset the already default property to the
default, so you could ditch them:

.ColorIndex = xlAutomatic
.Background = xlAutomatic

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Toby Erkson]

I know (from reading your web pages) but I think the OP tschng didn't ;-)

Much of the 'stuff' in the WITH...END WITH statements can be removed because
they are default values:
With Selection.Font
Name = "Arial"
FontStyle = "Regular"
Size = 8
Strikethrough = False
Superscript = False
Subscript = False
OutlineFont = False
Shadow = False
Underline = xlUnderlineStyleNone
ColorIndex = xlAutomatic
Background = xlAutomatic
End With

....would become...
With Selection.Font
Name = "Arial"
FontStyle = "Regular"
Size = 8
End With

....and that's assuming Arial isn't the default font otherwise the whole
WITH...END WITH can be removed; I'm making the assumption that the Font info
is not needed but being the newb that I am I could be wrong as some processes
seem to require this stuff. I'm sure an expert here can expound on this.
Naturally, I comment stuff out and test it before I actually delete it because
I never know...