Using VBA to do resource levelling. RRS feed

  • Question

  • Hi all! I know that my question here can sound perhaps a little crazy but I think I have not much choice but to attempt trying to to level the resources with VBA. Before I start conducting my research in my attempt to write the algorithm I would love some pointers from the experts here because I am new to MS project 2010.

    The reason why I am considering using VBA to level is because I don't want MS project 2010 to split the summary task (i.e delay certain task within the summary task as a result of resource leveling) I have asked the question here

    I would really really appreciate feed back and pointers to the best place to get started. Thank you in advance.

    PS: I am not a season VBA coder but I have fair experiences with and

    Tuesday, November 6, 2012 3:38 PM

All replies

  • I agree with one of the posters there, leveling is designed to do what you don't want it to, so don't use leveling. Instead, Link all important tasks and any driven by logical sequences. The look at overloaded short supply hard to get resources and link some of their tasks so they are sequential. Set all these tasks to priority 1000 so they are not leveled then level remaining resources.

    I know VBA can do a lot, but leveling manually in more complex scenarios is quicker then leveling automatically.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Wednesday, November 7, 2012 8:10 AM
  • Hi Rod Gill!

    I agree with you, however the nature of my project scheduling doesn't require such intricacy or detail in the planning. I can get away with it because at the site the supervisors will do what makes more sense to the situation. The planning results is just a guild for the amount of time spent.

    Therefore automatic leveling can do for my case. So back to the topic, I am still trying to solve my problem with using priority else if all fails I will have to code up a leveling algorithm.

    PS: I am designing an automated planning system that is why.

    Wednesday, November 7, 2012 5:40 PM
  • Hi,

    I can imagine this sort of procedure ut it will not be an easy task - Leveling is quite complex. I am still in favor of looking for a priority-driven solution and don't fully understand in which cases it doesn't work. Could you send me the file you are working on (if necessary change task names to make it anonymous) showing whe-re the priority solution doesn't work?


    Thursday, November 8, 2012 8:40 AM
  • For construction projects the critical path is often a very important piece of information. Leveling often messes with the critical path. For this reason alone I level manually as the consequences of losing critical path accuracy are too big.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Thursday, November 8, 2012 10:20 PM
  • Hi Jan thanks again.

    My company have been using Primavera 3 and 6 for donkey years. So the reason why I am exploring MS project is because of the automation advantages. The planners using P6 rarely ever level manually so that is why I thought this might work out.

    Jan, I will send the MS project file shortly.  Let me key in the data.(ms project have never been used here)

    Basically we use job cards. each JC have say 10 task. Each task have one type of manpower resource like rigger or scaffolder(I have 11 different types).

    The problem happen when a bunch of JC are allowed to run concurrently(as per the linking of the task) That is when MS project will split the summary task. I hope you are right that I can use priority to solve the problem, I am currently exploring that avenue. If you have any great ideas about using priority to solve my problem that will be awesome.

    Friday, November 9, 2012 4:35 AM
  • Hi Mr Gill,

    If that is the case we can ask MS project to level within the slack. That will solve the problem, no?

    Friday, November 9, 2012 4:37 AM
  • My experience says when time is tight, critical path is No 1 priority. I would then never level my schedule preferring to manually level. Project can only delay tasks, manual leveling allows many other solutions, some of which may even save more time.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Friday, November 9, 2012 9:06 PM