Create Sparklines very slow when lots of Sparkline Groups already existed for given launch

Hello,

I have an Excel file that I use for some personal projects for which I could really please use some help. The macro will create or delete non-contiguous sparklines; only one worksheet would ever have Sparklines and no other charts are present in the workbook. The data for the sparklines is kept on a different sheet then the sparklines. The quantity of sparklines will vary depending on source data and can be upwards of 10,000+.

If the xlsb file is launched when no sparklines are exist, then the code to create the sparklines runs upwards of 10 to 30 times faster than if the sparklines had previously existed for a given launch of the file. Removing the sparklines or deleting and recreating the entire Target sheet does not improve the speed, only saving without Sparklines and relaunching.

The slow performance can have a major impact. For example: ~8,000 Sparklines takes ~55 seconds to execute when running quickly but can take ~1700 seconds if Sparklines were either already present when the file was launched or had previously been created and then removed. Smaller sets of Sparklines would naturally take less time to create but would still suffer from the same difference in the speed at which they are created, i.e. a smaller set of Sparklines might take 1.5 and 20 seconds to execute for the fast and slow executions respectively.

Sparklines are placed into cells that previously contained numerical results, and the results are Backuped up on a different worksheet as they would be replaced when Sparklines are removed. There are no formulas or conditional formatting in the workbook.

ToggleSparklines function is triggered from the Target sheet via a button so the Target sheet is active at the time the function is triggered.

I have spent hrs and hrs trying to figure out this problem, and I have exhausted everything I can think to try. This is my first time submitting a thread so hopefully I am doing it correctly.

I would really appreciate any possible suggestions. Thank you very much

NOTE: the sample code has been removed since the code contained in subsequent posts is targeted more at the specific issue.

WORK AROUND SOLUTION:
After many, many hours, I have finally stumbled upon what appears to be a viable work around for the difficulty although I still think the culprit must be some bug in MS Excel.

The solution was to add each non-contiguous Sparkline as it was created to a consolidated single Sparkline Group.

The previous method would result in one Sparkline Group for each Sparkline so there would be thousands of Sparkline Groups which would then experience the performance hit if the Sparklines/Sparkline Groups were removed and re-created on the same launch of the fi

The current method used to add each non-contiguous Sparkline to a consolidated Group is to "Select" the full range in the destination worksheet that would receive the Sparklines and then perform the Sparkline Group command. There might be a better method of creating and adding non-contiguous Sparklines to a group that doesn't rely on a worksheet "Select" command, but I haven't yet figured out how to do it for Sparklines that exist in different rows and columns. Also, the addition of the Sparklines to a group has to be done as each Sparkline is created rather than after all of the Sparklines have been created otherwise the performance difficulty is not improved.

The below code is a sample set that switches between adding and removing Sparklines. As it stands, my sample set now consistently takes less than 2 seconds to create the Sparklines, regardless of whether or not Sparklines had previously existed or not.

NOTE: during the testing, I also identified that the performance difficulty didn't start occurring on my system until somewhere between 650-800 Sparklines/Sparkline Groups had been created and removed.

Please Login or Register  to view this content.