Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Very Slow Macro execution!! (Excel 97)

    I have a macro that I created some time ago but I still use every week. I would like to know if there is a way to improve the performance of it or I'd at least like to understand why it works so slowly. The code is performed on two chart pages that are each set up to occupy a full worksheet. [On the otherhand, it may just be my computer, but it's a P-II 350]. No screen updating actually takes place as this code is run from another worksheet and it does not activate the sheets being modified. Here's the code:

    Private Sub cmdUpdateHeaders_Click()

    LHeader = Worksheets("Titles").Range("B8").Text + " " + Worksheets("Titles").Range("B9").Text
    RHeader = Worksheets("Titles").Range("B6").Text + " #" + Worksheets("Titles").Range("B7").Text + " - " + Worksheets("Titles").Range("B10").Text

    With Sheets("Low Peaks").PageSetup
    .LeftFooter = "Prepared by: My Name"
    .RightFooter = Format(Date, "mmmm d, yyyy")
    .LeftHeader = LHeader
    .RightHeader = RHeader
    .TopMargin = 48
    .BottomMargin = 48
    .LeftMargin = 27
    .RightMargin = 27
    .HeaderMargin = 27
    .FooterMargin = 27
    End With

    With Sheets("High Peaks").PageSetup
    .LeftFooter = "Prepared by: My Name"
    .RightFooter = Format(Date, "mmmm d, yyyy")
    .LeftHeader = LHeader
    .RightHeader = RHeader
    .TopMargin = 48
    .BottomMargin = 48
    .LeftMargin = 27
    .RightMargin = 27
    .HeaderMargin = 27
    .FooterMargin = 27
    End With

    Sheets("Titles").Activate
    End Sub

    Thanks for any tips provided.

    Drew

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very Slow Macro execution!! (Excel 97)

    For reasons known only to Microsoft, setting some of the PageSetup properties is extremely slow. Setting the margins is one of those that is slow. I have never tried this, and don't even know how to do it, but I have heard that the only way around this problem is to convert the part of the code that does the PageSetup to an Excel4 macro, and then use the ExecuteExcel4Macro method to run the code.
    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very Slow Macro execution!! (Excel 97)

    One way of doing his might be to have a blank page with all the characteristics set up (perhaps on a different worksheet), and then copy that blank page into your target worksheet. It will bypass the slow margin size setup which Legare refers to.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Very Slow Macro execution!! (Excel 97)

    I think the last solution is the recommended one. I have had problems in Excel 97 with this before and I seem to remember that it boils down to a problem with a memory leak that is triggered by repeatedly setting properties of the PageSetup object in VBA (haven't got the KB article any more).

    If you experimented with a new file containing about 20 sheets and used your macro to try to set PageSetup for all of them, you might even find that your macro eventually crashes with an 'out of memory error!!

    Jeremy

Posting Permissions

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