Accelerate chart display

0
votes

I have 50+ line charts with 12 data series per chart. Each series has 2000 data points. Displaying a chart by setting ActiveSheet.ChartObjects(chart_name).Visible = True takes 2+ seconds at first for each after opening the workbook. Going through all the charts (step by step) for a quick review feels like it's taking forever.

I'm looking for any advanced tips or techniques that can make this displaying process faster.

All i could find in the topic is the link below but I have no clue what it says.

https://social.msdn.microsoft.com/Forums/office/en-US/b7c63f9d-e373-4455-a793-f58707353032/excel-2010-extemely-slow-chart-rendering-vs-2003?forum=exceldev

Search for "partially solved" to find the exact post about their partial solution.

1
excelchartsexcel-2010vba

1 Answers

1
votes

Well I figured out a time saving method. I hope I'm not writing this only to the white sheet. Give me some feedback if you are actually reading this.

As far as I understand whenever excel renders a chart it goes into the memory and when it is set visible again (after being hidden), the process is 10x faster. In my case the first rendering of a chart is 2.7s long, but the second only takes 0.3s. 2+ seconds feels like hell when you got used to fast workflow.

The somehow solution is going through (set visible) all the charts in the begining to load them into the memory. This is much faster then multiplying the number of charts with the measured 2.7s. 50 charts load in 28s and not 135s. The blinking charts can be frustrating but with a simple white shape as a temporary cover that's solved too.

The program:

ActiveSheet.Shapes("COVER").Visible = True
ActiveSheet.Shapes("COVER").TextFrame.Characters.Text = "Loading charts (0/50)"

For counter = 1 To 50

    chart_name = Sheets("Data").Cells(counter , 1)
    ActiveSheet.ChartObjects(chart_name).Visible = True
    DoEvents
    ActiveSheet.ChartObjects(chart_name).Visible = False
    ActiveSheet.Shapes("COVER").TextFrame.Characters.Text = "Loading charts (" & counter & "/50)"

Next counter

ActiveSheet.Shapes("COVER").Visible = False

Success, yaay.