Calling Jack Dahlgren... VBA help

[MarshallR]

Hi, I mailed Mike Glen with some VBA questions, and he
advised that Jack was the man to speak to. Here are bits
from my original mail:

I've been playing with VBA to automate tasks quite a bit,
but being completely self-taught (I'm relying solely on
the help files provided!) other than a teeny bit of
experience dabbling in BASIC when I was a kid, it's been
fairly slow going.

However, I've managed to make some very useful code
including one template which creates an mpp for an entire
3 month maintenance release for the software company I
work for - based on an exported Crystal report of the
errors recorded in our database. It used to take a day or
so to key in all the error numbers, descriptions, time
estimates for development, testing etc. Now all it takes
is an export of the rpt file into excel, and copying the
data to clipboard at the right time, as prompted by the
VBA - all in all it can be done in under 5 minutes. This
code is of fairly limited use obviously, given that it's
organisation-specific.

One of the other macros I've written was one I did to
provide a feature which I was surprised isn't offered by
Project as standard - a flag per task which flags when all
the task's predecessors are complete. It loops though
each task and identifies whether the task's immediate
predecessors, and 'grandparents', 'great-grandparents'
etc, are complete. With a graphical indicator on the
customised flag column it provides a very useful tool
(especially for large projects with thousands of lines,
which I've had to deal with - and without collaboration
options!!), which can then be filtered to give a quick
view of what's ready to start.

One thing I still have problems with is how Project treats
inserted mpps in a consolidated project. I've had a lot
of difficulty writing VBA that will effectively manipulate
tasks in a master project, because of things like the
Methods on the whole tending to rely on IDs rather than
Unique IDs. Most of the critical stuff I do (like
the "flag ready to start" macro above) I have decided to
write to be executed on individual sub plans - in fact the
whole macro is encased in a loop which goes through all
the open projects so that I don't have to open each one
individually, run it, close it, open the next one... (i.e.
it runs the process on the first open project, then the
second, and so on).

The one macro I have written which steps through each task
in a consolidated project is one which marks overbudget
tasks of a set tolerance, in red text (one of my managers
wanted an easy view). This was one of the first I wrote
and is very clumsy and slow - rather than using something
like 'for each task...', I resorted to physically forcing
the focus down one cell at a time. It works, but it's
very inelegant! From memory I also had problems
with 'object required' errors on blank lines - had to add
some error handling which again made me a little
disappointed in the way it was constructed.

Do you have any tips for how best to handle consolidated
projects with VBA?

I've also had some problems adding an application object
for Project to handle application events including crashes
in certain circumstances. Is this well known as an area
of instability?

Sorry to have rambled... any help would be appreciated.

Cheers,
Marshall.

 

[John]

Marshall,
Jack may step in also to offer his expert advice. Meanwhile here's my 2
cents. It is nice to see someone else who has the fortitude to learn VBA
using the self taught process. You are clearly reaping the rewards of
VBA by developing procedures to make Project do the things you need. It
will also increase your value to your company.

One of the first things I do when running a macro on a consolidated file
is to ensure the file is fully "exploded". One simple line of code will
do that:
OutlineShowTasks expandinsertedprojects:=True

The standard method for dealing with blank lines is the following code
structure:
For Each t in ActiveProject.Tasks
If Not t is Nothing Then
[your code here]
End If
Next t

I never did like the double negative (i.e. "Not" and "Nothing")
structure but it is the most effective method.

Fortunately it is pretty easy to loop through all tasks of a
consolidated project once the file is "exploded". The "For Each . . ."
structure above covers all tasks. It gets a little more complex to loop
through all resources if the structure includes a common resource pool
file but there are ways to deal with that also.

Another trick I often employ is to loop through a filtered set of tasks.
First apply a filter and then use code similar to the following:
[filter definition code]
SelectTaskColumn
Set Area = ActiveSelection.Tasks
For Each t in Area
If Not t is Nothing then
[your code here]
End If
Next t

The more code you can write to run in what I call background processing
(versus forground processing), the faster it will execute. This is
especially important when working with large files. By background
processing I mean code that operates by referring to project objects
indirectly rather than code that selects a task (or resource) in order
to operate on it. In other words, avoid code that "selects". However,
sometimes the only way to effectively get to a project object is to
select it. For example, the code snippet above uses a combination. A
"select" method is used to set the Area however the loop operates on the
task objects indirectly (i.e. you don't see the loop going through each
task in the active view but you will see the results if visible fields
are changed).

Hopefully this gives you some assistance. I'm sure Jack can provide even
more.

John

 

[MarshallR]

Thanks John.

The line for exploding sub tasks is very handy - I'd done
the same thing in a much more roundabout way with five
lines of code in a 'DisplayAll' sub procedure which I call
from other subs as req'd. Looks like I can do a find and
replace on that call now...

