CODEWITHSUNDEEP
sqlexcelvbagraph
Kevin P
Kevin P

Reputation: 35

Pause Excel graph while data is updating

I have lots of data that is pulled by a press of a button from SQL query through connections in Excel. Then I compose few simple calculations to get results. I also have 4 graphs that are based off that data.

I run into an issue where the code will take few minutes to execute. I believe it is due to the fact that while data is being updated, graphs are updated as well. I ran into that conclusion after removing graphs, it was significantly faster.

Is there a way to speed up this process a bit? Can I pause the graphing and resume it after all the data have been updated?

Thank you!

Upvotes: 2

Views: 967

Answers (3)

Nate May
Nate May

Reputation: 4072

Have you considered offsetting the chart area in vba and switching back at the end of the code?

Here's how you can Select the Chart Area in VBA.

For example. If you want to chart data in Range A1:A10 then you can do the following

Charts(1).SetSourceData Source:=Sheets(1).Range("B1:B10")

your logic

Charts(1).SetSourceData Source:=Sheets(1).Range("A1:A10")

This "Aims" the chart at a different range so that it doesn't try to recompute the graph after each cell change. Once your logic is complete, then "Aim" it back at the correct range.

Upvotes: 5

Kevin P
Kevin P

Reputation: 35

The way I did it was to clear all series in all the graphs at the beginning. Formatting stays the same on each graph.I created a sub that I call in the main program from where I am sending each chart variable name declared as ListObject and assigned a chart name.

Private Sub DeleteOldData(CurrentChart As ChartObject)
Dim s As Long

With CurrentChart
    For s = .Chart.SeriesCollection.Count To 1 Step -1
        .Chart.SeriesCollection(s).Delete
    Next s
End With
End Sub

Then, I update the data. After, I re-assign all the series back the graph through another sub.

Private Sub AddData(CurrentChart As ChartObject, table1 As ListObject, table2 As ListObject, series1 As String, series2 As String)

CurrentChart.Chart.SeriesCollection.NewSeries
CurrentChart.Chart.FullSeriesCollection(1).Name = series1
CurrentChart.Chart.FullSeriesCollection(1).Values = table1.ListColumns(2).DataBodyRange
CurrentChart.Chart.FullSeriesCollection(1).XValues = table1.ListColumns(1).DataBodyRange
CurrentChart.Chart.SeriesCollection.NewSeries
CurrentChart.Chart.FullSeriesCollection(2).Name = series2
CurrentChart.Chart.FullSeriesCollection(2).Values = table2.ListColumns(2).DataBodyRange
CurrentChart.Chart.FullSeriesCollection(2).XValues = table2.ListColumns(1).DataBodyRange
CurrentChart.Chart.FullSeriesCollection(2).Select

DoEvents
End Sub

table1 and table2 are the tables from where I am pulling the data. I had to add 2 graphs on 1 chart from 2 different tables. Series1 and Series2 are the names for the series. I declared them before calling each sub.

Upvotes: 1

Uri Goren
Uri Goren

Reputation: 13700

I suggest using

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

And perhaps also

Application.EnableEvents = False

Before the query, And reversing it after the query

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

Upvotes: 1

Related Questions