« Office VBA for Mac After 2008 | Main | Project Management Immaturity Model »

Making the move from VBA to VSTO in Microsoft Project

One of the most useful features of Microsoft Project is the ability to automate actions with it. Primarily this is done by writing VBA (Visual Basic for Applications) code, or by just recording a macro and then editing the VBA code that the macro recorder produces. But a few years back Microsoft came out with VSTO (Visual Studio Tools for Office) which allow you to more easily write code to automate office applications within Visual Studio which is their main line programming environment.

For a long time I ignored VSTO because it didn't live up to the promise it made. It was clumsy to use and difficult to deploy the applications. I also avoided it because I couldn't get the project add-in templates to work.

However, with Visual Studio 2008 they provided two things which make it much easier for the casual developer to use. The first is what they call "ClickOnce" deployment. What this means is that you can easily deploy by publishing your solution to a local drive, a website (HTTP) on a CD/DVD or even USB locations. Deployment also supports offline, automatic updating and rollback. The second is an improved template for building a Project Add-in. At the MVP summit there was a lot of talk of using VSTO as a way to automate Project, so I gave it another chance and I like what I found. It took a while to get up to speed, so I decided to post a tutorial showing how to create a simple add-in. Here it is:

Starting a New Add-in

This tutorial assumes you have Microsoft Visual Studio 2008 installed. The first step is to open Visual studio and from the "File" menu, select "New Project...". This brings up the "New Project" dialog box.

01-New-Project-in-VSTO.jpg

In this case I'm using Visual Basic as the language as it is closest to VBA so it is easier for me to deal with. Expand the "Project Type" tree on the left to find "Visual Basic/Office/2007" then enter a name for your solution. If you do not enter a descriptive name now you will regret it later. Highlight "Project 2007 Add-in" and click "OK". The solution will be displayed with two subroutines, one which occurs when the add-in opens and the other when it closes.

thisaddin-vb.jpg

Adding a Form to Your VSTO Solution

In most cases you want the user to be able to do something. That means we need to provide some sort of user interface. In this simple case we are just going to display a user form. To add a form go to the "Project" menu and select "Add Windows Form..."

add-a-windows-form.jpg

In the dialog box that appears you can see all of the other types of objects you can add to your project. Things like the splash screen allow you easily build some commonly used user interface elements. But for now, we need just one form.

add-a-new-item-dialog.jpg

Once you have added the form you will see the form itself in the design window in the center of the screen. You should also see it in the "Solution Explorer" on the upper right side of the screen. The design window is tabbed so you can see the original thisaddin.vb code tab behind the current "Form1.vb" tab.

the-new-form.jpg

The new form is just like a user form in Project VBA. You can edit the Text property to give it a new name in the title bar. Set it however you would like. There are a number of different properties which you can set for font, color, size, behavior etc. Try playing around with some of them. There is one property we really want to set for this form, topmost property should be set to True so that the form always stays visible on top of the application.

Adding Controls to the Form

Now that we have the form we want to put some controls on it. For those who aren't familiar with using forms, a control is typically just something on the form - a label perhaps or a button, textbox or perhaps something more complex like a date picker. The next screenshot shows the common controls in the toolbox. There are many other controls available as well. One of the biggest advantage of using an Add-in rather than a VBA user form is that there are a much wider variety of controls available. The Windows forms also offer standard things like the ability to easily resize the form.

Visual Studio 2008 common controls toolbox vb

To keep this example really simple I am just going to add one button and one label. Clicking on the button will count the tasks in the active project and then put that value in the label. To do this we just select the button control in the toolbox. When it is selected the cursor will change as shown below. Then just click and drag anywhere on the form to create the size and shape of button you want.

adding a button to a windows form.jpg

The button will be created with a name like "Button1". If you are going to change the name of the button, this is the best time to do it. Make sure the button is selected and then in the "Properties" box at the lower right side of the screen find the "Name" property at the top of the list. Edit it to whatever you like. I usually name the button with the action that they are going to perform. This one I call "btnCountTasks". When you have renamed the button, you will notice that the text on the button has not changed. It still reads "Button1". Scroll a bit further down the list of properties and set the "Text" property to "Count Tasks". You can look at some of the other properties while you are there, but to keep things simple don't change any of the other properties.

Writing Code for a Windows Forms Button

The next step is to write some code so that clicking on the button actually does something. Double Click on the button and Visual Studio will automatically switch to the code view for the form. It will also automatically create a handler which will run whenever you click the button.

writing code for the button click event

If you want to create a handler for a different event, select the appropriate event from the drop down list of events at the right top corner of the window. If the event exists it will be shown in bold and selecting it will take you to that section of code. If it doesn't exist, then the shell of the handler will be created for you.

event-list.jpg

When the event handler is created the cursor is sitting there waiting for you to type code. At this point you could simply start writing code, but to make things easier and more modular, I prefer to have a button click call a sub-routine. That way another button can call the same sub-routine. It also makes writing and debugging the code easier. So the only thing we are going to do is call the sub-routine I'll call "countTasks".

To do this, select a line outside of the event handler and type "sub countTasks()". The editor will create an end for it and a blank line for your code. Now go back to the event handler and on the blank line there write "countTasks". You now have a button which when clicked will run the countTasks sub-routine.

running-a-subroutine.jpg

Refering to the Project Application and Active Project

