Hi all,

How to reference a macro defined in another workbook?

Clara
--
thank you so much for your help

Clara,

This runs the macro 'Test' in Book1.xls in the root of C

Sub runit()
Set wbTarget = Workbooks.Open("c:\book1.xls")
Application.Run (wbTarget.Name & "!test")
End Sub

You would probably want to include eror checking.

Mike

"clara" wrote:

Hi all,

How to reference a macro defined in another workbook?

Clara
--
thank you so much for your help

Hi Mike,

Thank you for your help! I think you give me one method, but I still want to
know how to add a reference entry in the VBE project property window.For
example, if I open book1 and book2, how can I add a reference to book1 in
book2' VBA Project propery window.

Clara
--
thank you so much for your help


"Mike H" wrote:

Clara,

This runs the macro 'Test' in Book1.xls in the root of C

Sub runit()
Set wbTarget = Workbooks.Open("c:\book1.xls")
Application.Run (wbTarget.Name & "!test")
End Sub

You would probably want to include eror checking.

Mike

"clara" wrote:

Hi all,

How to reference a macro defined in another workbook?

Clara
--
thank you so much for your help

Clara,

Open the workbook to which you want to set a reference. In VBA, go to the
Tools menu and choose "VBAProject Properties". In that dialog, change the
project name from "VBAProject" to something more meaningful, such as
"MyProcedures". Close that dialog and then save, but don't close, the
workbook. Then open the workbook that will reference MyProcedures. Go to the
Tools menu, choose References, and select "MyProcedures" in the list of
references. Note that VBA References use the VBProject name, not the file
name. "MyProcedures" is the project name, not the file name. The actual file
name is irrelevant.

Once the reference is in place, you can call procedures in the MyProcedures
project as if they existed the same workbook from which they are called. If
there is a possibility of a name collision (two procedures with the same
name residing in separate projects), you can qualify the procedure name with
the project name to specify which procedure to call. E.g.,

Result = MyProcedures.MyFunction(123)
' rather than
Result = MyFunction(123)

Note that you cannot close the MyProcedures workbook if there are other
workbooks open that reference that workbook.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"clara" wrote in message
...
Hi Mike,

Thank you for your help! I think you give me one method, but I still want
to
know how to add a reference entry in the VBE project property window.For
example, if I open book1 and book2, how can I add a reference to book1 in
book2' VBA Project propery window.

Clara
--
thank you so much for your help


"Mike H" wrote:

Clara,

This runs the macro 'Test' in Book1.xls in the root of C

Sub runit()
Set wbTarget = Workbooks.Open("c:\book1.xls")
Application.Run (wbTarget.Name & "!test")
End Sub

You would probably want to include eror checking.

Mike

"clara" wrote:

Hi all,

How to reference a macro defined in another workbook?

Clara
--
thank you so much for your help

Is there a way to do this thru a procedure?
--
Cheers,
Ryan


"Chip Pearson" wrote:

Clara,

Open the workbook to which you want to set a reference. In VBA, go to the
Tools menu and choose "VBAProject Properties". In that dialog, change the
project name from "VBAProject" to something more meaningful, such as
"MyProcedures". Close that dialog and then save, but don't close, the
workbook. Then open the workbook that will reference MyProcedures. Go to the
Tools menu, choose References, and select "MyProcedures" in the list of
references. Note that VBA References use the VBProject name, not the file
name. "MyProcedures" is the project name, not the file name. The actual file
name is irrelevant.

Once the reference is in place, you can call procedures in the MyProcedures
project as if they existed the same workbook from which they are called. If
there is a possibility of a name collision (two procedures with the same
name residing in separate projects), you can qualify the procedure name with
the project name to specify which procedure to call. E.g.,

Result = MyProcedures.MyFunction(123)
' rather than
Result = MyFunction(123)

Note that you cannot close the MyProcedures workbook if there are other
workbooks open that reference that workbook.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"clara" wrote in message
...
Hi Mike,

Thank you for your help! I think you give me one method, but I still want
to
know how to add a reference entry in the VBE project property window.For
example, if I open book1 and book2, how can I add a reference to book1 in
book2' VBA Project propery window.

Clara
--
thank you so much for your help


"Mike H" wrote:

Clara,

This runs the macro 'Test' in Book1.xls in the root of C

Sub runit()
Set wbTarget = Workbooks.Open("c:\book1.xls")
Application.Run (wbTarget.Name & "!test")
End Sub

You would probably want to include eror checking.

Mike

"clara" wrote:

Hi all,

How to reference a macro defined in another workbook?

Clara
--
thank you so much for your help

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?