Excel VBA - Macro Slows down with embedded images which are not part of VBA code

[jmarkus]

Hello,

I have a workbook with multiple sheets. Each sheet has a (different) code which is executed when I switch to that sheet. One of the sheets (A) has linked pictures in the first column. When I switch to sheet (B) I execute some code which, after populating the sheet with text values from other sheets simply goes through a range of rows and hides any rows which didn't have data in them. That code is simply:

For Each xRg In Range("F17:F117")
             If xRg.Value = "" Then
                xRg.EntireRow.Hidden = True
             Else
                xRg.EntireRow.Hidden = False
             End If
Next xRg

If I delete all ~150 images in sheet A and switch to sheet B it takes less than a minute to execute sheet B's code. If I keep all the images in sheet A, it takes ~40-50 minutes! Other functions have also slowed down, but the For-Next loop above is by far the slowest (I put time-stamps between different lines of the code to check). Right now, my work around is to delete the images prior to executing sheet B's code (the images get placed on sheet A via another macro as linked pictures) and then putting them back when switching to sheet A again, but that seems really kludgy.

Since the code above doesn't rely on sheet A, I don't understand why things slow down with the images.

Any and all insight is appreciated.

Thanks,
Jeff

 

[Andrzejek]

Just a guess here, but did you try:

[blue]Application.ScreenUpdating = False[/blue]
For Each xRg In Range("F17:F117")
    If xRg.Value = "" Then
        xRg.EntireRow.Hidden = True
    Else
        xRg.EntireRow.Hidden = False
    End If
Next xRg [blue]
Application.ScreenUpdating = True[/blue]

---- Andy

There is a great need for a sarcasm font.

 

[jmarkus]

Yes, I tried that and I tried setting calculations to manual, but it is still much slower with images than without.

Jeff

 

[SkipVought]

Rather than row by row, I'd put a filter on and 1) select empty cells on column F and 2) select visible rows between 17:117 and 3) hide the selection.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jmarkus]

I had a suspicion that just because things slowed down in the for-next loop, maybe that wasn't the cause - so I commented out the entire loop and it STILL slowed down. So now it seems that I have nothing to go on other than with images macro runs slow, without it doesn't...

I've also noticed that while my macro is running, other Office applications like Outlook slow down too!

Dazed and confused...

Jeff

 

[jmarkus]

Okay, after more playing and tweaking, I've determined that for better or worse, the performance drag is caused by having linked images.

My code requires the linked images at the start in order to show the right pictures for the right data and put them in the right place, however once that is done and they can move and size with the cells, they don't need to stay linked.

So what I want to do is change the linked picture to a 'dumb' JPEG. I thought the best way to do this would be to select each image and cut it and then paste it back in the same place using PasteSpecial as a JPEG. So here is my code for that:

Dim topLeft As Range
Dim Pname As String
    For Each pic In Sheets("BOM").Shapes
       If Left(pic.Name, 8) = "AsmImage" Then
       Pname = "P" & pic.Name
        pic.Select
        Set topLeft = pic.TopLeftCell
        Selection.Cut
        topLeft.Select
        Sheets("BOM").Cells(1, 5) = "+"
        ActiveSheet.PasteSpecial Format:="Picture (JPEG)"
        Selection.Name = Pname
       End If
    Next

Unfortunately, it seems that this too suffers from performance lag (once I get the JPEGs instead of the linked EMFs everything else does speed up significantly though). So now I'm looking for suggestions how to change my linked pictures to JPEGs efficiently.

Thanks,
Jeff

 

[jmarkus]

So, for now I've arrived at a compromise. Simply removing the link, after the picture has been updated to show the correct image solves the performance issue.

The file size is still larger than I would like because I couldn't get my code above to work consistently for some reason and therefore the pictures are still EMF format, but I can live with that...for now.

Jeff