Resource Usage view question

[Paul09]

Hello, this is my first post to these forums, so here it goes..

I am new to MS Project and need to make a small change in the way the the Resource Usage View displays information. I will use the following as an example:

What I need is to add a field for each group that displays the total remaining availability. I realize there is a Remaining Availability field, but I have a problem with it. I do not want to count the first resource in the calculation. Let me give an example.

For group "01 - PM - SR" I need to perform the following calculation: (100%-55% + 100%-123% + 100-80% + 100-58%) = 84%. This indicates that there is 84% of one resource still available this month. This 84% would ideally be inserted where the 67% currently is.

If anyone could point me in the right direction, or has any other suggestions, they would be greatly appreciated.

Thanks in advance,
Paul

 

[PDQBach]

You're going to need VBA for this and it isn't going to be easy.

First of all, %Alloc doesn't appear to be exposed in the object model which mean it's a derived value (or, if it is available but simply not exposed then you'll still need to derive it).

Second, you'll need to access TimePhaseData (somewhat time-consuming, but not horrifically difficult, to get it working the way you want it to work) so you can see how much work has been assigned to the resource which you'll need to know to derive the %Alloc number.

Finally, and I'm kinda guessing here because I wasn't prepared to do the TPD tests to get something useful out, you almost certainly will need to access the Resource Calendar for each resource (to find out how much time the resource was available for scheduling) and you will need to access the Max Units for the resource so you can do the calculation since a resource could be available for the entire month but only at 50% max units and you want your calculation to reflect that.

One other thought, you won't be able to do the calculation and then plug the result into the cells on the right side of the display. You'll have to create your own output (hardcopy or .csv for use as input to a spreadsheet -- or, I suppose, you could load Excel and push the values directly to it).

So: you need the VBA code to access TPD, resource calendar, and resource max units. Then you need to determine how you'll go about formatting the output.

Oh ... one other thing: thank you so much for using the

 

[Paul09]

PDQBach,

Thank you very much. That was exactly what I needed. By the way, I was unable to find a function named TimePhaseData, but TimeScaleData worked just as I needed, is this what you meant?

Also, if anyone is curious, I was able to access '% Allocated' through this method (it did not need to be derived).

Thanks again for the help,
Paul

 

[PDQBach]

Technically ... TimeScaleData is being replaced by TimePhaseData but they are the same.

I'm glad that you can get the %Alloc that way ... it'll save you immense effort, I expect.

Are you planning to load Excel and "push" (for lack of a better term) values to it or are you going to write the values to a .csv file and go frome there?

 

[Paul09]

I was planning on writing to .csv, but I just came across a staffing spreadsheet currently in use in which this information is calculated and entered by hand. I am looking to move the vba macro over to the spreadsheet and odbc into the project to eliminate having to open the project and run a macro.

Do you have any suggestions on how to go about this? (or if it is even feasible)

Thanks again,
Paul

 

[PDQBach]

Never tried that so I don't know if it can/can't be done.

In almost all cases, I write my VBA code in Excel, have it load Project and then suck the data back into the Excel spreadsheet. That way, I can start with an empty framework in the spreadsheet (with things like headings, formulas, etc.) and then do stuff like inserting rows knowing that the formula can be copied. In addition, with lots of formatting already done, it basically comes down to a macro that opens the template, copies it to a new workbook and then pulls the data from the MS Project file into the nice "pretti-fied" Excel cells.

There may be a more efficient approach but my worklife is more focused on "getting it working well" rather than "get it working the most elegant way possible."

If you are doing a lot of Project VBA you might want to get a copy of VBA for Project by Rod Gill. There are some other Project VBA links and resources scattered across the net, but this book seems to have just about everything that most people will ever need.

(BTW, you might want to come back here relatively often. There are often requests for solutions that need VBA and it seems like you'll be able to help these people out.)

 

[Paul09]

I will make sure to do that. Thanks.

Paul