Macro slow with shapes; Sheets with shapes not touched

Ashirra

New Member
Joined
Dec 13, 2017
Messages
4
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Dec 13, 2017
Messages
4
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top