I have a hidden workbook that contains many custom functions in several
standard code modules. This hidden workbook is opened at runtime by an AddIn
(also created by me). I need these custom functions to be accessible
(call-able) from within any of the currently open workbooks in the same way
that the 'personal.xls' macro 'workbook' is accessible by all other
workbooks.

Does anyone know how this can be easily done? I want to avoid dynamically
creating a reference to my hidden workbook in all of the other workbooks if
possible.

Thanks in advance

Toby Chittenden

Hello Toby,

Have you considered making your hidden workbook an Add-in? It is stored as
an .xla and can be loaded via the
Tools | Add-ins menu command. Setting that will cause that add-in to be
loaded by every workbook that has specified it as an add-in.  It is still
hidden. A hacker can load it but can't see the code - if you have protected
it.

Chris,

I cannot protect this hidden workbook as I need the user to be able to
insert custom function code modules into it at on the fly at run-time - via
my other AddIn for my system.

I was just wondering how Excel exposes the 'personal.xls' workbook to all
other open workbooks - and hence can I use the same mechanism to expose my
hidden workbook in the same way.

Ta

Toby

Hello Toby,

The personal.xls workbook is stored in the Startup folder. Any workbook
with the .xls extension is opened when Excel loads. If it is not hidden it
becomes visible. If it is hidden it is loaded but doesn't show.

The same will work for your .xls file. You can see more about the topic by
reading the Microsoft Knowledge Base article:
Q240150 - XL2000: How to Use Excel Startup Folders

You can access that from the web with the simple address "mskb 240150"
(without the quotes, and no dot anything.)

Chris et Al.

Unfortunately I don't want to open my hidden workbook everytime Excel starts
up but only when my AddIn is loaded. That said, I still want it to have the
same global scope as the personal.xls workbook. Any ideas how I do this?

Regards

Toby Chittenden

No ideas whatsoever since you specified that the user must be able to add
macros to it at run time.

Chris (et Al.)

I also don't know how to do it which is why I posted to this newsgroup.
However, what I am trying to do is mimic the behaviour of the personal.xls
macro workbook but in a workbook of my own creation. Does anyone know how to
do this... or even if it is possible?

Toby

What happens if you record all the macros in "MyPersonal.xls", and then
load that from the Add-in? You call the macros from another workbook with
MyPersonal!MySub( ). The workbook is available on that machine, but not on
the network. Don't put it in the XLStart folder. That way you don't need to
create a reference to it in all other workbooks. But the calls to it have
to be specific, since you don't have a reference.