Sharing VBA procedures; alternative to personal.xls?

[curiousgeorge408]

I have a workbook with a set of VBA procedures (subs and functions).
I want to use the same procedures in another (new) workbook. I know
that I could cut-and-paste into the VBA editor. I also know that I
could put the shared procedures into personal.xls.

I believe there is a "better" way -- at least, a way that I prefer,
and a way that I have used in the past without problems. This has
been explained in these newsgroups, but I cannot find the
explanation. And what I remember is not working for me.

As I recall, all I need to do is open the original workbook that
contains the VBA procedures in the same Excel instance in which I open
the new workbook (File / New / Blank Workbook).

That does not work for me now, regardless of whether I open the
original workbook before or after creating the new workbook.

What am I doing wrong?

Oh, I __can__ access the subs in the original workbook. Just not the
functions.

Does this method of sharing VBA procedures work only for subs? Is
there a special way that I must declare the functions for this method
of sharing to work?

I have declared the functions simply as "function foobar(arglist) as
type".

I notice that the module that contains the VBA procedures is in one
VBA project named for the original workbook, whereas the new workbook
is in a separate VBA project. Is that the problem? Or is that
correct, namely that each workbook is a separate VBA project?

Bottom line: How can I share VBA procedures -- functions as well as
subs -- between workbooks, ideally without using personal.xls?

FYI, I am using Office Excel 2003.

 

[Ron de Bruin]

Save the workbook as Add-in

See this small example

Debra Dalgleish's (Toolbar example from Dave Peterson)
http://www.contextures.com/xlToolbar02.html

 

[curiousgeorge408]

Save the workbook as Add-in
See this small example
Debra Dalgleish's (Toolbar example from Dave Peterson)
http://www.contextures.com/xlToolbar02.html

Thanks for the pointer. It may prove useful someday. But I would
prefer not to go the Add-In route, if I can avoid it.

The Option Private Module help page explains: "Option Private is only
useful for host applications that support simultaneous loading of
multiple projects and permit references between the loaded projects.
For example, Microsoft Excel permits loading of multiple projects and
Option Private Module can be used to restrict cross-project
visibility."

Conversely, it would seem that a public moodule is
"visible" (accessible?) across projects (workbooks?). Since there is
no Option Public Module statement, I ass-u-me that modules are public
by default.

Note: I did not specify either Public or Private for each VBA
procedure in the one workbook. My understanding is that they are
public by default.

If all that is true, why can't I access functions (per se) across
"projects" (i.e. workbooks)?

Oddly, I can execute sub(routine)s across projects (workbooks) from
Excel, but I cannot call subs in one project from another project.
Why not?

I cannot help but think that my difficulty is a user error (mine). I
hope someone can tell me what I'm doing wrong.

 

[Gord Dibben]

To use UDF's have you tried =bookname.xls!MyFunction()

Where bookname.xls contains your Functions


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Did you want to use these functions in a cell in a worksheet?

If yes, you could use syntax like:
='my other workbook.xls'!myFunct(a1:a7)

If you want to access the functions in your other project (via code), you have a
couple of choices.

You could add a reference in the "receiving" project that points at the
"sending" project.

First, it's probably better to give the "sending" project a nice unique name.
Open the VBE
Hit ctrl-r to see the project explorer
select your workbook's project.
It should look something like: VBAProject (book1.xls)
Hit F4 to see the properties window
Type the new name in the (Name) property

Save this workbook.

Select the "receiving" project
Tools|References
Look for that nice unique name and select it (check it).

Then you can use the functions in that other workbook just like they're built
into VBA.

Dim Resp as long
resp = myFunct(activesheet.range("a1:A7")

======

The second way to have your code call a function in another workbook's project
is to use Application.run:

application.run "'FullfileName.xls'!macroname", myargument
or to return something:
resp = application.run("'fullfilename.xls"'!macroname, myargument)

 

[curiousgeorge408]

Did you want to use these functions in a cell in a worksheet?
If yes, you could use syntax like:
='my other workbook.xls'!myFunct(a1:a7)

Thanks to you and Gord for this. I shoulda figured this out when I
saw that syntax in the macro menu.