VBA Hangs While Producing Complex Graph

[pbornemeier]

Running Excel 2016, 32-bit on a Windows 10, 64-bit system. I use VBA code to analyze a large data set and produce moderately complex graphs consisting of 12 series of which about half are displayed (IsFiltered = False) at any time depending on various conditions. The code hangs about 1 in 4 times at various points in execution. To correct the problem, I found that I have to right-click on the graph to bring up the context menu, click 'Select Data...' which brings up the 'Select Data Source' window, then without doing anything else click either 'OK' or 'Cancel' to close the Select Data Source window. This causes the formerly invisible series to reappear. When I click 'Run' in the VBE, the code continues.

Image 01a & 01b show the error (1004 - Parameter not valid) and a representative spot in the code where the program stops. The series being complained about does exist. Other images show the state of the graph when the code stopped (02) as well as the graph after (performing Select Data, Cancel) (03 - The green line appears). Sometimes it is other series that are not immediately visible. Rerunning the code on the same data sets that once raised the error may or may not cause a problem on subsequent runs.

I have tried various code solutions, including: adding and removing additional series, multiple DoEvents statements, de-selecting and reselecting the graph prior to the points where the halt occurs in hopes of making the series visible and keeping the code from stopping, but have not achieved that goal. I have run the code on multiple machines and the same problems occur.

I would prefer to figure out why the code was stopping and then recode to prevent that. I would be happy with a method to recreate the manual steps of "Select data" and "Cancel" in code so I could have that code execute in response to the errors that currently halt the code. Advice for either is appreciated.

 

[frabulator]

I dont know if this is your issue, or if my input would help, but I ran into a similar situation when dealing with graphs when Microsoft released its updates around the time of 2016-2019 Excel migration. The 'FullSeriesCollection' was changed to "SeriesCollection", or vice-a-versa, I cant remember as it has been several years. But once I changed those around everything straightened up.

As for the speed, I found from testing graphs that the quickest way to achieve large data crunches is with 'application.screenupdating = false'. Do Events seems like it would work faster, but in most cases, it does not outperform just hiding pages from view.