Why macro slow on one, fast on another?

[Guest]

Hi All.......

I am working on a program that will generate some 37 Charts. I have set up
each chart with it's own macro so it can be run individually, or of course
they can all be run from a master macro. In order to be able to control the
print-attributes on a global basis and not have to include them all in every
macro, I have made them into Submacros which are called by each Chart macro.
Now here's my situation.

At work I have a machine thats about 800mhz, 256MB ram, one monitor, Win98,
XL97, network lazer printer....and on it the macros were created and all run
fine and relatively fast. At home, I have a 1.2ghz machine with 512MB ram,
two monitors, WinME, XL97, local HP inkjet printer....and the macros run fine
until it gets into this particular Submacro, then it runs incredably
SLOW!!!.........Anyone have any idea what might be causing the difference in
performance,(NOT doing the printout, just generating the chart), and what I
could do about it? I am concerned because others will use this program and I
know not what their machines might be.


Here's the Submacro where the slowdown occurs: if I REM it out, all runs
faster.

Sub MarginsAndFooterAttributes()
'====================================================
'This macro is called from each individual Chart Macro, it affords
'global control of the Margins and Footer attributes
'====================================================
With ActiveChart.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "Paula, X6333"
.CenterFooter = ""
.RightFooter = "&D" 'injects current Date, &T injects time
.LeftMargin = Application.InchesToPoints(0.2)
.RightMargin = Application.InchesToPoints(0.22)
.TopMargin = Application.InchesToPoints(0.33)
.BottomMargin = Application.InchesToPoints(0.39)
.HeaderMargin = Application.InchesToPoints(0.17)
.FooterMargin = Application.InchesToPoints(0.18)
.ChartSize = xlFullPage
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.BlackAndWhite = False
.Zoom = 100
End With

End Sub

Any ideas or thoughts would be much appreciated
TIA
Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

PageSetup utilizes the printdriver for each line (property). Some print
drivers are faster than others. Apparently, the one you have at home is
slow.

The usual suggestions are to minimize the number of settings you make -
don't set items that use the default value.

You can also look at using the xl4 macro command as suggested by John Green:

=================================
From: John Green ([email protected])
Subject: Re: About PageSetup..
Newsgroups: microsoft.public.excel.programming
View complete thread (10 articles)
Date: 2001-01-22 12:57:23 PST




PageSetup in VBA has always been a painfully slow process. If you can't
avoid having
to set these parameters, you can use the Excel 4 macro function, PAGE.SETUP
to carry
out most of the PageSetup operations much more quickly. The following two
macros are
almost equivalent, and should give you the clues you need to start using
PAGE.SETUP.
You can download a full description of all the Excel 4 macro functions from
Microsoft's web site:

Sub PS()
ActiveSheet.DisplayPageBreaks = False
With ActiveSheet.PageSetup
.LeftHeader = "My Company"
.CenterHeader = ""
.RightHeader = "&D / &T"
.LeftFooter = "Highly Confidential and Proprietary"
.CenterFooter = ""
.RightFooter = "Finance"
.LeftMargin = Application.InchesToPoints(0.54)
.RightMargin = Application.InchesToPoints(0.3)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.36)
.HeaderMargin = Application.InchesToPoints(0.22)
.FooterMargin = Application.InchesToPoints(0.17)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
' .PrintQuality = 600 ' does not work with all the printers
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub

Sub PS4()
head = """&LMy Company&R&D / &T"""
foot = """&LHighly Confidential and Proprietary&RFinance"""
pLeft = 0.54
pRight = 0.3
Top = 0.4
bot = 0.36
head_margin = 0.22
foot_margin = 0.17
hdng = False
grid = False
notes = False
quality = ""
h_cntr = False
v_cntr = False
orient = 2
Draft = False
paper_size = 1
pg_num = """Auto"""
pg_order = 1
bw_cells = False
pscale = True
pSetUp = "PAGE.SETUP(" & head & "," & foot & "," & pLeft & "," & pRight &
","
pSetUp = pSetUp & Top & "," & bot & "," & hdng & "," & grid & "," & h_cntr
& ","
pSetUp = pSetUp & v_cntr & "," & orient & "," & paper_size & "," & pscale
& ","
pSetUp = pSetUp & pg_num & "," & pg_order & "," & bw_cells & "," & quality
& ","
pSetUp = pSetUp & head_margin & "," & foot_margin & "," & notes & "," &
Draft & ")"

