« Making stuff up | Main | Sailing Sailing... »

Working with Modules and Macros

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:

  • References are automatically opened if available.
  • You can call macros in any type of modules including standard modules
  • The macro appears in auto list as a method
  • You can pass arguments

    The disadvantages of this method are:

  • You can not use this method to call a project macro from some other application like vb6, Excel etc.
  • You will get an error if Project1 is renamed, deleted or moved.
    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:

  • It is simple
  • You can call macros in any type of modules including standard modules
  • You can use this method to call project macro from some other application like vb6, Excel etc.

    The disadvantages of this method are:

  • You can not pass arguments
  • The macro does not appear in auto list of methods
  • You need to ensure that source project is open

    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").macro­1

    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:

  • You can use this method to call a ms project macro from other applications like vb6, Excel
  • You can pass arguments

    The disadvantages of this method are:

  • You can not call macros in standard modules
  • The macro does not appear in auto list of methods
  • You need to ensure that source project is open

    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.

  • RELATED POSTS

    Comments (1)

    Patio Furniture:

    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 !!

    Post a comment

    (Comments are moderated to fight SPAM and will be published after I have a chance to approve them. Thanks for waiting.)

    About

    The previous article is Making stuff up.

    The next article is Sailing Sailing....

    Current articles are in the main index page and you can find a complete list of articles in the archives.

    Creative Commons License
    This weblog is licensed under a Creative Commons License.
    Powered by
    Movable Type 3.34