Project VBA Programming
Table of Contents

1

    Downloading the Sample Files

3

    Introducing VBA

3

    VBA Programming Skills Needed

4

    Support for Your VBA Macros

5

Module 01: Creating and Editing VBA Code

7

    Understanding the Visual Basic Editor

9

    Using the Project Explorer

12

    Entering and Editing Code

13

    What is IntelliSense?

15

    Understanding Modules

17

    Using Sub Procedures

17

    Using Functions

18

    Using GoSub Subroutines

18

    Using Class Modules

18

    Using UserForms

19

Module 02: Common VBA Code for All Office Programs

21

    Using Office VBA Code

23

    Adding Comments to Your Code

23

    Understanding Objects, Methods, Properties, and Parameters

24

    Using the With Statement

27

    Using the MsgBox Function

29

    Prompting the User for Inputs

30

    Understanding Functions and Procedures

31

    Using Help to Learn More about VBA

33

Module 03: Using Variables and Constants

35

    Using Variables to Store Data

37

    Declaring Variables

37

    Using a Naming Convention for Variables

39

    Using Constants to Store Fixed Values

39

    Controlling Variable Scope

41

    Forcing Declared Variables

41

    Protecting Variables from Unwanted Change

42

    Sharing Variables with Another File

42

    Storing Data in the Registry

42

    Working with Data Types

43

    Handling Dates

43

    Handling Text

45

    Using Numbers

48

    Using Variant Variables

49

    Converting Between Variable Types

49

    Compiling Your Code

50

Module 04: Using Conditional Statements and Loops

53

    Structuring Your VBA Code

55

    Using the Conditional IF Statement

56

    Working with Loops

58

    Using the Do Loop

58

    Using the For Next Loop

60

    Using the For Each Next Loop

60

Module 05: Using Classes

63

    Creating a Simple Class

65

Module 06: Working with Other Applications

71

    Understanding VBA Automation

73

    Adding References to Other Applications

73

    Managing VBA Security

75

    Creating a Digital Certificate

76

Module 07: Writing and Debugging Your Code

81

    Using a Simple Approach to Create a VBA Macro

83

    Estimating Development Time

84

    Running and Debugging Your VBA Code

85

    Using VBA’s Debugging Features

86

    Making Your Code Easier to Maintain

88

    Making Your Code Easier to Debug

90

Section 2: Microsoft Project VBA Essentials

93

    Introducing Project VBA

95

    Downloading the Sample Files

95

Module 08: Defining and Using Custom Fields

97

    Overview of Custom Fields

99

    Using Formulas to Support Project VBA

102

    Defining Custom Fields

103

    Using a Value List in a Custom Field

105

    Using a Formula in a Custom Field

109

    Testing for an NA Date Value

113

    Using Graphical Indicators in a Custom Field

113

    Defining Custom Outline Codes

119

    Deleting a Custom Field or Outline Code

124

Module 09: Using Objects, Methods, and Properties

127

    Understanding the Project Object Model

129

    Using Application-Related Objects, Methods, and Properties

131

    Using Project-Related Objects, Methods, and Properties

133

    Using Project Objects

133

    Using Project Methods

134

    Using Project Properties

134

    Using Task-Related Objects, Methods, and Properties

135

    Using Resource-Related Objects, Methods, and Properties

137

    Using Assignment-Related Objects, Methods, and Properties

138

    Using the Object Browser

138

Module 10: Recording Macros

145

    Understanding When to Record a Macro

147

    Recording a Macro

147

    Modifying a Recorded Macro

151

    Storing Your VBA Code

153

    Controlling the Size of Project .MPP Files

155

    Repairing Corrupted Files

156

Module 11: Looping Through Your Schedule

157

    Looping Through All Tasks in a Project

159

    Clearing a Custom Field for Non-Summary Tasks

160

    Using Loops to Set Custom Field Values

161

Module 12: Creating New Objects in Microsoft Project

163

    Safely Creating New Objects

165

    Creating New Views, Tables, and Filters

165

    Creating a New Toolbar

166

    Creating a New Menu

167

Module 13: Managing Run-Time Errors

171

    Managing Errors

173

    Using Resume Next Error Handling

175

    Using Goto Error Handling

175

    Selecting an Error Handling Method

176

    Using the Err Object

176

Module 14: Creating and Running UserForms

179

    Understanding UserForms

181

    Creating a UserForm

182

    Adding Code to a UserForm

184

Module 15: Using Events

187

    What Are Events?

189

    Using Project Events

189

    Using Task Events

190

Module 16: Creating the Project Control Center Macro

195

    Structuring Your Macros

197

    Project Control Center Macro Overview

198

    Designing the Project Control Center

198

    Deliverables for Project Control Center

199

    Understanding the Project Control Center Code

200

Module 17: Displaying Driving Tasks

209

    Designing the Driving Task Macro

211

    Designing the Main Procedure

212

    Understanding Task Dependencies

213

    Understanding the Task Driver Code

213

    Adding a UserForm to Display the Results

222

    Running the Macro from a Toolbar

228

Module 18: Distributing Your Macros

231

    Making Your Procedures Private

233

    Calling a Procedure from Another File

233

    Using the Global.mpt File

234

    Moving Macros into the Global.mpt File

234

    Copying Modules and Forms to the Global.mpt

235

    Adding the CopyToGlobal Toolbar Button

236

    Creating a Global Macros Toolbar

237

Module 19: Working with Timephased Data

241

    Understanding Timephased Data

243

    Reading Timephased Data

244

    Exporting Timephased Data to a .csv file

246

    Writing Timephased Data

250

    Updating the Project Control Center

252

    Designing the Get Resource Procedure

253

Module 20: Controlling Excel with Project VBA

261

    Using Excel for Project Reporting

263

    Understanding VBA Automation

263

    Using Late Binding

264

    Using Early Binding

265

    Connecting to an Already Open Copy of Excel

267

    Exporting a List of Resources to Excel

268

    Running Excel VBA Code Using Project VBA

270

    Controlling Microsoft Project from Excel

271

    Creating the S-Curves Macro in Excel

272

    Completing the Who Does What When Macro

278

Module 21: Create a Cost Margin Report

287

    Understanding the Cost Margin Report

289

Module 22: Consolidate Multiple Projects

295

    Creating Consolidated Project Reports

297

Module 23: Changing Working Time

303

    Working with Calendars

305

    Designing the Update Calendars Macro

305

    Create the Calendar Exceptions UserForm

306

    Add the Text Class to Store Calendar Exceptions

319

    Apply Calendar Changes to All Projects

323

    Add the Macro to the Project Control Center Toolbar

326

Module 24: Display Predecessor and Successor Tasks

329

    Working with Task Dependencies

331

    Designing the LinkedTasks Macro

331

    Understanding the TaskDependency Object

333

    Understanding the TaskLinks Macro Code

334

Module 25: Miscellaneous Useful Code

343

    Indenting Tasks Using VBA

345

    Reorganizing Task Sequences

348

    Creating Formulas in Custom Fields

349

    Working with Hyperlinks

350

    Working with Subprojects

352

    Making Your Code Run Faster

352

    Timing Code Execution

354

    Using the Windows API to Open and Save Files

356

    Using File Open

357

    Using File Save as

359

    Using Get Folder

360

    Applying the clsBrowse Class to a New Project

360

Section 3: working with Databases

363

    Developing Project Database Code

365

    Downloading the Sample Files

366

Module 26: Importing Data from Other Sources

367

    Importing Data from an Excel Workbook

369

    Importing Data from an Access Database

376

    Importing Data from a SQL Server Database

379

Module 27: Reading and Writing Data in a Database

385

    Using the PJDB.HTM File

387

    Looping Through all Projects in a Database

389

    Reading Data from a Database

391

    Creating the Database View

391

    Creating the Excel VBA Macro

392

    Writing Data to a Database

395

Module 28: Using the OLE DB

403

    Understanding OLEDB

405

    Using the OLEDB Driver for Your Project Version

405

    Using the OLEDB driver with Project 2007

406

    Connecting to an .MPP File Using OLEDB

406

Module 29: Accessing Project Server Data from Excel

415

    Understanding the pjsvrdb.htm File

417

    Weekly Reporting on Project Server Data

418

    Looping through All Projects

418

    Starting Project Professional and Logging into Project Server

419

    Setting up the Excel Report

421

    Reading a List of All Project Titles

421

    Reading Milestone and Current Task Data

425

    Reading all Issues and Risks for a Project

427

    Writing the Final VBA Code for the Excel Report Macro

431

    Creating a Project Program Report

435

Module 30: Using Project 2007 VBA

437

    Introducing Project 2007 VBA

439

    Using Named Parameters

440

    Undoing a Macro

440

    Managing Custom Fields

442

    Controlling Visual Reports

443

    Managing Calendars

443

    Formatting Cell Background Colors

444

    Managing Deliverables

445

    Converting SQL Views from 2003 to 2007

446