Personal.xls Macros

Mark

unread,

Dec 7, 2003, 4:38:06 PM12/7/03

to

Over the past year or so, I have built several macro
libraries including two that Excel applications use. I
have 50 or so spreadsheets that use one or more macros
from these libraries. Presently, the master copies of the
Excel-relevant modules are stored in Personal.xls and are
declared "public". When I try to call/refer to the macros
in Personal.xls from another open spreadsheet, I get an
error indicating that the referenced subroutine/function
is not defined. I have tried adding the name of
the "owning" spreadsheet, i.e., Personal.xls to the call
(as is shown in the Macro List tool), but that
generates "compiler" errors. The only way to make it work
is to use the Application.Run command. But you can not
pass arguements using that approach.
So, I copy entire modules of specific macros into the
application as I am building it. This works but it is
cumbersome and requires nontrivial bookkeeping to ensure
that an application is running the most recent library. Is
there a simplier way to manage this stuff. For example, is
there a way to have the application simply "fetch" or
refer to the macro from Personal.xls at runtime?
ANy help would be appreciated.

Bob Phillips

unread,

Dec 7, 2003, 4:49:27 PM12/7/03

to

"Mark" <anon...@discussions.microsoft.com> wrote in message
news:015e01c3bd0a$66854b00$a401...@phx.gbl...

> The only way to make it work
> is to use the Application.Run command. But you can not
> pass arguements using that approach.

Application.Run "MYCUSTOM.XLM!My_Func_Sum", 1, 5

pfsar...@yahoo.com.nospam

unread,

Dec 7, 2003, 5:01:19 PM12/7/03

to

Here are some methods for calling macros.

Syntax 1 and Syntax 2 assume that the macros are either in the same
WorkBook or that there is a Reference to the WorkBook containing the
macros.

To set a reference to another WorkBook : If the WorkBook containing
the macro to run is not open, open the WorkBook. Alt-F11 to access
VBE. Ctrl-R to activate Project Explorer. Select the project of the
macro that does the calling. Tools > References. Check the name of the
project that contains the called macros.

Syntax 1

Call MacroName ' No parameters
Call MacroName (param1, param2) ' Parameters

Syntax 2

MacroName ' No parameters
MacroName param1, param2 ' Parameters

Syntax 3 assumes that the WorkBook contaning the called macros is
open, but that there are no References to that WorkBook.

Application.Run "'FileName.xls'!MacroName"
' No parameters

Application.Run "'FileName.xls'!MacroName", param1, param2
' Parameters

Syntax 4 assumes that the WorkBook contaning the called macros is not
open.

Application.Run "'" & "PathName" & "FileName.xls" & "'!MacroName"
' No parameters

Application.Run "'" & "PathName" & "FileName.xls" & "'!MacroName",
param1, param2
' Parameters

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

Mark

unread,

Dec 7, 2003, 6:03:07 PM12/7/03

to

Your suggestion to reference the Personal.xls file (VBA
editor/Tools/Reference) works exactly as I want. The only
thing I had to do was change the name of VBAProject
associated with Personal.xls from "VBAProject" which is
ambiguous to something that is not. And that is a one
time change. Thanks a lot

>.
>