This morning someone asked how to access code stored in another file. In this case it was the global.mpt file. There are a number of answers to this, but first I want to explain a bit about how code is stored within Project files.
This is a bit complicated because there are a number of different places where the actual code can be stored. Don't fall asleep while I walk through this. The actual code is a procedure which is a named sequence of statements executed as a unit. procedures are commonly refered to as macros. They can come in a few varieties such as Function, Property, and Sub. They can be recognized because they typically start like Sub nameOfMyMacro()
and end with End Sub
or the same but using the Function or Property keyword.
Procedures need to be stored somewhere. To make things manageable they are usually stored within something called a module. I tend to think of a module as an envelope which holds procedures. A module can contain one or more procedures. When you use the organizer you can only move code at the module level.
The other place where procedures can be stored is in the project object. In the VBA object browser you can find it by opening the Project VBE (ALT+F11) then looking in the project explorer and expanding the project objects folder and then double-clicking on "ThisProject". I have an article with more details about "ThisProject" on my website if you want to learn more. It also describes a bit about class modules which I'm not going to go into here.
OK, now back to the real topic:
There are 3 ways to refer to code in other project files. To make things clear lets assume that we want to run "macro1" which is in "module1" in "Project1" and that we are trying to do this from "Project2". If you want to use code from the global.MPT file simply substitute it for Project1.
Using References
Save both projects and ensure that both projects are open. In VBE editor open Project explorer which lists all the projects. By default all vba projects are named as "vbaproject". The global.MPT file is named "ProjectGlobal". If you want to reference ProjectGlobal you don't need to do anything else. If you want to reference another project you will need to change the name to make it unique. To do this select the vbaproject corresponding to project1.mpp, click on properties icon, change the vba project name to vbaproject1. Do the same to project2 but call it vbaproject2.Next select vbaproject2 and go to the Tools menu, select References, you can see vbaproject1 and ProjectGlobal listed in the references dialog box. Set the reference to vbaproject1. Now you can call any macro in vbaproject1 from project2.mpp as follows :
This is Macro1 stored in Project1/Module1
Public Sub Macro1()
MsgBox "Hey!"
End Sub
This is Macro2 which is in some module in Project2
Sub Macro2()
VBAProject1.Module1.Macro1
End Sub
The advantages of this method are:
The disadvantages of this method are:
This is my method of choice when the module is in the global.MPT file.
Using Macro method of Application object
Make sure that Project1 is open and simply call the macro using the macro method:
Application.Macro "Project1.mpp!Module1.Macro1"
The advantages of this method are:
The disadvantages of this method are:
Using the Project object
In this case Macro1 must be in ThisProject module or any class module (to be clear ThisProject IS a special case of a class module. It is not a standard module).
Projects("Project1.mpp").macro1
An alternative is to create a project object for Project1.mpp and use:
Set prj = projects("project1.mpp")
prj.macro1 "hello", "hi"
The advantages of this method are:
The disadvantages of this method are:
To summarize. I prefer the first method, but the other two are valid alternatives, though the third is a bit fussy.
Thanks to Venkata Krishna for pointing these three methods out to me many years ago.
Comments (1)
I appreciate you for hosting such a wonderful site. Your website happens to be not only useful but also very creative too. We find only few professionals who can write not so easy stuff that creatively. A lot of people search for information with regard to a topic like this. I Myself have gone in detail through dozens of websites to come across information regarding this. Keep up the good work !!
Posted by Patio Furniture | January 5, 2010 1:37 PM
Posted on January 5, 2010 13:37