Application.ExecuteExcel4Macro pSetUp
End Sub

John Green (Excel MVP)
Sydney
Australia

KeepItCool suggested one way to do multiple sheets at once, if the
activesheet has the setting you want:

Sheets(Array("Sheet1", "Sheet3")).Select
Sheets("Sheet1").Activate
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show

 

[Guest]

Allll-righty then.......thank you very much kind Sir.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Tom,

I like CLR was finding that it took a long time to undertake the Page Setup
things. I have 13 sheets that need to have the set up done and therefore
decided to go for your suggestion.

However when I run the macro I get :

Runtime error 1004, the formula you typed contains an error... When I select
debug, the line that is highlighed is "Application.ExecuteExcel4Macro pSetUp"

Can you offer any help?

The following is the code I have:-

If ActiveSheet.Name <> "MAIN" Then
For Each wks In ActiveWorkbook.Worksheets
With wks
head = """"""
foot = "&a""Page &P of &N"""
pLeft = 0.5
pRight = 0.5
Top = 0.5
bot = 1#
head_margin = 0.5
foot_margin = 0.5
hdng = False
grid = True
notes = False
quality = ""
h_cntr = False
v_cntr = False
orient = 2
Draft = False
paper_size = 1
pg_num = """Auto"""
pg_order = 1
bw_cells = False
pscale = True
pSetUp = "PAGE.SETUP(" & head & "," & foot & "," & pLeft & "," &
pRight & ","
pSetUp = pSetUp & Top & "," & bot & "," & hdng & "," & grid & "," &
h_cntr & ","
pSetUp = pSetUp & v_cntr & "," & orient & "," & paper_size & "," &
pscale & ","
pSetUp = pSetUp & pg_num & "," & pg_order & "," & bw_cells & "," &
quality & ","
pSetUp = pSetUp & head_margin & "," & foot_margin & "," & notes &
"," & Draft & ")"
Application.ExecuteExcel4Macro pSetUp
End With
Next wks
End If

 

[Guest]

Tom,

I like CLR was finding that it took a long time to undertake the Page Setup
things. I have 13 sheets that need to have the set up done and therefore
decided to go for your suggestion.

However when I run the macro I get :

Runtime error 1004, the formula you typed contains an error... When I select
debug, the line that is highlighed is "Application.ExecuteExcel4Macro pSetUp"

Can you offer any help?

The following is the code I have:-

If ActiveSheet.Name <> "MAIN" Then
For Each wks In ActiveWorkbook.Worksheets
With wks
head = """"""
foot = "&a""Page &P of &N"""
pLeft = 0.5
pRight = 0.5
Top = 0.5
bot = 1#
head_margin = 0.5
foot_margin = 0.5
hdng = False
grid = True
notes = False
quality = ""
h_cntr = False
v_cntr = False
orient = 2
Draft = False
paper_size = 1
pg_num = """Auto"""
pg_order = 1
bw_cells = False
pscale = True
pSetUp = "PAGE.SETUP(" & head & "," & foot & "," & pLeft & "," &
pRight & ","
pSetUp = pSetUp & Top & "," & bot & "," & hdng & "," & grid & "," &
h_cntr & ","
pSetUp = pSetUp & v_cntr & "," & orient & "," & paper_size & "," &
pscale & ","
pSetUp = pSetUp & pg_num & "," & pg_order & "," & bw_cells & "," &
quality & ","
pSetUp = pSetUp & head_margin & "," & foot_margin & "," & notes &
"," & Draft & ")"
Application.ExecuteExcel4Macro pSetUp
End With
Next wks
End If