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. Use commas not semicolons.

3. Keggi,
   It might be the semicolons instead of commas.

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

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

6. Thank you AlexJ.

   Your named formula workaround did the trick!

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

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

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

10. Love it. Love it love it LOVE IT.

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

12. Funny enough, if I completely remove the entire formula for my camera object, the picture remains.

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

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

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

16. Pingback: User-Friendly Surveys Without VBA - yoursumbuddy

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

    Visual Basic

    Private Sub SetUpPics() Dim wkb As Workbook Dim wks As Worksheet Dim pic As Picture Dim sFrm As String Dim r As Range ' scratch range for testing picture formula Dim iPic As Long ' picture index Dim PicName As String, i As Integer Set wkb = ActiveWorkbook wkb.Names.Add Name:="PicsOn", RefersTo:="=1" For Each wks In wkb.Worksheets For Each pic In wks.Pictures sFrm = pic.Formula 'bevat de formule 'n verwijzing naar 'n cel 'in code: zit er 'n dollar teken in If InStr(sFrm, "$") > 0 Then 'okay, 'n celverwijzing 'zit de verwijzing naar het worksheet er bij? 'in code: zit er 'n ! in If InStr(sFrm, "!") > 0 Then 'hoef je niets te doen Else sFrm = " '" & wks.Name & "'!" & sFrm End If On Error Resume Next Set r = Range(sFrm) On Error GoTo 0 If Err.Number Then Err.Clear Else PicName = "Pic." & i While NameExists(PicName) i = i + 1 PicName = "Pic." & i Wend wkb.Names.Add Name:=PicName, RefersTo:="=if(PicsOn=1, " & sFrm & ", """")" pic.Formula = "=" & PicName End If End If Next pic Next wks End Sub

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

    Private Sub SetUpPics()     Dim wkb As Workbook     Dim wks As Worksheet     Dim pic As Picture     Dim sFrm As String     Dim r As Range ' scratch range for testing picture formula     Dim iPic As Long ' picture index     Dim PicName As String, i As Integer       Set wkb = ActiveWorkbook          wkb.Names.Add Name:="PicsOn", RefersTo:="=1"          For Each wks In wkb.Worksheets         For Each pic In wks.Pictures             sFrm = pic.Formula             'bevat de formule 'n verwijzing naar 'n cel             'in code: zit er 'n dollar teken in             If InStr(sFrm, "$") > 0 Then                 'okay, 'n celverwijzing                 'zit de verwijzing naar het worksheet er bij?                 'in code: zit er 'n ! in                 If InStr(sFrm, "!") > 0 Then                     'hoef je niets te doen                 Else                     sFrm = " '" & wks.Name & "'!" & sFrm                 End If                                  On Error Resume Next                     Set r = Range(sFrm)                 On Error GoTo 0                                  If Err.Number Then                     Err.Clear                 Else                     PicName = "Pic." & i                     While NameExists(PicName)                         i = i + 1                         PicName = "Pic." & i                     Wend                     wkb.Names.Add Name:=PicName, RefersTo:="=if(PicsOn=1, " & sFrm & ", """")"                     pic.Formula = "=" & PicName                 End If             End If         Next pic     Next wks End Sub

18. Pingback: Solution to slow performance of VBA with linked pictures

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

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

    Visual Basic

    While NameExists(PicName)

    1	While NameExists(PicName)

    in the last IF…Then construct at the bottom.

    Are you able to post the code from that function?

    Thanks

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

    Visual Basic

    Public Function NameExists(ByVal sName As String, ByRef sh As Worksheet) As Boolean Dim sReturn As String On Error Resume Next sReturn = sh.Parent.Names(sName).Name If Len(sReturn) = 0 Then sReturn = sh.Names(sName).Name End If NameExists = Len(sReturn) > 0 End Function

    1 2 3 4 5 6 7 8 9 10 11 12 13 14

    Public Function NameExists(ByVal sName As String, ByRef sh As Worksheet) As Boolean          Dim sReturn As String          On Error Resume Next         sReturn = sh.Parent.Names(sName).Name                  If Len(sReturn) = 0 Then             sReturn = sh.Names(sName).Name         End If          NameExists = Len(sReturn) > 0      End Function

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

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