The double negative bit is also handy (though as you
suggest, getting one's head around the logic is a bit
dicey...). I had been using error handling - this is much
prettier.

Re looping through all tasks in a consolidated project,
I've had no problems with this, it's just how to a pass a
reference to a task that I had problems with. I can't
remember now what the actual problem was, but there was a
sticking point in that the syntax required 'task ID' to
identify tasks, and as you know, in a consolidated Project
the ID is not unique. I remember pulling my hair out
wondering why Unique ID wasn't the syntax required.
Anyway, if I come up against it again I'll come back with
some more specific details.

Thanks again - just the first two points were enough to
make the huge amount of spam I've received from my first
three newsgroup posts, worthwhile!!

Cheers,
Marshall.

 

[John]

Marshall,
You're welcome. I'll be honest with you, until I discovered the "Expand
all tasks" method, I too tried using an defined limit loop with
"DisplayAll". I ran into a similar issue with decoding the predecessor
field. I was unaware of the "TaskDependencies" property until only
recently. Oh well, its all part of the learning process.

I though Jack might also reply but I guess he figured I covered it. You
might be interested in Jack's webpage though. It has several nice VBA
macros you may find useful as timesavers or to use as learning tools.
His webpage is at: http://masamiki.com/project/macros.htm

Good luck with your VBA code.
John

 

[JackD]

Marshall,
You're welcome. I'll be honest with you, until I discovered the "Expand
all tasks" method, I too tried using an defined limit loop with
"DisplayAll". I ran into a similar issue with decoding the predecessor
field. I was unaware of the "TaskDependencies" property until only
recently. Oh well, its all part of the learning process.

I though Jack might also reply but I guess he figured I covered it. You
might be interested in Jack's webpage though. It has several nice VBA
macros you may find useful as timesavers or to use as learning tools.
His webpage is at: http://masamiki.com/project/macros.htm

Good luck with your VBA code.
John

Seemed like it was under control so I didn't feel like I had much to add.
By the way, I try never to depend on task ID or task UID.

Your point about using filters instead of going through each task is a good
one. I've found it faster in some instances. Here is a bit of code I'm
working on now which sets a collection of tasks (exportedTasks) equal to the
activeselection.tasks. Instead of checking for blank tasks you need to check
for a selection that is empty. That is what the If ActiveSelection = 0 does.
Once you have the task collection you can do many things with it.

-Jack
--------------------------------
Sub setExportedTasks()
'filters to get collection of tasks for export
'with flag10 set to yes
'warn if export is more than 20 tasks
dim exportedTasks as Tasks
FilterEdit Name:="_MCexportedTasks", TaskFilter:=True, Create:=True,
OverwriteExisting:=True, FieldName:="Flag10", Test:="equals", Value:="yes",
ShowInMenu:=False, ShowSummaryTasks:=False
FilterApply Name:="_MCexportedTasks"
SelectAll
If ActiveSelection = 0 Then
myCancel = True
MsgBox "You have no tasks to export data for" & Chr(13) _
& "Please check the flag10 field to be sure that some tasks are marked YES"
Exit Sub
End If
Set exportedTasks = ActiveSelection.Tasks
exportcount = exportedTasks.Count
If exportcount > 25 Then
If MsgBox("You are exporting " & exportcount & " tasks" & Chr(13) & "Are
you sure you want to continue?", vbOKCancel, "Large Export Warning") =
vbCancel Then
myCancel = True
Exit Sub
End If
End If
End Sub
----------------------------------

 

[MarshallR]

Hi Jack,

I think we must think along similar wavelengths - I
checked out your website and found that you've got a macro
to trace predecessor tasks. I did one in a similar vain
which traces predecessors and assesses whether they're
complete, to update a flag.

Just this week I did this one:

Sub KCSExportWork()
Dim varResponse

varResponse = MsgBox("Do you want to export only those
tasks flagged as being assigned for this week (Flag4)?" _
+ Chr(13) + Chr(13) + "Note that choosing 'No' will result
in all tasks for the resource being exported.", vbQuestion
+ vbYesNoCancel, "Which Filter?")

OptionsSave defaultProjectsPath:="c:\my
documents\planupdates\out"

Select Case varResponse
Case vbYes
FileSaveAs FormatID:="MSProject.XLS5",
map:="KCSUpdate"
Case vbNo
FileSaveAs FormatID:="MSProject.XLS5",
map:="KCSUpdateAll"
End Select

OptionsSave defaultProjectsPath:="c:\my documents\project"

End Sub


.....which as you can see exports to one of two pre-defined
maps (by the way, KCS is the company I work for).
I had just started working on a macro which will
dynamically manipulate the properties in a map so that you
can export the current view (i.e. using the currently
applied filter, table, etc). Looks like you're doing a
similar thing with a different approach. Innnteresting...

Thanks for all your help guys (by the way, I just used
the 'If Not t is Nothing Then' line for the first time
today - verrry handy).