Up until now we have just been working within Visual Studio and have not been making any references to Microsoft Project. We are about to get started with that. In this example we are just going to work with the active project. To make this easy we will define a variable named proj. To do this we write a typical dim statement which references the Microsoft.Office.Interop.MSProject namespace. If we were writing VBA within project we could write:
dim proj as Project
Since we are not within Project VBA we need to specify more about where the project object can be found so we write:
Dim proj As Microsoft.Office.Interop.MSProject.Project
The intellisense within Visual Studio will suggest to you the likely object after you type a letter or two as shown below:

defining-the-project-variable.jpg

The next thing to do is to set proj to the active project. This isn't strictly necessary, but it does make things easier. To do this we write:
proj = Globals.ThisAddIn.Application.ActiveProject
The final step is to output some things about the active project. In this case we will just use a message box to report to the user. You could have the text be displayed in a text box or on a label within the form as well. The line we are going to use to output the project name and the number of tasks is:
MsgBox(proj.Name & " has " & proj.Tasks.Count & " tasks")
Finally, after we are done reporting on the number of tasks in the project we release the proj variable by setting it to nothing:
proj = Nothing

final-code.jpg

Showing the Form

The final thing we have to do with our add-in before we can publish it is to make the form we just made show. Go back to the ThisAddIn.vb tab and in the Startup event, define a form (here I use "myForm" as the object and use the show method to display the form. In another tutorial I'll post a cleaner way of doing this by creating a menu bar and buttons to show the form. This form is just going to show when the add-in is loaded. Here is the final code for this:

show-form.jpg

Publishing the Add-in

There are several options for publishing the add-in. That subject is also worth a separate post detailing all the different ways, but for now I'm just going to publish to a folder on my own hard drive. To do this go to the "Build" menu and select "Publish ..."

publish-project-addin.jpg

When the wizard runs, enter the location you want to publish to and click your way through the rest of the wizard. It might take a little while and when you are done you will see a notification in the lower left corner that "Publish succeeded".

publish-wizard.jpg

Using Windows Explorer, navigate to the location that you saved the file to and double-click the "Set-up" file.

set-up.jpg

Using the Add-in

After installing, the add-in will run whenever you open project. This add-in will float above your project and can be clicked at any time. If you want to close it, just click the x.

final-add-in.jpg

If you want to disable it, go to the "Tools" menu, select "Com add-ins" and then deselect the item from the list that shows. You can also remove the Add-in by going to your Windows control panel, choosing the "Add-Remove Programs" option, find the add-in and click on "uninstall".

I hope that you found this tutorial helpful in understanding the basic principles of building and deploying a VSTO Microsoft Project Add-in. With a bit of work you can take this very rough and simplistic Add-in and create something useful and easy to deploy. Feel free to leave comments or ask questions.

UPDATE: Find the second part of this tutorial which shows how to add a command bar and command buttons here.

RELATED POSTS
  • Microsoft Project Undo Levels and Macros
  • VBA and Visual Basic For ... to ... statements
  • VBA to VSTO Tutorial Part Two - Adding a Command Bar and Buttons
  • Analyze Microsoft Project Resource Usage Data In Excel
  • VBA Writing to a text file (MS Project, Excel)
  • Working with the Project Object
  • Working with the Tasks Collection
  • Setting Microsoft Project Level Custom fields using VBA
  • New Fields in Microsoft Project 2010
  • Project 2010 Tips and Tricks Webcast

  • Comments (2)

    Mot:

    Hello:


    Thanks for sharing your experience. I've also read your tutorial No. 2.


    I have just finished migrating from VBA to VSTO for my Excel files, and now proceeding on MS Project files.


    I am quite comfortable with VSTO/Excel, but still struggling with VSTO/MSProject. For example, I have no clue why in the following two statements, the first Project1 declaration is accepted by the compiler, yet the second Project1 assignment is not.


    Dim Project1 As _
    Microsoft.Office.Interop.MSProject.Project


    Project1 = Globals.ThisAddin.Application.ActiveProject


    Anyway I'll struggle through, but just want to ask if I am going to get where I need to be.


    This is what I need to get to: In VBA/MSProject, I use "ProjectBeforeTaskChange" event. If somone changes the content of a field on a task, the event would be triggered, the changes will be examined, then accepted or rejected accoridng to procedures built into the event.


    Another nice to have is the ability to make a task or field read-only or writable according to the identity of the editor. This is not available in VBA, but may be available in VSTO.


    So in order to get what I need, VSTO must allow me to use "ProjectBeforeTaskChage" event, and preferably to allow locking of a field.


    Have you been successful in programming these two features ?


    Mot
    Locking Project fields is not something that can be done reliably with either VBA or VSTO. The reason is that the user can easily turn off macros - in fact in many organizations, it is set that way by policy - so disabling the code is trivial and likely. The second point is that many of the on-change events in project have conditions where they are not triggered by actions, so there may be cases where they work, and cases where they don't. Because of this, I avoid most of the events except for those based on open and close. -Jack

    Ranjan Poudel:

    Hi,

    Thank you for posting such a wonderful acticle, especially for beginners of VSTO like me. In fact, I have been programming Excel VBA for a long time now and wanted to migrate to VSTO. I was desperately trying to learn the basics and you have helped me a lots.

    Thanks again.
    Ranjan Poudel
    Excel VBA Developer
    Kathmandu, Nepal

    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 Office VBA for Mac After 2008.

    The next article is Project Management Immaturity Model.

    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