Macro for page setup of worksheets is slow

[jayray]

I have a macro (a standard for each ws in worksheets loop) that goes
through each sheet in a workbook and sets the page setups. Each setup
is nothing unusual, just the margins, orientation, zoom, black and
white, etc., whatever is on the Excell Page Setup menu. I've always
found that this sequence is slow, with each page taking more than a
second, even if I turn the ScreenUpdating off. Is there anything I can
do to speed up the sequence?

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/udfs/pagesetup.html

 

[Don Guillett]

Page setup macros ARE slow. Change ONLY what is necessary, not everything
OR, better yet use a Template sheet with the page setup done and copy that
one before putting in data.

 

[jayray]

Page setup macros ARE slow. Change ONLY what is necessary, not everything
OR, better yet use a Template sheet with the page setup done and copy that
one before putting in data.

--
Don Guillett
SalesAid Software





- Show quoted text -

Thanks for reply, and especially to Mr. McGimpsey on the Excel4 macro
approach.

 

[jayray]

Page setup macros ARE slow. Change ONLY what is necessary, not everything
OR, better yet use a Template sheet with the page setup done and copy that
one before putting in data.

--
Don Guillett
SalesAid Software





- Show quoted text -

I tried this approach, copying the text into VBE directly from the
website, and trimmed down some of the parameters (my Excel 2003 seemed
to think there were too many continuations), being careful as I did it
that I deleted both the Optional entry and the concatenated string
within the pgSetUp string. However, I find that the macro
PageSetupXL4M is not identified in the macro run form (Alt+F8), so I
cannot launch it. What am I doing wrong?

 

[JE McGimpsey]

However, I find that the macro PageSetupXL4M is not identified in the
macro run form (Alt+F8), so I cannot launch it. What am I doing
wrong?

You're not doing anything wrong. Since the macro has arguments, it won't
appear in the macro run dialog (since there's no way to supply the
arguments).


You can either rewrite the macro to hardcode the arguments, or else call
the macro with the appropriate arguments.

 

[Dave Peterson]

But I can use alt-f8 if I want to type in the name of the procedure and the
parms that I want to pass:

'PageSetupXL4M "qwer"'

Passes "qwer" to the first parm (lefthead in JE's code).

(but this would become unmanageable very quickly, well for me, at least.

JE:

I'm using xl2003 (wintel) and when I copied your code into a new workbook's
general module, I got a "too many line continuations" error in the declaration
section.

I'm not sure if this is a new problem, or if I never stole this from your site
<vbg>.

 

[gimme_this_gimme_that]

The "Excel" way of thinking is that you should start with a Template
XL sheet.

That way you don't have to make any margin assignments at all. All you
have to do is load the template. You also don't have to set the
margins everytime you start on your project.

A template is an ordinary Excel Workbook that has some formatting in
it. It can be an empty workbook with custom margins, or you can have a
full flung Excel Workbook with lots of VBA just waiting for new data
and a button click to bring it up to date.

Templates are stored in the Application.TemplatesPath. directory.
Peruse it.

For starters you probably should change your default template which
comes with about an inch marchine on the left-top-bottom-right (a
funky incredible waste of paper) and change all the margins to 0.25
inches.

If your new default isn't applicable to your new application, create a
Template specifically for it.

 

[JE McGimpsey]

JE:

I'm using xl2003 (wintel) and when I copied your code into a new workbook's
general module, I got a "too many line continuations" error in the declaration
section.

I'm not sure if this is a new problem, or if I never stole this from your site
<vbg>.

Dunno either - I revised it to get rid of some of the continuations,
however...

 

[jayray]

You're not doing anything wrong. Since the macro has arguments, it won't
appear in the macro run dialog (since there's no way to supply the
arguments).

You can either rewrite the macro to hardcode the arguments, or else call
the macro with the appropriate arguments.

Could you show an example (just use two parameters for Zoom and
BlackAndWhite, for example) of how to write a calling macro that
passes the parameters to the XL4 code?

 

[JE McGimpsey]

One way:

Public Sub try()
PageSetupXL4M Zoom:=125, BlackAndWhite:=True
End Sub

 

[jayray]

But I can use alt-f8 if I want to type in the name of the procedure and the
parms that I want to pass:

'PageSetupXL4M "qwer"'

Passes "qwer" to the first parm (lefthead in JE's code).

(but this would become unmanageable very quickly, well for me, at least.

JE:

I'm using xl2003 (wintel) and when I copied your code into a new workbook's
general module, I got a "too many line continuations" error in the declaration
section.

I'm not sure if this is a new problem, or if I never stole this from your site
<vbg>.


For the too many continuations errors message, just delete the
continuations so that every two lines become one:

sPgSetup = "PAGE.SETUP(" & sHead & c & sFoot & c & _
LeftMarginInches & c & RightMarginInches & c & _

becomes

sPgSetup = "PAGE.SETUP(" & sHead & c & sFoot & c & LeftMarginInches &
c & RightMarginInches & c & _

Or delete more and make each line into a longer line...

You can do the same for the continuations in the pgSetup
concatenations, but don't delete the parameter strings as the order
there seems to define how the XL4 macros read the parameters.

 

[Dave Peterson]

Actually the too many continuations error was in the proc delcaration--not in
the code itself.

But my note to JE was essentially a request for him to update his web page.
That way, anyone who referred to that page wouldn't have to make the same fix.