Macro for page setup of worksheets is slow
 

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?

Macro for page setup of worksheets is slow
 

See

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

In article . com,
jayray wrote:

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?

Macro for page setup of worksheets is slow
 

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

"jayray" wrote in message
ups.com...
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?

Macro for page setup of worksheets is slow
 

On Jun 6, 12:15 pm, "Don Guillett" wrote:
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
"jayray" wrote in message

ups.com...



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?- Hide quoted text -

- Show quoted text -

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

Macro for page setup of worksheets is slow
 

On Jun 6, 12:15 pm, "Don Guillett" wrote:
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
"jayray" wrote in message

ups.com...



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?- Hide quoted text -

- 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?

Macro for page setup of worksheets is slow
 

In article .com,
jayray wrote:

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.

Macro for page setup of worksheets is slow
 

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.



JE McGimpsey wrote:

In article .com,
jayray wrote:

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

Macro for page setup of worksheets is slow
 

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.

Macro for page setup of worksheets is slow
 

In article ,
Dave Peterson wrote:

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...

Macro for page setup of worksheets is slow
 

On Jun 6, 4:05 pm, JE McGimpsey wrote:
In article .com,

jayray wrote:
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.

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?

Macro for page setup of worksheets is slow
 

One way:

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


In article .com,
jayray wrote:

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?

Macro for page setup of worksheets is slow
 

On Jun 6, 5:52 pm, Dave Peterson wrote:
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.

JE McGimpsey wrote:

In article .com,
wrote:

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

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.

Macro for page setup of worksheets is slow
 

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.

jayray wrote:

On Jun 6, 5:52 pm, Dave Peterson wrote:
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.

JE McGimpsey wrote:

In article .com,
wrote:

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

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