Macro slow with shapes; Sheets with shapes not touched

[Ashirra]

Hello

I'm using this board quite a few times but I never posted a question.

I'm working on a special project in my company and for this project we use excel and vba code.

We got a strange behavior.

With VBA we import data from other workbooks into the main workbook. This tooks about 30s for about 40k rows.

After we inserted two more sheets with a few structured tables and charts/camera objects and other shapes it tooks over 3-9 minutes. The clue is in the macro we didn't touch these sheets at all.

Memory used w/o these two sheets 180Mb
Memeroy used with these two sheets 230Mb

CPU usage mostly the same.

without these two sheets => 30s
with the sheets, delete all charts/camera objects/shapes => 45s
with sheets and objects => 3min up to 9min

in the last case the time for each time we call the macro it's increased by 3min and finds it's peak at about 9 min
usage of memory and cpu are more or less the same.

during the macro following things are disabled
- Screenupdating = false
- Calculation = manual
- enableEvents = false

Thank you for your help
Ashi

 

[Ashirra]

no one? really?

 

[RoryA]

camera objects

I'd pretty much guarantee that that is the problem right there. It's well known that linked pictures like that slow VBA code down dramatically. Your best bet is generally to alter the linking formula to use a cell value to control it - for example:

=IF(Sheet1!$A$1=1,link range here,"")

so that you can set A1 to anything other than 1 while the code is running and then reset it again afterwards to re-enable the linked pictures.

 

[Ashirra]

First thank you Rory


But your answer was only part of the solution. I think in normal cases this workaround is just fine.


After we did the workaround the slicers got faster but not that fast.


So we did some further investigation and found out that when my colleague created the slicers they were fast as hell. Also, if I used them.
But when I did the same thing -> slow, slower, slowest. When my colleague used the slicers, the same behavior occurred.


In the end the solution was easy. Deinstallation and reinstallation of Excel on my computer.

I hope that this few lines can help someone with the same problem.

 

[RoryA]

I'm glad your problem is resolved. I would point out that this is the first mention you have made of slicers. Your question was about code and its performance being affected by camera objects.