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.

 

[shg]

That is very clever.

 

[qle]

Re: Code running VERY slow when spreadsheet has linked pictu

I'm having a hard time using that. Do I need to create a unique name for each picture?

Sub TurnOffPictures()
    ThisWorkbook.Names("PicsOn").RefersTo = "0"
End Sub

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

What does this code do?

=IF(PicsOn=1,Sheet2!$A$1:$C$5,"")

 

[shg]

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

 

[qle]

So do you have to go to each sheet and give the reference area a unique name? Do you have an example code by any chance?

 

[shg]

Not sure I understand your question. Each linked picture already has a formula. You need to change the formula as I described for each one. How many do you have?

Am I misunderstanding?

 

[qle]

Depends on spreadsheet. Some has 1 some has 20 or more. That's why the code I wrote earlier just went through each sheet and save the link and removed it and then put it back when done without care to naming the area of reference.

 

[shg]

OK; minimally tested:

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
 
    Set wkb = ActiveWorkbook
 
    wkb.Names.Add Name:="PicsOn", RefersTo:="=1"
 
    For Each wks In wkb.Worksheets
        For Each pic In wks.Pictures
            iPic = iPic + 1
            sFrm = pic.Formula
            
            On Error Resume Next
            Set r = Range(sFrm)
            On Error GoTo 0
            
            If Err.Number Then
                Err.Clear
            Else
                wkb.Names.Add Name:="Pic." & iPic, RefersTo:="=if(PicsOn, " & sFrm & ", """")"
                pic.Formula = "=Pic." & iPic
            End If
        Next pic
    Next wks
End Sub

 

[shg]

Erm -- that code's not really bulletproof ...

 

[qle]

All the pictures disappears, I think with a little bit more tweaking, I can get it to work. Will work on it more tomorrow. However, for right now, I'll stick with my old code where I don't have to mess with cells naming and it doesn't affect the other sheets. Thanks for your help. Too much staring at codes make head go bang.

 

[steveblaha]

I tried qle's revised code and it seems to work well. The only issue I've had thus far is that the relink doesn't work if the workbook is closed and then re-opened after doing the unlink. Is there any way to save those?
Thanks,
Steve