Project VBA to change Total Slack Column?

[Brian]

I have a fairly large Project file that I am working with (approx. 5000
lines) with a few hundred lines of LOE activities (these tasks are flagged in
a flag column "LOE" as 'Yes'). I am looking to use the existing project
Critical Path Functionality to identify where my actual critical tasks are.
The issue is that the LOE activities jump on the Critical path due to their
duration (most span the entire program).

My question is:

Can I override the total slack column to where LOE = Yes set Total Slack to
1 day, and have it recalculate for the remainder of the file?

Is there another way to calculate the critical path without deleting my LOE
tasks from the file and then using the critical path filter?


Thanks in advance,

Brian

 

[Jim Aksel]

You can use VBA to calculate a different critical path for you ignorning
values where LOE=Yes. That's a lot of work, please consider this instead:

Pull all your LOE activities out to a seperate file. Create a Master
Project and insert your Measureable and LOE files into it. Your full
schedule is now back together.

By definition, an LOE activity will not have any "real" predecessors or
successors so it can stand alone in a separate file. In some instances, an
LOE activity does not start until a certain point in the program such as
"Phase2 funding received" or "CDR". In cases like that, you can use a
Hammock Task as your LOE-- kick off your LOE with a date from your
Measurable file. Information on Hammock tasks is available on the link
below. Go to FAQ and read FAQ#19.

Here, were a little lazy... we just hard code the dates in the LOE file and
have to remember to adjust those as appropriate based on program realities,
funding profiles, and the like. A good example is "Customer Test Support"
.... LOE for us, and we have no control over when our customers actually take
our software out into a live test environment since it happens at their
facilities long after we've delivered.

I find this to be an effective solution - rather than reinventing the wheel
of "Calculate Critical Path" with some new tweaks. Although that would be
nice, the implementation would involve subtle devils becuase inevitabily
someone is going to hang a predecessor to an LOE task that will drive
something. I hope I just avoided a lot of development time for you.
--
If this post was helpful, please consider rating it.

Jim
It''s software; it''s not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

 

[John]

I have a fairly large Project file that I am working with (approx. 5000
lines) with a few hundred lines of LOE activities (these tasks are flagged in
a flag column "LOE" as 'Yes'). I am looking to use the existing project
Critical Path Functionality to identify where my actual critical tasks are.
The issue is that the LOE activities jump on the Critical path due to their
duration (most span the entire program).

My question is:

Can I override the total slack column to where LOE = Yes set Total Slack to
1 day, and have it recalculate for the remainder of the file?

Is there another way to calculate the critical path without deleting my LOE
tasks from the file and then using the critical path filter?


Thanks in advance,

Brian

Brian,
Well you can't override the Total Slack or Critical fields - they are
calculated by Project. You can however, customize a spare flag field
with this formula,
Flag1=IIf([Critical]=True And [your LOE flag field]=False,"yes","no")

Then format a gantt bar style to pick up the result of Flag1.

Hope this helps.
John
Project MVP

 

[Brian]

Thanks for your help Jim.

The main issue is that our customer wants one project file where they can
see all program tasks, and see the critical path by selecting another view.
Also, I forgot to mention that I am not using Project server, rather I am
using regular Project 2003. My current solution has been similar to what you
describe, but rather than store the LOE in another file, I delete them out of
a master file and rename the master file 'Critical Path.' Once the LOE has
been removed the calculations workout and my real critical path is
calculated.

You say that it is possible to re-calculate the critical path with VBA,
would you happen to know where I can look to find some sample code? I would
just want to set Total Slack = Null where LOE = Yes. It seems like a fairly
simple loop, assuming project will allow me to override this column.

I have good VBA experience with Excel, Access and PowerPoint, but it has
been difficult to find examples of Project VBA online! If you (or anyone!)
could point me to some examples I would greatly appreciate it!

Thanks again,

Brian

 

[Jim Aksel]

Sorry, I don't have a quick place to look for a code sample on critical path.
The best I could do is refer you to books on Project Management and how to
manually calculate a critical path. You will have to modify the algorithm to
exclude when LOE=yes.

I know the Program Management Body of Knowledge (PMBOK) has the information,
it is available from PMI.org but you can probably search Google and get some
answers. The basic approach is to establish Late Start/Finish and Early
Start/Finish by making a forward (ASAP) and backward (ALAP) pass through the
schedule. The diferences between the two dates are your slack. If slack=0
then you've found a critical path item.

You may be tempted to use Total Slack since Slack=0 is the definition of
critical path. However, remember Total Slack and Critical=yes are both
calculated by project so they are already influenced by your LOE efforts and
you need to avoid that. Project also calculated Early/Late Starts and
Finishes but those are also influenced by your LOE tasks as well so you
cannot rely on those either as long as there are LOE tasks in your schedule.

If you send your customer the Master Project file with the two subprojects,
they have everything in one file. Just keep everyone in the same folder.
--
If this post was helpful, please consider rating it.

Jim
It''s software; it''s not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

 

[Jack Dahlgren]

You can not override the Total Slack column. It is calculated. You can only
change Total slack by changing task duration or dependencies.
You could have a macro which sets the duration of the LOE tasks to Project
duration - 1 day. It would be rather simple to do.

In pseudocode:

Run through all tasks and find the finish date of the latest non-LOE task
Run through all tasks and set the remaining duration of all LOE tasks so
they complete 1 day (1 week, 1 hour ...) prior to the finish date determined
above.
Recalculate schedule.

You could then make a view with all the LOE tasks filtered out.

I can think of number of other ways to do it as well, but this one seems
simplest. Probably a dozen lines of code.

-Jack Dahlgren