1

having a bit of trouble getting started with this problem, and not much luck googling solutions.

I can't use MS Project to display gantt charts, so am wanting to use VBA to draw a pretty gantt chart in Excel from an Access database of tasks, time to complete task, preceding tasks, etc. I know VBA, but am having trouble conceptualising this problem. In other words I'm happy with the drawing, Access connection etc... just having trouble working out how to get the start date of each task given every task could theoretically effect every other task. enter image description here Attached very simple example of the problem... standard CPM stuff, where I have one example of A as a precedent to tasks B and D. If I think of this in terms of "paths" there's a path A-B-C, and a path A-D-E... If my code calculates the start point A based on A-B-C then that won't be enough time for A-D-E to complete by go live, so A would have to be based on A-D-E...

Seems simple but with 20 tasks with various precedents along the way, it gets complicated... i.e. I can loop through and find a bunch of unique paths, and I can find the "longest" path but that wouldn't necessarily give me the viable minimum start date for each task, as there may be other dependent tasks that branch off and take longer, therefore the original path needs to start earlier, then all of the tasks within that path are "shifted", so any calculations on related paths would then have to be shifted...

I feel compelled to write more about what I've tried to show I've at least made an effort but fear it may sound like giberish! Anyway: my first attempt had a recursive function that would loop through each activity, and so starting at A it would it would go "do i have subsequent task(s)" if it did, then the function would call itself again for B, then C, return that for B, return that for A... And it would loop through each of the subsequent tasks if it found more than one, and choose the "minimum" date... i.e. return E for D, then D for A, and choose D over B (D < B)

But I couldn't just loop through that same approach for each activity, as if i started at B, then found subsequent activities, I wouldn't get the "same B" as above... because that B was dependent on D and E (due to A).

So then i'm thinking well I either need to include a "look back" as well as a "look forward". But that gave me a bit of a headache. Or I really need to store my dates for each task as i go, so I could store a date for A, B, C... and then calculate the path A D E, which would mean i'd have to change A, B, and C again... but then once I have a large number of tasks, it would seem I would be going back and recalculating everything over and over again...

Am I on the right track? Seems like i'm tending towards kind of a "brute force" where i'm just looping through the same calculations over and over again until everything "settles" and finds it's minimum start date... I feel there's a "smart" way of doing it that I'm missing?

1 Answer 1

1

The terms that you are missing are "Critical Path" and also "Resource Availability". You need to determine the set of operations that all must be done in sequence that is the longest, in time, everything else can be fitted round that subject to available resources - if you only have one developer then she/he is the critical resource and the minimum time will simply be the total of all of the times.

There are a number of solutions, most based on path walking, and the reading of them is highly academic, but most actually work backwards in time at each point examining the prerequisites for the final delivery.

The "smart" thing to do is to buy in, or find a free, solution and use that, Excel is not a good tool for this, e.g. take a look at TaskJuggler and utilise your skills in providing an interface to the tool from Access, (if you are committed to using that).

Keep in mind that projects are live things, when delays occur or new dependencies are discovered, the analysis needs to be redone and priorities adjusted, extra resources obtained and/or the expected end date pushed out.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.