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

Sub TurnOnPictures()
    ThisWorkbook.Names(“PicsOn”).RefersTo = “1”
End Sub

Works a treat.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Posted in Uncategorized

23 thoughts on “Performance of linked pictures

  1. 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?

  2. 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.

  3. I wonder if Finland is only country where we use semicolons in formulas :) Commas are used before decimals…

  4. 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 :-)

  5. 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)

  6. 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 :-)

  7. 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….

  8. 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.

  9. 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!

  10. Can I plz ask which version of Excel this was tested in? It took lots of experimenting with Excel 2010 to understand the language and descriptions used above, but eventually succeeded, but only because Excel’s error checking in not foolproof. Here’s what I did and had to be done in a particular order.

    Example:

    1) Select a data range e.g. Sheet1!$C$1:$E$10
    2) Name range of 1) to e.g. PicSnapDat1.
    3) Create range name PicsOn from the OP and select e.g. cell Sheet1:$A$1
    4) Enter any value in PicsOn you like except 1… e.g. “Off”
    5) Put the text “Check for Errors” in e.g. Sheet!$B$2 and range name it PicError
    6) Take snapshot and navigate to 2nd worksheet of same workbook, e.g. SnapSheet1!$A$1. Do Enter to create picture.link
    7) Select snapshot which on left shows the sequential image number such as “Picture 12”
    8) Overtype “Picture 12” with a name you’d like the snapshot to be referenced, call it “PicSnap1″, and do enter to store picture name.
    9) Create a range name and reference a single random sheet cell, e.g. call it PicDat1
    10) Select PicSnap1 of 8) and type on right =PicDat1 and enter. This should be accepted and referencing a valid range.
    11) Go back into the Range Name Manager and edit the PicDat1 range to have the formula =IFERROR(IF(PicsOn=1;PicSnapDat1;””);PicError) as range reference and enter.

    Backwards, PicSnap1 refers to =PicDat1 which had accepted the range original 1 cell reference. PicDat1 now changed in last step 11) refers to the conditional formula. If you’d try to enter =PicDat1 again it will be rejected as invalid.

    What was noted is that if PicsOn is set to any other value than 1, the snapshots remains [dont be confused], but wont update, whilst the data entry in the worksheet is snappy again [fairly snappy at least with 69 of these in 1 workbook), until PicsOn is set to 1 again. “Picture Off” will not appear in place of the snapshot, at least in Excel 2010 it would not [it’s not a cell], but if there’s an error, the “Check for Errors” will appear in place of PicSnap1. Alternately, on could make the ;””) part of the formula to refer to another cell that has the text “Set PicsOn to 1”, but that has performance impact, certainly 69 times as is done in my workbook, even when worksheet has been hiden / deactivated.

    I’m sure this stepped set could be made shorter and reordered, but this is how I got it to work. Thanks so much to Jan Karel for sharing the idea and others for their clarification… unawares one could make a range name reference a formula, to in fact be able to show different snapshots in the same location. Think presentations right off the laptop linked projector will gain… possibly type in a graph name or select and an indirect cell range reference will make it appear in place… no navigating around, just click names from a talk list… once again, the sky has no limit.

    cheers

  11. I spend many hours going through my code before I concluded that the camera tool was causing the performance disaster.
    Then I found this code and implemented it. Indeed performance returns to normal.
    Like to share an automation of this code, an adaption of code found elsewhere (and don’t know where anymore)
    This code makes the manual labour obsolete. Run it now and then, or make it event or code driven, so that all camera pictures are made conditional and then use JKP code to turn camera pictures update on/off. There might be smarter ways to do this, but at least this seems to work.

  12. Fantastic macro – truly eliminates the need to update the pics – saved hours of work – had 200 pictures to re code manually!!

  13. @WIM
    I’m about to try your SetupPics sub but notice you have the following line:

    in the last IF…Then construct at the bottom.

    Are you able to post the code from that function?

    Thanks

  14. I don’t know how you have a NameExists function without passing a worksheet, but here’s how I would do it.

  15. Man this code really helped me out. Took me a moment or 3 to figure out Wim code which also really helped me quickly format the names of some 50 plus pictures.

    Thanks!

  16. Just want to add a long time after that this was very helpful – a report that I had previously had to let run overnight, is now back to refreshing in 40 seconds. Camera function is great and exactly what I needed, but didn’t realise it would destroy my performance.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.