You can add a reference from one workbook to another via code. Suppose
you have a workbook named "HasFunction.xls" with a project name of
"projHasFunction" and contains a function named "AddThem". Now,
suppose you have a second workbook named "WantsFunction.xls". With
WantsFunction open (at this point it doesn't matter if HasFunction.xls
is open). In regular code module, use the following to set a reference
from WantsFuncxtion to HasFunxction:

Sub AddRef()
ThisWorkbook.VBProject.References.AddFromFile _
"C:\HasFunction.xls"
End Sub

Obviously, change the workbook name to the correct name. In the Macro
Security options of WantsFuncxtion.xls, you'll need to ensure that
"Trust access to the Visual Basic Project" is checked. Then, run the
code above. It will open the workbook "HasFunction" if it is not
already open.

Once the reference is established, you can call the AddThem functions
(which resides in HasFunction.xls) from code in WantsFunction.xls as
if AddThem resided in WantsFunction:

Dim D As Double
D = AddThem(11,22,33)

If there is any possibility of a name collision (two different
entities havint the same name), you can prefix the call to AddThem
with the library project name of HasFunxction.xls.

Dim D As Double
D = projHasfunction.AddThem(11, 22, 33)

In the future, opening the WantsFunction workbook wil cause the
HasFunxction workbook to open.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Wed, 2 Dec 2009 11:30:01 -0800, Ryan H
wrote:

Is there a way to do this thru a procedure?