Performance of linked pictures
I frequently use the camera tool to create pictures linked to cell ranges.
For example to be able to have different column widths beneath each other on one sheet.
They have one major drawback however: they can slowdown VBA performance (when updating cells) enormously (update time may go up from .2 secs to as much as 8 seconds for the same code).
My workaround:
Each camera tool object uses a defined name, defined such as:
=IF(PicsOn=1,Sheet2!$A$1:$C$5,“”)
Then I use these two tiny subs to turn the picture updating on and off:
Sub TurnOffPictures()
ThisWorkbook.Names(“PicsOn”).RefersTo = “0″
End Sub
ThisWorkbook.Names(“PicsOn”).RefersTo = “0″
End Sub
Sub TurnOnPictures()
ThisWorkbook.Names(“PicsOn”).RefersTo = “1″
End Sub
Works a treat.
Regards,
Jan Karel Pieterse
www.jkp-ads.com
Nice tip, but when i try to type in
=IF(PicsOn=1;Sheet2!$A$1:$C$5;”")
gives me an error “The text you entered is not a valid reference or defined name.”
Do you know why this happens?
Use commas not semicolons.
Keggi,
It might be the semicolons instead of commas.
It worked for me when I created a named formula “Pic.1? with the formula
=IF(PicsOn=1,Sheet2!$A$1:$C$5,”")
Then select the picture, and in the formula bar type “=Pic.1?
JKP – great trick! I had tried a similar workaround using VBA to assign and de-assign the formula address values with very unsatisfactory results.
I wonder if Finland is only country where we use semicolons in formulas
Commas are used before decimals…
Thank you AlexJ.
Your named formula workaround did the trick!
Keggi, The Netherlands uses the semi-colon too. I always have to be careful to replace them with comma’s, so the rest of the world doesn’t complain my tip does not work
Brilliant! (definitely needs to be done via a named range though, which was not too clear in the article).
While it helps your VBA run faster, I can also see it being used with something like a control (check box or drop down) to turn on/off updating – for example to compare old and new data / charts side by side, or just generally while working on a sheet, then switch to “update” so you have your collection of pictures ready for printing, or the use I often put this too which is copying and pasting to a PowerPoint presentation or to web pages.
(incidentally, whether using old-school camera tool or newer “paste as linked picture”, the end result seems to work basically the same and supports this trick. Only difference I have found is new function defaults to no background, old had white fill. Either can be changed of course)
Keggi, Portugal default setting is also semi-colon for list separator (as colon stands for the decimal separator). It’s a thing I allways have to change whenever I install Office in one of my computers (it’s an acquired habbit from the times when the only available excel version was the english one). Unfortunatelly I’m allways making mistakes whenever I’m on a “uncustomised” computer and also getting lot of complaints – “It doesn’t work” – from my students whenever I give an example and forgot to replace the colon by semi-colon
Love it. Love it love it LOVE IT.
Not working for me and I’m going batty at this point. I created a named formula called “PicsOn” and set the value to 1. Then I created another named formula called “Pic.1? and put the following formula in: =IF(PicsOn=1,Reference!$X$3:$Y$6,”"). I then changed the formula of my picture to “Pic.1?. No matter what the value of “PicsOn”, I get the same picture as before. I also did the same thing without using a named formula for “PicsOn”; instead I set a cell, A2, to 1 and then referenced that cell in the “Pic.1? formula. Still, the picture remains….
Funny enough, if I completely remove the entire formula for my camera object, the picture remains.
Just a note to those who may read this and not understand. The formula has to be a “named” one, ie you have to go into insert > Name > Define.. and input the formula as part of the name.
In my case I had the pictures in only one sheet so what I did was to set the change command with the event worksheet_activate and then I also set a worksheet_deactivate. That way when I clicked on the sheet it would update, then I would click away and it would disable the pictures again. This made it automatic, no buttons or anything.
I used this today. It cut the time on a VBA procedure from many minutes to a few seconds, and also got rid of screen flicker. Like Sebastian I tied it to a Worksheet_Activate event. I changed the switch from 0 and 1 to TRUE and FALSE, to make it easier to understand in the future. Finally, I noted that I had to set the switch to TRUE before applying the formula to the picture, or else I’d get the “The text you entered is not a valid reference or defined name” message. Thanks Jan Karel!