I have developped a VBA application in Excel which automates the process of
setting some part of the footer of newly created workbooks.

The problem I have is that the application becomes inexpectingly slow on
some machines. I identify that the following instruction is responsible for
the lack of performance:

Private Sub InsertClassification(iPageSetup As PageSetup, iLevel As String)
iPageSetup.LeftFooter = kFmt & iLevel & vbLf & iPageSetup.LeftFooter
End Sub

On some machines, it takes up to half a second to be executed, while it is
executed much faster on other.

Any idea ?

Thanks in advance

Alan

Pagesetup is notoriously slow on ALL machines. Some are quicker than others
as the calls to the print driver are more streamlined (HP are awful
generally).

You can use the old XLM commands in VBA which are much quicker. Cannot help
more at this point as I only came across XL at V5 with VBA and do not know
XLM macros

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"alan674" wrote in message
...
I have developped a VBA application in Excel which automates the process of
setting some part of the footer of newly created workbooks.

The problem I have is that the application becomes inexpectingly slow on
some machines. I identify that the following instruction is responsible
for
the lack of performance:

Private Sub InsertClassification(iPageSetup As PageSetup, iLevel As
String)
iPageSetup.LeftFooter = kFmt & iLevel & vbLf & iPageSetup.LeftFooter
End Sub

On some machines, it takes up to half a second to be executed, while it is
executed much faster on other.

Any idea ?

Thanks in advance

Hi Nick,

You can use the old XLM commands in VBA which are much quicker.


I think the following Tom Ogilvy / John Green post supports your
suggestion:


http://tinyurl.com/93q24


---
Regards,
Norman



"Nick Hodge" wrote in message
...
Alan

Pagesetup is notoriously slow on ALL machines. Some are quicker than
others as the calls to the print driver are more streamlined (HP are awful
generally).

You can use the old XLM commands in VBA which are much quicker. Cannot
help more at this point as I only came across XL at V5 with VBA and do not
know XLM macros

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"alan674" wrote in message
...
I have developped a VBA application in Excel which automates the process
of
setting some part of the footer of newly created workbooks.

The problem I have is that the application becomes inexpectingly slow on
some machines. I identify that the following instruction is responsible
for
the lack of performance:

Private Sub InsertClassification(iPageSetup As PageSetup, iLevel As
String)
iPageSetup.LeftFooter = kFmt & iLevel & vbLf & iPageSetup.LeftFooter
End Sub

On some machines, it takes up to half a second to be executed, while it
is
executed much faster on other.

Any idea ?

Thanks in advance

Norman

Thanks for the detail....pre-dates my experience

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Norman Jones" wrote in message
...
Hi Nick,

You can use the old XLM commands in VBA which are much quicker.


I think the following Tom Ogilvy / John Green post supports your
suggestion:


http://tinyurl.com/93q24


---
Regards,
Norman



"Nick Hodge" wrote in message
...
Alan

Pagesetup is notoriously slow on ALL machines. Some are quicker than
others as the calls to the print driver are more streamlined (HP are
awful generally).

You can use the old XLM commands in VBA which are much quicker. Cannot
help more at this point as I only came across XL at V5 with VBA and do
not know XLM macros

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"alan674" wrote in message
...
I have developped a VBA application in Excel which automates the process
of
setting some part of the footer of newly created workbooks.

The problem I have is that the application becomes inexpectingly slow on
some machines. I identify that the following instruction is responsible
for
the lack of performance:

Private Sub InsertClassification(iPageSetup As PageSetup, iLevel As
String)
iPageSetup.LeftFooter = kFmt & iLevel & vbLf & iPageSetup.LeftFooter
End Sub

On some machines, it takes up to half a second to be executed, while it
is
executed much faster on other.

Any idea ?

Thanks in advance

Thank you very much for your help guys !

As I am an old developper, it reminded me a lot of things ! ;-

If I took an other printer than HP, macro works well and fast.
Unfortunately, Excel 4 macro didn't solve the problem with HP printers:
procedure is executed a bit faster but not so much... (still around 5 to 10
seconds for 12 sheets)

An other idea maybe ? I wonder if there were a mean to stop printer driver
operations during macro iterations through worksheets and then to restart it
to make final calculation ?

Alain

"Nick Hodge" wrote:

Norman

Thanks for the detail....pre-dates my experience

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Norman Jones" wrote in message
...
Hi Nick,

You can use the old XLM commands in VBA which are much quicker.


I think the following Tom Ogilvy / John Green post supports your
suggestion:


http://tinyurl.com/93q24


---
Regards,
Norman



"Nick Hodge" wrote in message
...
Alan

Pagesetup is notoriously slow on ALL machines. Some are quicker than
others as the calls to the print driver are more streamlined (HP are
awful generally).

You can use the old XLM commands in VBA which are much quicker. Cannot
help more at this point as I only came across XL at V5 with VBA and do
not know XLM macros

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"alan674" wrote in message
...
I have developped a VBA application in Excel which automates the process
of
setting some part of the footer of newly created workbooks.

The problem I have is that the application becomes inexpectingly slow on
some machines. I identify that the following instruction is responsible
for
the lack of performance:

Private Sub InsertClassification(iPageSetup As PageSetup, iLevel As
String)
iPageSetup.LeftFooter = kFmt & iLevel & vbLf & iPageSetup.LeftFooter
End Sub

On some machines, it takes up to half a second to be executed, while it
is
executed much faster on other.

Any idea ?

Thanks in advance

Maybe change printer driver before doing the setup and then change back
again?

DM Unseen

Change printer driver before doing the setup and then change back
again?

DM Unseen

Change printer driver before doing the setup and then change back
again?

DM Unseen

Change printer driver before doing the setup and then change back
again?

DM Unseen

No, because macro should be generic and we don't know which are the available
printer connected to a specific machine.
In addition I tried to set the ActivePrinter property of Application object
to "" but it seems that it's not possible...

"DM Unseen" wrote:

Maybe change printer driver before doing the setup and then change back
again?

DM Unseen

Alan,

the only other option is to install some printer driver yourself and
use that.

use
http://www.codeguru.com/forum/showth...oto=nextoldest
and
http://www.frogmorecs.com/arts/using...ic_driver.html

DM Unseen