[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Speed up Macro

Posted on 2012-08-17
3
Medium Priority
?
590 Views
Last Modified: 2012-08-17
I have a macro that is running really slow and I think the problem is that I did a poor job of coding it.  I would apperciate any help and also an explianation on how to improve it, so that I can learn from my mistakes.

Sub PrintSetup()
Dim ws As Worksheet
Application.ScreenUpdating = False

  
    For Each ws In ActiveWorkbook.Worksheets
 
    ActiveWindow.DisplayGridlines = False
       If ws.Name <> "Cover" Then
        With ws.PageSetup
            .Orientation = xlLandscape
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .LeftMargin = Application.InchesToPoints(0.4)
            .RightMargin = Application.InchesToPoints(0.4)
            .TopMargin = Application.InchesToPoints(0.4)
            .BottomMargin = Application.InchesToPoints(0.4)
            .HeaderMargin = Application.InchesToPoints(0)
            .FooterMargin = Application.InchesToPoints(0)
            .LeftFooter = "&""Arial,Regular""&8Risk Management Information Delivery / Integrated Data Mart"
            .CenterFooter = ""
            .RightFooter = "&""Arial,Regular""&8&P of &N"
        End With
     End If
        
        If ws.Visible = True Then
            ws.Select
            ActiveWindow.DisplayGridlines = False
            Range("A1:I1").Select
            Selection.Font.Underline = xlUnderlineStyleSingle
        End If
        
        
        On Error Resume Next
        
    Next ws
Application.ScreenUpdating = True
Sheets(1).Select

MsgBox "Done"


End Sub

Open in new window

0
Comment
Question by:montrof
3 Comments
 
LVL 9

Assisted Solution

by:WebDevEM
WebDevEM earned 1000 total points
ID: 38306003
Hi Montrof -

I don't see anything that jumps out as really wrong with that, but I think you might be able to speed it up a little bit by not having it calculate InchesToPoints each time within the loop.  It might not save much time, but if there are lots of worksheets the time saved will increase with each iteration.

Maybe something like this:
Sub PrintSetup()
Dim ws As Worksheet
Dim Pts4 as Single = Application.InchesToPoints(0.4)
Dim Pts0 as Single = Application.InchesToPoints(0.0)

Application.ScreenUpdating = False

  
    For Each ws In ActiveWorkbook.Worksheets
 
    ActiveWindow.DisplayGridlines = False
       If ws.Name <> "Cover" Then
        With ws.PageSetup
            .Orientation = xlLandscape
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .LeftMargin = Pts4
            .RightMargin = Pts4
            .TopMargin = Pts4
            .BottomMargin = Pts4
            .HeaderMargin = Pts0
            .FooterMargin = Pts0
            .LeftFooter = "&""Arial,Regular""&8Risk Management Information Delivery / Integrated Data Mart"
            .CenterFooter = ""
            .RightFooter = "&""Arial,Regular""&8&P of &N"
        End With
     End If
        
        If ws.Visible = True Then
            ws.Select
            ActiveWindow.DisplayGridlines = False
            Range("A1:I1").Select
            Selection.Font.Underline = xlUnderlineStyleSingle
        End If
        
        
        On Error Resume Next
        
    Next ws
Application.ScreenUpdating = True
Sheets(1).Select

MsgBox "Done"


End Sub

Open in new window

0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 38306020
Manipulating the PageSetup object is notoriously slow.  I do not see any obvious ways to significantly increase your performance there.

All I can offer is that, for PageSetup, be very sure to ONLY include those properties that you absolutely have to.  For example, if you can already count on the margins to be correct, then take those lines out of your macro.
0
 
LVL 1

Author Closing Comment

by:montrof
ID: 38306068
Thank you both.  I also turned off automatic calculation and added a statement to only look at visible sheets.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question