Code running VERY slow when spreadsheet has linked pictures

[jung34]

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 results of searches to cells is very slow. However, using formulas (VLOOKUP, INDEX etc..) works just fine. So far I'm getting around with just formulas on that spreadsheet but I'd prefer to run code.

 

[steveblaha]

Thought I'd bump this to see if anyone is interested in taking a look at it (my question above).

Perhaps I'm barking up the wrong tree using so many Picture Links in my workbook. I am up loading about 80 pics at a time onto one sheet and rearranging them into cells that distribute to the proper sheet. Worked fine for about the 1st 5 pages, but really started bogging down the workbook after that. I have about 20 pages total with 4 pics each.

 

[fatshady]

Re: Code running VERY slow when spreadsheet has linked pictu

I've signed up for this forum just to thank you for this solution of my struggle.
i have about 25 sheets with 2 linked pics on each sheet and after applying this solution macros performance has increased at least by 500%
Thank you!

 

[bjarble]

Re: Code running VERY slow when spreadsheet has linked pictu

This stuff is great!

I'm making a "Super Bowl" squares spreadsheet. The idea is that I've got a "master" tab full of a bunch of images and then I would have 25 sheets that would have random ordered linked picture cells back to the master.


I've defined names for all my images that I reference in the target cells (ie, =Image9) based on those names.


Works except the spreadsheet drags down. I'm only on my 2nd sheet (of 25) and every update to a cell takes 1-2 minutes of Excel freezing.


I came across this thread and tried the clever "UpdatePictures" method, essentially updating my Names to read:
=if(UpdatePictures=1,Images!$B$1,"") and created the appropriate UpdatePictures Name and set it to 0.


However, now when I try to enter the name for a cell value to reference a picture, I get the "Reference isn't valid" error.


I *believe* it is because of the "" at the end of the if. Seems like the cell is expecting a picture/image, and trying to do a string or any other value causes it to puke.


I tried every method I can think of and still get the error.


Any ideas?

 

[Siyanna]

Each picture has a formula, e.g., say for Picture 1,

=Sheet2!$A$1:$C$5

Replace that with

=Pic.1

Where Pic.1 is a named formula you have created:

Pic.1 Refers to: =IF(PicsOn=1, Sheet2!$A$1:$C$5, "")

... and PicsOn is the named constant that gets changed by the code, initially defined as

PicsOn Refers to: =1

Repeat for formulas Pic.2, Pic.3, ...

Hi SHG

Im hoping you can help me with setting up which looks like an amazing solution to speed up my code

I have many sheets in my workbook
4 of them have pictures on them (about 6 picture links on each sheet) however my code is still running slow...

Weird thing is if i have this workbook open and i try to run code on another workbook that had no picture links - that workbook code runs slow which is weird but anyhows...

My 4 sheets are called (YT, TD, MM, NW)

I have tried to follow your steps by adding

A named range to every picture for each sheet

Ie named Pic.1 (Put the Link ref: YT!C2)
I repeated that step for each pic
So say last pic was named Pic.24 - Link ref: NW!C8

I have a named range Called PicsOn - link ref =1

Now im trying to now apply the code which unlinks and resets them back but its failing to add the links unless im doing something silly

Here is my Code

Sub UpdateData()

Call TurnOffPictures

‘MyCodeToUpdateStuff

CallTurnOnPictures

End Sub

For some reason its not resetting all the pics on each sheet and not linking them
Again at the end

Am i doing it right?

 

[Siyanna]

That is very clever.

Hi Mate

any chance you can have a look at my latest post on this thread n help me if u can please