Automation Question

[Mark]

I have developed a macro which can be run two ways:

1. The usual way: Open an Excel application, open the workbook (call it
myWB) and run the macro (call it myMacro).
2. The automation way:
From a second workbook (call it MasterWB), create a new Excel application
via automation, open the workbook (myWB) in the new application and run the
macro (myMacro).

Here's the issue that I'm having - the macro runs faster when run the "usual
way" than the "automation way". I don't get it.

Anybody have an idea why this happens?

Thank you,
Mark




I open an Excel Application and workbook via automation.

So there are two Excel Applications open at the same time. The first Excel
Application opens the second Excel Application and a workbook.

A macro in the workbook of the second Excel Application is run.

What I notice is the following:
The macro runs slower when run via the automation

 

[Jim Rech]

When I run Excel via automation I find that the macros run at normal speed
as long as that instance of Excel is in the foreground. This puts it in the
foreground: XL.Visible = True

This presumably is due to how Windows doles out processor time among
applications. If you need to keep the Excel instance hidden you might have
to live with slower speeds. As you may know, you can set the priority of a
running application using Task Manager; I don't know how you can set it
programmatically (or how much it would improve performance if you could).

--
Jim Rech
Excel MVP
|I have developed a macro which can be run two ways:
|
| 1. The usual way: Open an Excel application, open the workbook (call it
| myWB) and run the macro (call it myMacro).
| 2. The automation way:
| From a second workbook (call it MasterWB), create a new Excel application
| via automation, open the workbook (myWB) in the new application and run
the
| macro (myMacro).
|
| Here's the issue that I'm having - the macro runs faster when run the
"usual
| way" than the "automation way". I don't get it.
|
| Anybody have an idea why this happens?
|
| Thank you,
| Mark
|
|
|
|
| I open an Excel Application and workbook via automation.
|
| So there are two Excel Applications open at the same time. The first
Excel
| Application opens the second Excel Application and a workbook.
|
| A macro in the workbook of the second Excel Application is run.
|
| What I notice is the following:
| The macro runs slower when run via the automation
|
|
|
|
|

 

[K Dales]

Don't think this is really surprising, since you have a lot more overhead
when you run it via automation. For one thing, you are taxing your
computer's processor and memory more just by having two Excel sessions going
at once. But also, when run in automation, Windows has to do a lot more than
just run the macro: it needs to keep an eye on that new Excel session, see if
any events have been triggered, etc.

But that is a natural trade-off: by getting the computer to do more for you
it also makes it run slower. You need to decide if the tradeoff is
worthwhile or no.

 

[Mark]

Thanks for your comments.

I have not had a chance to try the "XL.visible = true " yet, but will.

I may also try NOT to run the macro through automation, but in the same
session. While automation is critical to other parts of the process, it is
not so critical withrunning this particualr macro.

Mark