Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Slow running macro (Excel XP)

    Attached is the layout sheet I use daily in my business. Numerous people on this forum have been helpful in its continued evolution over the past several years.

    It has now gotten pretty elaborate and perhaps, too much so.

    If you unzip it and take a look at the PRINT SHEET macro you'll see that it saves the form to a backup file and then prints some labels and then prints the form.

    Before adding the label printer this process took a few seconds from start to printing at the printer. After adding the label printer it is taking noticably longer....perhaps 10 seconds to get to the printing the form step.

    However, the label printing is near the end of the 10 second wait, and when it begins goes quickly.

    Does anyone see anthing here that would be slowing the process down that much? It acted this way on my P4 3.2 system and is still acting the same on my new Core Duo 2.4 system. Both have 2 gig of RAM. In both systems I have macro protection set to "low" in Excel and disabled in the options for Norton Antivirus. (As such I am very careful about opening Excel files not generated by myself.)

    Thanks or your time and interest,
    BH Davis

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Slow running macro (Excel XP)

    Modifying page setup in a macro is notoriously slow. See <post:=177,205>post 177,205</post:> for a method that is reputedly faster (I haven't tried it myself).

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Slow running macro (Excel XP)

    HansV,

    I found the problem thanks to your comments.

    The slowdown was taking place as a result of the copy/paste of the entire form to a backup workbook. I was only copying the data area of the layout sheet, but it was forcing the backup sheet to go through all the extensive calculations as a result of the paste. The backup workbook sheet was just a SAVE AS of the original to LAST FORM. All cell formulas came along for the ride.

    Every time I pasted a new set of data into that backup sheet the macro process took longer and longer. After recreating a new blank backup sheet it would work fine until numerous saves added more and more data to the backup sheet. That made recalculating the entire sheet more and more complex.

    In the short term I only need to periodically create a new "blank" backup sheet. In the long term I'm going to edit my macro to do a PASTE SPECIAL, VALUES . That way by starting with a completely blank backup sheet I'll never be adding any formula caluctions to the paste operation. Also, the base LAST FORM backup sheet is now going to start out as a blank form with all formlas deleted. The only reason I need a backup is I occasionally need to reprint the previous version of a layout once I've already changed the data on the currently worked on sheet. The VALUES paste will give me all I need.

    Thanks again for your help.

    BH

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Slow running macro (Excel XP)

    Ahhhhhhhhhh... .now I remember this too..........you can't use PASTE SPECIAL on a range of cells.......or at least, I get an error message: "This operation requires the merged cells to identically sized." I get that message even though the merged cells "are" identical in shape and size to those into which I'm pasting the information. I've experiemented in a simple worksheet with just a few merged cells and get the same message.

    Is there a way to PASTE, VALUE my layout sheet form to my backup Last Form?

    Thanks again for helping.
    BH

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Slow running macro (Excel XP)

    You have found one of the reasons why you should not use merged cells - they are a pain in the neck (or elsewhere). But it'll be a lot of work to redesign your rather busy form without them...

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Slow running macro (Excel XP)

    Rather busy is putting it mildly. There is a ton of information there my guys need to make our mouldings, and it all has to print out onto an 8 1/2 x 11 so they can actually produce a product from the form. Actually, many variations of the product!

    Thanks for discussing the issue with me.
    BH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •