Copy Cell and Paste as Picture Link Causes Slow Down of Macro Execution

[bisel]

Hello All,

My workbook is fairly large and there is a lot of VBA in it. I am experiencing a problem that has been reported before, however the solution in prior posts is not working.

My problem:

When I copy a cell and paste it as a linked picture, the VBA slows ... especially when that VBA is checking individual cells for values, etc. Here is the link to prior post from a few years back ...

Code running VERY slow when spreadsheet has linked pictures

I have a spreadsheet with about 10 linked pictures and trying to run code in ANY spreadsheet while that one is open, is VERY slow. I'm guessing that there might be a VBA option to take the pictures out of the picture while running the code. For example code that searches and writes the...

www.mrexcel.com

I am experience the same symptoms that were mentioned in the above post:

• If I copy any cell on any worksheet and then paste it as a picture link anywhere in workbook, execution of VBA slows. Even for private subs on unrelated worksheets.
• If I remove the picture that is linked to another cell, the problem goes away.

There is a bit of code which back in 2003 resolved the issue for the OP in that post, however, that code no longer works. If you review the post the code that was reported back in 2003 or so ran a routine which first un-linked picture objects from their source and then re-linked them after the code finished running which was previously running slowly.

Anyone have an up to date solution. I am running Win11 and Office 365 (or whatever Microsoft is calling it these days).

Thanks,

Steve

 

[Mr Statto]

Hi Steve, this is a problem I had a few years ago which reared its head again today, at which point I stumbled across your question. If you've not been able to resolve it already then can I suggest the following:

1. Name one cell "UPDATE" and use data validation to limit it to TRUE or FALSE
2. Name the ranges that contain the data you want in the pictures, but rather than just referring to the range use the formula "=if(UPDATE,
   #VALUE!

#VALUE!

 

[Mr Statto]

Hi Steve, this is a problem I had a few years ago which reared its head again today, at which point I stumbled across your question. If you've not been able to resolve it already then can I suggest the following:

1. Name one cell "UPDATE" and use data validation to limit it to TRUE or FALSE
2. Name the ranges that contain the data you want in the pictures, but rather than just referring to the range use the formula "=if(UPDATE,
   #VALUE!

#VALUE!

Sorry, it looks like something in my answer has caused all sorts of issues! I'll try again

1. Name one cell "UPDATE" and use data validation to limit it to TRUE or FALSE
2. Name the ranges that contain the data you want in the pictures, but rather than just referring to the range use the formula "=if(UPDATE, {range} )" (eg =IF(UPDATE,Slide!$J$62) (see attached screenshot for examples)
3. Copy the range as a link, or use the Camera tool
4. Change the reference of the linked picture to the range name (eg change "=$G$36" to "=NORDIC_ON"
5. Amend your VBA code to set the UPDATE cell to FALSE before running, and then back to TRUE once complete.

This makes a huge difference to the running of the code