I have an excel spreadsheet for which I get regular updates. I then create graphs of various bits of data in this sheet. I automated that in VBA but running the macro takes an extremely long time. It seems to be the actual creation of the graph object itself, before I’ve even linked it to any data. That particular line can take minutes to run!
The spreadsheet itself is large (18MB) but not huge. The problem does seem to be related to the size since if I delete data it seems to run much quicker.
Has anyone encountered similar problems or does anyone have any suggestions (within the VBA universe!)
I once tried to create a graph from scratch in VBA. Ultimately I chose to have my template file include a graph and then the VBA updated the data sources and anything else I wanted.
That ways the VBA isn’t creating a totally new graph. I’m not sure if your issue is that part or actually the updating of the data sources though.
Poly’s method seems sound. For regular updates, your graphs should not have to be created each time.
So, create the graphs.
Create a tab for data.
Create a tab for summary data for graphs from the data tab.
For each regular update, merely update the data.
Done.
If your file has “a lot” of calculations in it, you might turn off “Automatic Calculate” (can be done within VBA as well). That way, when you get new data; you can manually launch “Calculate Now” and have the process take its time, then have the graph creation (which can be “right clicked” and select “update”).
Poly’s method is not ideal, since I’m getting a different excel file each time and I’d prefer to keep the graphs in those files. But it’s a good suggestion because if it runs faster with the graphs pre-created, then it’ll be worth it. I’ll try that at some point. I’ll just have to copy-paste the new data into the template and then update the ranges of data that each graph refers to.
Dim newChartObj As ChartObject
Set newChartObj = ActiveSheet.ChartObjects.Add(endRange.Left, endRange.Offset(2, 0).Top, 400, 300)
It’s specifically the second line where the program is hanging - it can spend 60-90 seconds on this, just creating the graph object. At this point the graph object doesn’t even contain any data - that’s added in the next few lines and doesn’t take any time.
The slowness does seem to be related to the size of spreadsheet. If I delete a bunch of columns, reducing the spreadsheet to around 7Mb, it takes 5-10 seconds to create the graph. Still slower than I’d have hoped for but much acceptable. But ultimately, I’d prefer to keep these columns. All of this slowness makes no sense to me because if I create the graph manually, it happens immediately, even if I haven’t deleted any columns.
@Vorian - there aren’t “a lot” of calculations (0 in fact), so that’s not the issue. I did try with calculations set to manual just in case that made a difference but it didn’t. I also tried setting screenupdating to false without any effect.
I have no ideas about the speed issue. I would guess copy/pasting the blank chart from the template file into the file with the data is also slow, but it’s possible it would run fine. Then you could continue to add the data etc all in the same source file. It feels like a roundabout approach though.
I also have no idea about the speed issue, which is why I posted in the first place The actual process is doing exactly what I want but taking 10x longer than it ought - and I don’t know what Excel is thinking about under the hood when it’s grinding away to no apparent effect. I guess I’ll just have to experiment and see if there is something which makes a difference. Thanks for you suggestions - they are worth trying.
How many Sheets are there for these files that contain data that you want to graph?
It seems to me that something is telling VBA to scan the entire (populated) region. You might look into seeing how “endRange” is defined (if at all) what is encompassed by it.
It might be that by “deleting” some columns, something internal is “reset” that makes subsequent “scanning” faster (or that the scan is faster because it’s looking at fewer things).
Another thing that I’ve done in the past wrt creating “templates” for dumping data (especially large amounts of it).
You might create an “intermediary” or a “staging” worksheet that will reference the sheet(s) where the data you want to use via the INDIRECT function. This will allow you to delete a worksheet (that is being referenced) and replaced w/o breaking other functions. Then you can have your chart object “pointing” to this staging sheet and you’d have a “template” by which you can either copy this worksheet to the file(s) where it’s needed.
Thanks for the suggestions, folks. To answer some questions that were asked and give some updates.
Only one sheet (no formulae)
endRange is well defined: it’s the last cell that contains data that I want to graph and I set it with: endRange = ActiveSheet.Range("C:C").Find(countryName, After:=Range("C1"), SearchDirection:=xlPrevious)
No autosave issues
The data that I’m interested in copying is in the first 10 columns (there are about 60 altogether. I’ve found that if I cut and paste columns 11-60 into a new sheet (within the same workbook), that the macro runs much quicker. So I can create a temporary sheet, move the extraneous data there, create the graphs and then move the data back again. This is a horrible hack but for the current purposes, it works.
It seems that there are at least three different ways to add charts in VBA
Set cht = ActiveSheet.ChartObjects.Add(100, 100, 360, 240).Chart
Set cht = ActiveSheet.Shapes.AddChart(xlXYScatterLines, 100, 100, 360, 240).Chart
Set cht = ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines, 100, 100, 360, 240).Chart
The first is what I’m using which is also the oldest method. I suspect that using the third method might speed things up, so I will probably play around with that to see if I can get it to work.
If I were to hazard a guess, it would be that the “Find” method in your endRange object is triggering Excel to perform an indexing operation (to make subsequent filtering faster) upfront. And this indexing is getting performed over the entire worksheet (hence, why it runs faster after removing columns).
You might look to see if there are other “tweak” arguments for that method.
I don’t think this is the problem. The line with the Find executes very quickly. It’s specifically the line where the chart is added. Set newChartObj = ActiveSheet.ChartObjects.Add(endRange.Left, endRange.Offset(2, 0).Top, 400, 300)
At this point there is no data specified for the chart - the chart object is simply added. However, I think VBA is doing a lot of processing behind the scenes to try and work out what default sets of data should be used.
The actual process is doing exactly what I want but taking 10x longer than it ought - and I don’t know what Excel is thinking about under the hood when it’s grinding away to no apparent effect. I guess I’ll just have to experiment and see if there is something which makes a difference. Thanks for you suggestions - they are worth trying.