XL 2000 Call Macro Frm Anthr WrkBk?

Chris

unread,

Mar 24, 2001, 2:24:46 AM3/24/01

to

If I have two workbooks open, how can I execute a macro from one workbook to
the other?

Thanks,
-Chris

Rob Bovey

unread,

Mar 24, 2001, 4:13:56 AM3/24/01

to

Hi Chris,

There are two ways to do this. You can either add a reference to the
VBAProject of the workbook containing the macro you want to call and then
call it directly, or you can use the Excel Application object's Run method.
I don't like references myself, so I always use the latter. Note that in
either case the macro must be public (e.g. it can't be preceeded by the
Private qualifier and it can't reside in a module that contains the Option
Private Module directive). The workbook containing the macro must also be
open.

For a simple subroutine with no arguments:

Application.Run "Book1.xls!MacroName"

For a subroutine with a String argument and a Long argument:

Application.Run "Book1.xls!MacroName", "Arg1", 2

For a function with arguments that returns a Boolean value:

bReturn = Application.Run("Book1.xls!MacroName", "Arg1", 2)

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

"Chris" <MsVb...@hotmail.com> wrote in message
news:2ZXu6.5910$ue1.5...@newsread2.prod.itd.earthlink.net...

Chris

unread,

Mar 24, 2001, 3:08:04 PM3/24/01

to

Rob,

This is really a big help and clarifies a lot!

Thank you,
-Chris
"Rob Bovey" <Rob_...@msm.com> wrote in message
news:eRBPdLEtAHA.2108@tkmsftngp04...

David McRitchie

unread,

Mar 25, 2001, 11:30:19 AM3/25/01

to

What are differences between
Application.Run
Call
just using macroname

Macroname
Call [Book1.xls].MacroName("Arg1", 2)

David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

----- Original Message -----
"Rob Bovey" <Rob_...@msm.com> wrote in message
news:eRBPdLEtAHA.2108@tkmsftngp04

Newsgroups: microsoft.public.excel.programming
Sent: Saturday, March 24, 2001 4:13 AM
Subject: Re: XL 2000 Call Macro Frm Anthr WrkBk?

> There are two ways to do this. You can either add a reference to the
> VBAProject of the workbook containing the macro you want to call and then
> call it directly, or you can use the Excel Application object's Run method.
> I don't like references myself, so I always use the latter. Note that in
> either case the macro must be public (e.g. it can't be preceeded by the
> Private qualifier and it can't reside in a module that contains the Option
> Private Module directive). The workbook containing the macro must also be
> open.
>
> For a simple subroutine with no arguments:>
> Application.Run "Book1.xls!MacroName"
>
> For a subroutine with a String argument and a Long argument:>
> Application.Run "Book1.xls!MacroName", "Arg1", 2
>
> For a function with arguments that returns a Boolean value:>
> bReturn = Application.Run("Book1.xls!MacroName", "Arg1", 2)>
>

Chip Pearson

unread,

Mar 25, 2001, 3:57:42 PM3/25/01

to

> What are differences between
> Application.Run
> Call
> just using macroname

"Application.Run" is sort of the equivalent of "Late Binding". The name and
location of the macro, and its data types, are resolved at run time.
Therefore, macro does not need to exist, or its workbook need to be open, at
compile time. It is really independent of VBA and the compiler. It has
handled more or less independently by Excel, not VBA. There is no need for
a reference from one project to another. Since everything is handled at run
time, you can specify the macro to run in a variable, whose contents are
specified at run time.

I don't believe there are any substantial differences between using the
"Call" statement and just specifying the name (other than some syntax
differences). I think that "Call" is a leftover from earlier versions of
the language, and is supported for backwards compatibility. When you
directly reference a procedure (with or without the "Call" statement) you
are doing the equivalent of "early binding". The references to the
procedure are resolved at compile time.

I haven't done any formal testing, but I think it is a fairly safe bet that
Application.Run requires a fair amount of overhead at run time. You would
likely see a substantial performance hit with Application.Run. Probably
something on the order of declaring an object "As Object" rather than "As
class_name".

CallByName is the VBA equivalent of "Application.Run" for objects. This
lets you call specific properties or methods of an object, but specify which
property or object at run time.

Application.Run is more flexible but you pay a price in performance.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"David McRitchie" <DMcRi...@msn.com> wrote in message
news:uS4ABnUtAHA.1436@tkmsftngp03...