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.

 

[Ivan F Moala]

Hi jung34


If you prefer to run your code, then via code...delink the Picture links @ the Start and then relink @ the End.

 

[jung34]

Re: Code running VERY slow when spreadsheet has linked pictu

Thanks.... that is what I'd like to do. However, I didn't link the pictures using code but through Excel shift-Edit paste as picture... so I if you could help me with the code I'd appreciate it.

 

[Andrew Poulsom]

Re: Code running VERY slow when spreadsheet has linked pictu

Have you tried:

Application.Calculation = xlManual

at the start of your code, and:

Application.Calculation = xlAutomatic

at the end? It might speed things up.

 

[jung34]

Re: Code running VERY slow when spreadsheet has linked pictu

Thanks, I've tried that to no avail. (about a 10% increase in speed which I don't think is related to the linked pictures).

 

[Andrew Poulsom]

Re: Code running VERY slow when spreadsheet has linked pictu

What sort of linked pictures are they? Are they linked to the workbook which contains your code? I am struggling to think of a reason that they would slow down your code.

Maybe you could also try:

Application.ScreenUpdating = False

at the beginning of your code, and:

Application.ScreenUpdating = True

at the end.

 

[jung34]

Re: Code running VERY slow when spreadsheet has linked pictu

Thanks... I've tried application.screenupdating also. No change.

The link is a picture link that is set up by copying some cells and pasting using shift-Edit paste picture.

I really like the delink, relink idea, however I'm not sure of the code to do that. I've tried searching for those words and combinations of other related words for some VBA function that will take the linked pictures out of memory while code is running. It seems like a feature that should exist.

 

[Andrew Poulsom]

Re: Code running VERY slow when spreadsheet has linked pictu

Try this:

Option Explicit
Dim Arr() As Variant

Sub Unlink()
    Dim Pic As Picture
    Dim x As Integer
    x = 1
    For Each Pic In ActiveSheet.Pictures
        ReDim Preserve Arr(1 To x)
        Arr(x) = Pic.Formula
        Pic.TopLeftCell.Select
        Pic.Copy
        ActiveSheet.PasteSpecial Format:="Picture (GIF)", Link:=False, _
        DisplayAsIcon:=False
        Pic.Cut
        x = x + 1
    Next Pic
End Sub

Sub ReLink()
    Dim Pic As Picture
    Dim x As Integer
    x = 1
    For Each Pic In ActiveSheet.Pictures
        Pic.Formula = Arr(x)
        x = x + 1
    Next Pic
End Sub

 

[qle]

Re: Code running VERY slow when spreadsheet has linked pictu

Thank goodness Andrew for the solution above. I was going crazy as to why Excel macro slow down. It took me a week to find your solution. I think it is still a bug that Excel need to fix in MS 2007 as well. I modified the code a tiny bit since i'm using copy cells and pastes cells as picture with link:=true and i have a lot of sheets with picture links.

option explicit  'place at to top of forms or module
Dim Arr() As Variant 'place at top of forms or module

Sub Unlink()   'call this before running code by "Unlink"
    Dim Pic As Picture
    Dim ws As Worksheet
    Dim x As Integer
    on error resume next
    x = 1
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        For Each Pic In ActiveSheet.Pictures
            ReDim Preserve Arr(1 To x)
            Arr(x) = Pic.Formula
            Pic.TopLeftCell.Select
            Pic.Formula = ""
            x = x + 1
        Next Pic
    Next ws
End Sub

Sub ReLink()    'call this after running code by "Relink"
    Dim Pic As Picture
    Dim x As Integer
    Dim ws As Worksheet
    on error resume next
    x = 1
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        For Each Pic In ActiveSheet.Pictures
            Pic.TopLeftCell.Select
            Pic.Formula = Arr(x)
            x = x + 1
        Next Pic
    Next ws
    ReDim Arr(1)
End Sub

 

[RoryA]

Re: Code running VERY slow when spreadsheet has linked pictu

You should also have a read of this for an elegant solution from MVP Jan Karel Pieterse.