Call a sub procedure from another workbook

[jbronnum]

I have a number of different workbooks all using the same sub
procedure. I want to put this sub procedure in just one workbook and
call this sub procedure from a number of other workbooks

How can i call a sub procedure from another workbook?

 

[Guest]

hi
the best way i know to do that is to save the file your macro is in to the
xlstart folder as Personal.xls and hide the file. on my pc the xlstart folder
is at
C:\Program files\microsoft office\office 10\xlstart\
your personal.xls file will open in the background when xl starts and all
macros in the personal.xls file will be available to run on any file.
I have a number of macros in my personal xls and run many from a custom menu
or custom icon.

Regards
FSt1

 

[jbronnum]

hi
the best way i know to do that is to save the file your macro is in to the
xlstart folder as Personal.xls and hide the file. on my pc the xlstart folder
is at
C:\Program files\microsoft office\office 10\xlstart\
your personal.xls file will open in the background when xl starts and all
macros in the personal.xls file will be available to run on any file.
I have a number of macros in my personal xls and run many from a custom menu
or custom icon.

Regards
FSt1






- Vis tekst i anførselstegn -

Hi FSt1

Thank you for your answer.

Does this also work when there are multiple users of the workbook?

What commando should i use to call the procedure? Call "procedure
name"?

Johan

 

[Chip Pearson]

There are basically three ways you can go about this. The first is to have
both the workbook that contains the sub and the workbook that needs to use
the sub open and use Application.Run to execute the code. E.g.,

Application.Run "'WorkbookWithSub.xls'!TheSubName"

Pay attention to the apostrophes. They are not necessary if your workbook
name does not contain spaces, but are necessary if the workbook name
contains spaces. In either case, they are harmless.

The second method is to create a reference from the workbook that needs to
use the sub to the workbook that contains the sub. In the workbook that
contains the sub, go to the Tools menu in VBA and choose "VBA Project
Properties". Change the name of the project to something meaningful, e.g.,
MyProject. Then open the workbook that needs to use the sub, go to to the
Tools menu in VBA, choose References, and select and check "MyProject" or
whatever you named the project. With this reference in place, you can call
the sub as if it resided in the same workbook. If there is the possibility
of a name collision (two subs with the same name), you can prefix the sub
name with the library name:

MyProject.MySub

Finally, you could put the sub into an Add-In and load that add-in via the
Add-Ins dialog on the Tools menu in Excel. With the add-in loaded, you can
call the sub directly as if it existed in the same workbook. Again, you can
avoid name collisions by including the project name of the add-in.

MyAddInProject.MySub


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

 

[jbronnum]

There are basically three ways you can go about this. The first is to have
both the workbook that contains the sub and the workbook that needs to use
the sub open and use Application.Run to execute the code. E.g.,

Application.Run "'WorkbookWithSub.xls'!TheSubName"

Pay attention to the apostrophes. They are not necessary if your workbook
name does not contain spaces, but are necessary if the workbook name
contains spaces. In either case, they are harmless.

The second method is to create a reference from the workbook that needs to
use the sub to the workbook that contains the sub. In the workbook that
contains the sub, go to the Tools menu in VBA and choose "VBA Project
Properties". Change the name of the project to something meaningful, e.g.,
MyProject. Then open the workbook that needs to use the sub, go to to the
Tools menu in VBA, choose References, and select and check "MyProject" or
whatever you named the project. With this reference in place, you can call
the sub as if it resided in the same workbook. If there is the possibility
of a name collision (two subs with the same name), you can prefix the sub
name with the library name:

MyProject.MySub

Finally, you could put the sub into an Add-In and load that add-in via the
Add-Ins dialog on the Tools menu in Excel. With the add-in loaded, you can
call the sub directly as if it existed in the same workbook. Again, you can
avoid name collisions by including the project name of the add-in.

MyAddInProject.MySub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consultingwww.cpearson.com
(email on the web site)







- Vis tekst i anførselstegn -

Hi Chip

Thank you veruy much

I will try it out

Best regards

Johan