Make my macro run faster please!

[Steve]

Hi everyone - I've written a macro to update a powerpoint chart with
data from an excel chart, and also update some of the formatting.
However, running it takes ages (well, 30 seconds or so per chart). Are
there any ways to speed the execution of the macro up?

Thanks

 

[Steve Rindsberg]

Hi everyone - I've written a macro to update a powerpoint chart with
data from an excel chart, and also update some of the formatting.
However, running it takes ages (well, 30 seconds or so per chart). Are
there any ways to speed the execution of the macro up?

Take the part that says:



and change it to read:




OK, I'll wipe the silly grin off my face and put it this way: It's
impossible to say what you might be doing wrong or how you might do it
better unless you show us what you're doing.

 

[Steve]

Good point - Forgot you weren't mindreaders for a sec there!

Anyway, here's the code:

''' Some Dims

Dim oGraph As Graph.Chart
etc

''' Grab the active xl chart and active ppt chart

Set oPPTApp = GetObject(, "Powerpoint.Application")
Set oGraph = oPPTApp.ActiveWindow.Selection.ShapeRange(1).OLEFormat.Object

Set appXL = GetObject(, "Excel.Application")
Set XLchart = appXL.ActiveChart

''' Update the data

For x = 1 To appXL.ActiveChart.SeriesCollection.Count
For y = 1 To appXL.ActiveChart.SeriesCollection(x).Points.Count

DataArray = appXL.ActiveChart.SeriesCollection(x).Values
oXValues = appXL.ActiveChart.SeriesCollection(x).XValues
oSeriesName = appXL.ActiveChart.SeriesCollection(x).Name
oXValue = oXValues(y)

oGraph.Application.DataSheet.Cells(1 + x, 1 + y) =
DataArray(y)
oGraph.Application.DataSheet.Cells(1, 1 + y) = oXValue
oGraph.Application.DataSheet.Cells(1 + x, 1) = oSeriesName

Next y
Next x

''' Update the formatting

With oGraph

.ChartType = XLchart.ChartType
With .ChartArea
.AutoScaleFont = XLchart.ChartArea.AutoScaleFont
With .Border
.Color = XLchart.ChartArea.Border.Color
.Weight = XLchart.ChartArea.Border.Weight
.LineStyle = XLchart.ChartArea.Border.LineStyle
End With
With .Fill
.Visible = XLchart.ChartArea.Fill.Visible
If XLchart.ChartArea.Fill.Type = msoFillSolid Then
.Solid
With .BackColor
.SchemeColor =
XLchart.ChartArea.Fill.BackColor.SchemeColor
End With
With .ForeColor
.SchemeColor =
XLchart.ChartArea.Fill.ForeColor.SchemeColor
End With
End If


''' There's actually quite a lot of formatting - i won't bore you with
the details

End With
End With

I expected the formatting section at the end to take some time to run,
but even the data updating section seems to be very slow

Any ideas on why this is greatly appreciated,

Steve

 

[Steve Rindsberg]

I don't see anything that screams "SLOW!SLOW!" at me right off, but I wonder
if shortening the "reference chains" would help. IOW, instead of repeatedly
walking your way down e.g. oGraph.Application.DataSheet.Cells, set a
reference to oGraph.Application.DataSheet and use ThatReference.Cells(x,y)
to get a the data.



--

Steve Rindsberg PPT MVP
PPTLive ( http://www.pptlive.com ) Featured Speaker
PPTools: http://www.pptools.com
PPT FAQ: http://www.pptfaq.com