×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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:

CODE

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

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

Just a guess here, but did you try:

CODE

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


---- Andy

There is a great need for a sarcasm font.

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

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

Jeff

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

(OP)
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

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

(OP)
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:

CODE

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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close