Very Slow Macro execution!! (Excel 97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote