Doug
If a task is empty / blank then it is equal to nothing. This is the VBA
value used for references or objects that are not declared or defined. You'll
usually get a runtime error 91 - "Object variable or With block variable not
set" when this occurs.
To avoid this error, you need to test that the task is not nothing - ie not
blank.
The followng code fragment check this during the task loop, if you copy and
paste this into a macro and step through it (F8) you'll get the hang of it.
Also note the check for an open project ~ another pitfall :
Option Explicit
Function ReadTasks() As Boolean
' NB - Redefined as a function, this allows the code to be called from
other modules and returns a success value. In reality all Subs are Functions
anyway - you just aren't aware of it in VB / VBA.
' Define reference variables.
' NB - you don't need to use these, but they make your code more readable
' and speed up your code execution since references need to be resolved
once only.
Dim mProjectApplication As msProject.Application
Dim mProject As msProject.Project
Dim mTasks As msProject.Tasks
Dim mTask As msProject.Task
' Unhandled error trap - just in case
.
On Error GoTo ReadTasksError
' Set reference to MSProject application.
Set mProjectApplication = msProject.Application
' Set a reference to the active project - First check that a project is
open.
If mProjectApplication.Projects.Count = 0 Then
' No projects are open - set the error object info and invoke the
error handler.
Err.Number = 0
Err.Description = "No projects are open"
GoTo ReadTasksError:
End If
Set mProject = mProjectApplication.ActiveProject
' Set a reference to the active project tasks.
Set mTasks = mProject.Tasks
' Iterate through the project tasks using the for each construct.
Debug.Print String(20, "-")
Debug.Print "Start of task list"
Debug.Print "Total tasks : " & Str(mTasks.Count)
Debug.Print String(20, "-")
Debug.Print mProject.ProjectSummaryTask.Name
Debug.Print String(20, "-")
For Each mTask In mTasks
If mTask Is Nothing Then
Debug.Print "*** Blank Task ***"
Else
' Output the tasks to the debug window - Indent them to reflect
those in the project plan.
Debug.Print String(mTask.OutlineLevel, " ") & mTask.Name
End If
Next
Debug.Print String(20, "-")
Debug.Print "End of task list"
Debug.Print String(20, "-")
' Process succeeded - return true.
GoTo ReadTasksExit
ReadTasksError:
' Error encountered - report..
MsgBox "(" & Trim(Str(Err.Number)) & ") " & Err.Description, vbCritical,
"Error - Read Tasks"
' Process failed - return false.
ReadTasks = False
' ..and fall through to exit block to gracefully shutdown.
ReadTasksExit:
' Release references for garbage collection.
Set mTask = Nothing
Set mTasks = Nothing
Set mProject = Nothing
Set mProjectApplication = Nothing
' Clear the error handler to ensure it isn't invoked by other module
errors.
On Error GoTo 0
End Function
Okay, so far so good ( I hope). But the "If mTask Is Nothing" statement is
really redundant and the same result can be achieved with a positive false
test :
For Each mTask In mTasks
If Not mTask Is Nothing Then
' Output the tasks to the debug window - Indent them to reflect
those in the project plan.
Debug.Print String(mTask.OutlineLevel, " ") & mTask.Name
End If
Next
Note I've eliminated the Debug.Print "*** Blank Task ***" statement.
And yes - I hate the Goto statements but it's the most graceful landing you
can achieve with VBA.
The one thing I've deliberately missed out is the test / report for a
project with 0 tasks - I thought I'd leave this for you - but there is a hint
in the code
Though this may look a little complicated - try stepping through it a few
times and deliberately break a few things - you could comment out some of the
references and watch it blow up and get caught by the error handler, it
should get you started. Good luck!
Cheers, Lee.
