VBA Excel Error with Office 2007

[Pat]

I have created a number of vba macros within Project some of which use
the Excel Object Library. I am using Project 2003 & Office 2003.
Everything works fine as long as the users have both 2003 versions.

The error I am encountering is when users have installed office 2007.
With 2007 obviously comes a different Excel Object Model (12.0 vs.
11.0).

When the users open and save the project and then distribute it back
to other users who still have 2003 for both Project & Office the vba
code no longer works.

When Looking at the References I am being told the 12.0 object model
is missing, of which it should be missing because I only have the 11.0
object (2003).

How do I make the vba work regardless of which object model the users
have on their machines?

 

[Pat]

I probably should have clarified.... The vba code does work for the
users with office 2007. the problem comes when a 2007 user saves the
project file and distributes it to a user who only has office 2003.
Then the vba code no longer works. It is viewable but will not
execute. I even tried re-adding the 11.0 object model of which it
will not allow me to do now.

the vba being executed exists within the project file. my only
thought is to have users copy the code to their global template file
and have it executed from there. then there should be no issues with
office changing the object libraries in the vba code existing within
the project file itself.

Is this the only path?

 

[Rod Gill]

Hi,

This is a loop hole in the VBA design. Take code in a new version of Office
and save to an older version or open with an older version and the object
libraries do not automatically revert to the older copies. If you open an
older file in a newer version they do upgrade automatically.

The solution is to develop using the oldest version needed (2003 in your
case) or as you suggested store the macros in the Global.mpt file.

Access VBA lets you manipulate the references in VBA so it works OK, but I
haven't managed to do the same with project VBA. Hopefully someone else has
and will respond!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com