Excel PageFormatting Macro

Topic

New Reply

I have a macro that does page formatting for some reports in Excel.

For some reason it runs extremely slow on some machines, and fine on others.

I have tested it on Win 98 and 2K. All machines involved have the same office setup.

4 machines tested: 1 Win 98 & 1 Win 2K run fine, 1 Win 98 & 1 Win 2K run slow. ??

Any help/suggestion will be greatly appreciated.

Thanks,
RL

Reply | Quote

Replies

An observation (not necessarily your solution):

If you have anti-virus software running, you can expect inordinate delays whenever VBA macros start. I have not migrated to Word 2000 and signed projects yet, so don’t know if that bypasses the virus check. Disabling “AutoProtect” (Norton’s) does not help with their NAV 2000 version. Maybe NAV 2001 is better at disabling the macro virus checking. But of course, you must heed the warnings about disabling anti-virus software.

Reply | Quote

Another suggestion…

The Print drivers being used may make a big difference. Are the slow machines using a different printer?

Reply | Quote

When you say “page formatting” do you mean that is is using the PageSetup property to set page formats? If so, using that property on some Operating Systems is VERY slow. I don’t know why, and I unfortunately don’t know any way around it. One thing that does seem to help a little (but not a lot) is to use With/End With to define the PageSetup object like this:

    With ActiveSheet.PageSetup
        .CenterHeader = "My Report"
        .CenterFooter = Format(Date(), "dd mmm yyyy")
    End With

How slow this is seems to be dependant on which OS you are running on, and what the printer is.

Reply | Quote

here’s a sample of the Code being used:
Private Sub myPageSetup()
With ActiveSheet.PageSetup
.CenterHeader = “”
.RightHeader = “&D &T”
.LeftFooter = “”
.CenterFooter = “”
.RightFooter = “Page &P of &N”
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
‘ .PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
End Sub

I have 2 Dell OptiPlex (1Ghz, 256MB ram) machines using the same printer.. one is fine the other is slow.

Reply | Quote

Yes, that is exactly the kind of code that is VERY slow. If those two computers are running the same OS, I don’t know why one would be slow and not the other.

Reply | Quote

I don’t know if you can do this. I’ve had that situation, where it is extremely slow; so, instead of formatting individual sheets, I copied an existing formatted (hidden) sheet onto a new worksheet. That turned out heaps faster.

Differences in speed may be to do with printer drivers?

Reply | Quote

I’ll give that a try.
Also, I found that doing the page formatting with an excel 4 macro command and using the ExecuteExcel4Macro is much faster. I almost hate to do it that way but if it works….

Reply | Quote

I found an old message on CompuServe from Bill Manville on this subject, and the workaround that he recommended was to use an Excel 4 macro. No one seemed to know why this is so slow.

I did think of one thing that could cause one of your machines to be slow and not the other. Check the CMOS setup settings for the LPT port (is that how the printers are attached?) both machines, or the network settings if the printers are attached through a network. I had a situation a while back where a user of one of my applications reported that his printing was very slow. Changing his network from Full Duplex to Half Duplex solved his problem.

Reply | Quote