none
MS Project 2010 RRS feed

  • General discussion

  • Hi,

    Is there any way to count the number of resources( given in the resource name column ) using VBA code or any other method?

    Consider the resource names as R1, R2 and so on......I need the count of R1, R2 respectively...Please help me in this regard....

    Thanks in advance......

    Friday, July 12, 2013 6:36 AM

All replies

  • Hi there,

    I think you have a number of options:

    You are using the "project 2010 client" so you have the visual reports at your disposal. Now choose a new template and build it using the "resource summary" data type:

    New_Visual_Report

    You can dump all the fields in the field picker if you like, doesn't matter, and now you get a Excel sheet that you can use and give a =count() formula.<o:p></o:p>

    I hear you say something about characteristics of the resource, you will like to give that characteristic in a custom field and add that in the field picker :-)

    Happy BIing,

    Erik v H.

    Friday, July 12, 2013 7:13 AM
  • I think you have a number of options:

    The other option beeing OLAP cubes on project server :-)

    No need for VBA, this is Out of the box.

    Friday, July 12, 2013 7:15 AM
  • shyamalat --

    Using VBA, define an Integer variable such as intResCount.  Then use code such as the following in your macro:

    intResCount = ActiveProject.ResourceCount

    If you need to access the number of project resources in a formula, you can reference the following built-in field in your formula:

    [Resource Count]

    So, there are two other methods using either VBA or a formula.  Hope this helps.


    Dale A. Howard [MVP]

    Friday, July 12, 2013 7:32 PM
    Moderator
  • Hi thanks for your reply...I am yet to install MS Visio so that I can check the ways you mentioned. I will revert to you once I get it for further help.
    Tuesday, July 23, 2013 7:39 AM
  • A variation on Dale's VBA solution, which gives you a pop-up box with the number of resource:

    Sub DisplayResourceCount()
        MsgBox vbTab & ActiveProject.ResourceCount & vbTab, vbInformation, "Resource Count"
    End Sub


    Tuesday, July 23, 2013 8:08 AM
  • Hi Dale, Thanks for your reply.

    The code which you said gives the count of half of the total resources given in the "RESOURCE NAMESive of R1, R2). i.e giving 13 if 26 resources(R1,R2) were there. Help me to get individual resource count of R1, R2 separately....

    Tuesday, July 23, 2013 9:36 AM
  • H i Dale,

    One more thing...I used that code where resources are not mentioned in the "RESOURCE NAMES" column.

    Even in that condition it showed the count as 13....How come it is possible?...Can you give me a better solution for the same?

    Tuesday, July 23, 2013 10:06 AM
  • Ah, I think I now understand what you want.

    You want a count of the number of resources against a task, not against the whole project, which we had all assumed.

    This will give you a dialogue box with the count of resources against the first selected task.

    Sub DisplayResourceCount()
        MsgBox vbTab & ActiveSelection.Tasks(1).Assignments.Count & vbTab, vbInformation, "Resource Count"
    End Sub

    This routine will throw an error, however, if the active selection doesn't contain any tasks (i.e. if you're on the resource sheet view, or select an assignment in the task usage view), but should at least get you started.

    A more elegant solution would be to to have a routine loop through the tasks periodically and update a custom text column with the value (I don't think a formula would give you access to this info - that would be even neater.)


    • Edited by Andrew Simpson Tuesday, July 23, 2013 10:15 AM Correcting code
    Tuesday, July 23, 2013 10:15 AM