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.