VBA Programming for Microsoft Office Project: Versions 98 Through 2007

Part Two: Advanced Project VBA

Syllabus in PDF Format

Course Overview

This course is designed to give you a great head start with VBA programming in Microsoft Project and is applicable to all releases from version 98 through 2007. Learn with Rod Gill, one of the world’s foremost Project VBA experts, live over the Internet in four half-day sessions for Exploring Advanced Project VBA delivered one week after the Intro course over four half-day sessions.

Target Audience

The target audience for this course is beginners to advanced VBA programmers.

Pre-Requisites

Prior to taking this course, you should take Part One: Intro to VBA unless you already possess basic VBA programming skills.

This is a course where the more you know beforehand, the more you are likely to learn during the course. The ideal preparation, therefore, is to experiment with recording and creating macros or playing with and editing an existing macro. Get clear what it is you want to be able to do after the course and what sort of macros you want to work on. We’ll focus more time in student’s expressed main areas of interest and much less on other areas. Please communicate what's most important for you to your training advisor.

Finally, come to class with a macro you need to develop during and after the course. Make sure that you also have time allocated between sessions and after the course to practice and use what you learn.

Learning Objectives

After completing this course, you will be able to:

• Build, edit, and use some very productive macros included with the course material.
• Use your class workbook to find the answers to many common questions and problems.
• Work with Microsoft Project databases and know when and how to use it.
• Create VBA macros in Excel to report on information in Project Server databases. (Note: If you do not have Microsoft Project Server, then you can spend this time can working on other macros with the instructor's help.)

Topical Outline

Module 8: Defining and Using Custom Fields

• Overview of Custom Fields
• Using Formulas to Support Project VBA
• Defining Custom Fields
• Using a Value List in a Custom Field
• Using a Formula in a Custom Field
• Testing for an NA Date Value
• Using Graphical Indicators in a Custom Field
• Defining Custom Outline Codes
• Deleting a Custom Field or Outline Code

Module 9: Using Objects, Methods, and Properties

• Understanding the Project Object Model
• Using Application-Related Objects, Methods, and Properties
• Using Project-Related Objects, Methods, and Properties
• Using Project Objects
• Using Project Methods
• Using Project Properties
• Using Task-Related Objects, Methods, and Properties
• Using Resource-Related Objects, Methods, and Properties
• Using Assignment-Related Objects, Methods, and Properties
• Using the Object Browser

Module 10: Recording Macros

• Understanding When to Record a Macro
• Recording a Macro
• Modifying a Recorded Macro
• Storing Your VBA Code
• Controlling the Size of Project .MPP Files
• Repairing Corrupted Files

Module 11: Looping Through Your Schedule

• SLooping Through All Tasks in a Project
• Clearing a Custom Field for Non-Summary Tasks
• Using Loops to Set Custom Field Values

Module 12: Creating New Objects in Microsoft Project

• CSafely Creating New Objects
• Creating New Views, Tables, and Filters
• Creating a New Toolbar
• Creating a New Menu

Module 13: Managing Run-Time Errors

• Managing Errors
• Using Resume Next Error Handling
• Using Goto Error Handling
• Selecting an Error Handling Method
• Using the Err Object

Module 14: Creating and Running UserForms

• Understanding UserForms
• Creating a UserForm
• Adding Code to a UserForm

Module 15: Using Events

• What Are Events?
• Using Project Events
• Using Task Events

Module 16: Creating the Project Control Center Macro

• Structuring Your Macros
• Project Control Center Macro Overview
• Designing the Project Control Center
• Deliverables for Project Control Center
• Understanding the Project Control Center Code

Module 17: Displaying Driving Tasks

• Designing the Driving Task Macro
• Designing the Main Procedure
• Understanding Task Dependencies
• Understanding the Task Driver Code
• Adding a UserForm to Display the Results
• Running the Macro from a Toolbar

Module 18: Distributing Your Macros

• Making Your Procedures Private
• Calling a Procedure from Another File
• Using the Global.mpt File
• Moving Macros into the Global.mpt File
• Copying Modules and Forms to the Global.mpt
• Adding the CopyToGlobal Toolbar Button
• Creating a Global Macros Toolbar

Module 19: Working with Timephased Data

• Understanding Timephased Data
• Reading Timephased Data
• Exporting Timephased Data to a .csv file
• Writing Timephased Data
• Updating the Project Control Center
• Designing the Get Resource Procedure

Module 20: Controlling Excel with Project VBA

• Designing the Driving Task Macro
• Using Excel for Project Reporting
• Understanding VBA Automation
• Using Late Binding
• Using Early Binding
• Connecting to an Already Open Copy of Excel
• Exporting a List of Resources to Excel
• Running Excel VBA Code Using Project VBA
• Controlling Microsoft Project from Excel
• Creating the S-Curves Macro in Excel
• Completing the Who Does What When Macro

Module 21: Create a Cost Margin Report

• Understanding the Cost Margin Report

Module 22: Consolidate Multiple Projects

• Creating Consolidated Project Reports

Module 23: Changing Working Time

• Working with Calendars
• Designing the Update Calendars Macro
• Create the Calendar Exceptions UserForm
• Add the Text Class to Store Calendar Exceptions
• Apply Calendar Changes to All Projects
• Add the Macro to the Project Control Center Toolbar

Module 24: Display Predecessor and Successor Tasks

• Working with Task Dependencies
• Designing the LinkedTasks Macro
• Understanding the TaskDependency Object
• Understanding the TaskLinks Macro Code

Module 25: Miscellaneous Useful Code

• Indenting Tasks Using VBA
• Reorganizing Task Sequences
• Creating Formulas in Custom Fields
• Working with Hyperlinks
• Working with Subprojects
• Making Your Code Run Faster
• Timing Code Execution
• Using the Windows API to Open and Save Files
• Using File Open
• Using File Save as
• Using Get Folder
• Applying the clsBrowse Class to a New Project

Module 26: Importing Data from Other Sources

• Importing Data from an Excel Workbook
• Importing Data from an Access Database
• Importing Data from a SQL Server Database

Module 27: Reading and Writing Data in a Database

• Using the PJDB.HTM File
• Looping Through all Projects in a Database
• Reading Data from a Database
• Creating the Database View
• Creating the Excel VBA Macro
• Writing Data to a Database

Module 28: Using the OLE DB

• Understanding OLEDB
• Using the OLEDB Driver for Your Project Version
• Using the OLEDB driver with Project 2007
• Connecting to an .MPP File Using OLEDB

Module 29: Accessing Project Server Data from Excel

• Understanding the PJSVRDB.HTM File
• Weekly Reporting on Project Server Data
• Looping through All Projects
• Starting Project Professional and Logging into Project Server
• Setting up the Excel Report
• Reading a List of All Project Titles
• Reading Milestone and Current Task Data
• Reading all Issues and Risks for a Project
• Writing the Final VBA Code for the Excel Report Macro
• Creating a Project Program Report

Module 30: Using Project 2007 VBA

• Introducing Project 2007 VBA
• Using Named Parameters
• Undoing a Macro
• Managing Custom Fields
• Controlling Visual Reports
• Managing Calendars
• Formatting Cell Background Colors
• Managing Deliverables
• Converting SQL Views from 2003 to 2007