ms project vba assignment

Carl Sprake

• Walking Kit
• Walking Resources
• Oxfordshire Way
• Oxford Greenbelt Way
• Project 2013
• Project Server
• Project 2007 and PWA
• Project 2010

Using VBA To Check Task and Assignment Fields

I’m currently working on a project to create a macro for MS Project. The intention is that this macro will  fire either on Save or on the click of a button and will check to see if certain fields have been filled out for tasks and assignments. Any missing fields will be reported back to the user.

We use Project Server to generate a “Daily Log” where we can record work against tasks that have been assigned to us. In order to make the completion of the daily log as easy as possible for the end users and for the plan owner (not to mention our finance department), we want to be able to check that most of the required information is in place in the first place.

Task fields

It is relatively easy to loop through tasks and check for task level fields.

The easiest thing to do is to work with all the tasks in the current collection of tasks in the active project.

First we need to check that the task is actually a valid task (i.e. not a blank row) and that it is not a summary task.

Then we can populate a number of variables with the required values.

Here we can see that VBA has a way for us to get the value of task fields.

Assignment Fields

Reading assignment level custom field values is a bit more tricky. If the name of the custom field is one word (i.e. there are no spaces), then according to various other web sites I have looked at, for an assignment a; “a.fieldname” will do the trick. Unfortunately, if the field name does contain spaces then this method will not work.

Since I was unable to resolve the issue by working in the background I decided I had to work in the foreground (i.e. by working with a view).

To make sure that I covered all the task and could work with all the required fields I wanted to do the following:

• Use a Enterprise view
• Remove any filters
• Display all tasks

This can be achieved using the following code:

We can now loop through all the rows in the view. This is one area where I have used a bit of a hack. I haven’t found an easy way to count the number of used rows in the current view. However, I can count the number of tasks in the current project and I know that we rarely assign more than one person to a task, so the following should give me a high enough limit to count to.

The view I’m using is a Task Usage view and therefore contains the names of tasks as well as the resources assigned to those tasks. What I need to do is to differentiate between Tasks and Assignments.

Now I can use the SelectTaskField to select a specific cell in row r and column specified by a field name.

SelectTaskField

A couple of important points about this command:

• Column – this should be the real name of the column as stored in Project, not the name shown in the view. Hence the use of Column:=”Name”, rather than Column:=”Task Name”
• RowRelative – the default value for this is True and will therefore select a cell r rows down from the currently active cell. Therefore, this should be set to False to ensure that it is always row r that is used.

Having selected the cell in the “Name” column of row r we can read the text in the cell using:

It is also possible to query the name of the task associated with the currently active cell.

And we can therefore compare sTest1 with sTest2. If they are the same then we are looking at a Task. However, if they are different then we are looking at a resource assigned to a task and the current row is an assignment. We can therefore pick up cell values from other columns in the row and compare them to expected values.

Final Outline

So, the final outline of the code looks like this:

Share this:

• No comments yet.
• July 31, 2012 at 13:11 Using VBA To Check Task and Assignment Fields « Joined Up Solutions

Leave a comment Cancel reply

Recent posts.

• SW Coast Path
• Oxfordshire Way Circular Walk Route 8
• A Day For Photography
• Plant Identification
• Oxfordshire Way Circular Walk Route 7
• Gordale Scar and Malham Cove
• Climbing Shoes Insane Pain vs Comfort
• Countif and Countifs
• Microsoft Office Specialist–First step
• Shotover Atlas Project Survey 20150326
• 2013 Fundraiser
• General Musings and Rants
• Medieval Re-enactment
• Photography
• Documentation
• Suggest Ideas
• Support Forum
• WordPress Blog
• WordPress Planet
• February 2015
• January 2015
• December 2014
• November 2014
• September 2014
• August 2014
• December 2013
• October 2013
• September 2013
• August 2013
• January 2013
• December 2012
• September 2012
• August 2012
• January 2012
• December 2011
• November 2011
• October 2011
• September 2011
• August 2011
• February 2011
• January 2011
• December 2010
• November 2010
• October 2010
• September 2010
• August 2010
• January 2010
• December 2009
• November 2009
• October 2009
• September 2009
• August 2009
• February 2009
• January 2009
• December 2008
• November 2008
• October 2008
• September 2008
• August 2008
• February 2008
• January 2008

• Already have a WordPress.com account? Log in now.
• Subscribe Subscribed
• Copy shortlink
• Report this content
• View post in Reader
• Manage subscriptions
• Collapse this bar

Assignment fields

The following are the assignment fields in Microsoft Office Project. For more information, click a field name.

Actual Cost fields

Actual Finish fields

Actual Overtime Cost fields

Actual Overtime Work fields

Actual Start fields

Actual Work fields

ACWP fields

Assignment Delay fields

Assignment Units fields

Baseline Cost fields

Baseline Finish fields

Baseline Start fields

Baseline Work fields

Baseline1-10 Cost fields

Baseline1-10 Finish fields

Baseline1-10 Start fields

Baseline1-10 Work fields

BCWP fields

BCWS fields

Confirmed fields

Cost fields

Cost Rate Table fields

Cost Variance fields

Cost1-10 fields

Critical fields

Date1-10 fields

Duration1-10 fields

Enterprise Cost1-10 fields

Enterprise Date1-30 fields

Enterprise Duration1-10 fields

Enterprise Flag1-20 fields

Enterprise Number1-40 fields

Enterprise Resource Outline Code1-29 fields

Enterprise Text1-40 fields

Finish (assignment field)

Finish Variance fields

Finish1-10 fields

Flag1-20 fields

Hyperlink fields

Hyperlink Address fields

Hyperlink Href fields

Hyperlink SubAddress fields

Indicators fields

Leveling Delay fields

Linked Fields fields

Notes fields

Number1-20 fields

Outline Level fields

Overallocated fields

Overtime Cost fields

Overtime Work fields

Peak fields

Percent (%) Work Complete fields

Priority fields

Project fields

Regular Work fields

Remaining Cost fields

Remaining Overtime Cost fields

Remaining Overtime Work fields

Remaining Work fields

Request/Demand (assignment field)

Resource Group fields

Resource ID (assignment field)

Resource Initials fields

Resource Name (assignment field)

Resource Type (assignment field)

Response Pending fields

Start fields

Start Variance fields

Start1-10 fields

Task ID (assignment field)

Task Name (assignment field)

Task Outline Number (assignment field)

Task Summary Name fields

TeamStatus Pending fields

Text1-30 fields

Unique ID fields

Update Needed fields

Work fields

Work Contour fields

Work Variance fields

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Reading and writing MS Project TimeScaleData for Resources, and Assignments

I am pulling projected cost into Excel reports and an external db. Part of the monthly projections are to update the project overheads. So I need to accurately pull monthly cost as well as write monthly cost to the overheads tasks.

I am having trouble understanding why would you read and write to an Assignment instead of directly to the cost Resource?

Read the month cost by resource (simplified):

There is only one TimeScaleValue in tsvs (the single month) so I believe that I'm fine just referencing tsvs(1).Value instead of having to increment through the collection.

Read month cost with assignments:

And finally write the month overhead cost to the overhead task:

This method works for me but I am frustrated I cannot get it to work writing directly to the resource cost. I don't really understand why I would have to read or write to an assignment (either task or resource) instead of directly to the resource itself. Could anyone explain why one should read and write costs using assignments or if there is a better way using the resources directly?

Thanks for your time.

The answer is that the assignments table contains the TimeScaleData that relates the resource to the task (and vice versa), so you must write to the assignment related to the resource (or task). Resource TimeScaleData is really the related Assignment TimeScaleData of the resource.

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

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 vba ms-project or ask your own question .

• The Overflow Blog
• The hidden cost of speed
• The creator of Jenkins discusses CI/CD and balancing business with open source
• Featured on Meta
• Announcing a change to the data-dump process
• Bringing clarity to status tag usage on meta sites
• What does a new user need in a homepage experience on Stack Overflow?
• Feedback requested: How do you use tag hover descriptions for curating and do...
• Staging Ground Reviewer Motivation

Hot Network Questions

• Question about word (relationship between language and thought)
• Advanced Composite Solar Sail (ACS3) Orbit
• Filtering polygons by name in one column of QGIS Attribute Table
• Beatles reference in parody story from the 1980s
• How to clean a female disconnect connector
• The head of a screw is missing on one side of the spigot outdoor
• Do you believe something to be the truth or do you know the truth?
• lib/xorg/Xorg →lib/x86_64-linux-gnu/libc.so →xorg/modules/drivers/nvidia_drv.so causes a segmentation fault. Should I report that to Debian team?
• Can ago be used with since?
• What was the typical amount of disk storage for a mainframe installation in the 1980s?
• How long should a wooden construct burn (and continue to take damage) until it burns out (and stops doing damage)
• How does a miner create multiple outputs in a coinbase transaction?
• Circuit that turns two LEDs off/on depending on switch
• Are fuel efficiency charts available for mainstream engines?
• Starting with 2014 "+" signs and 2015 "−" signs, you delete signs until one remains. What’s left?
• What are the most common types of FOD (Foreign Object Debris)?
• Setting the desired kernel in GRUB menu
• What happens on a CAN bus when impedance is too low?
• Plausible orbit to have a visible object slowly circle over the night sky
• How high does the ocean tide rise every 90 minutes due to the gravitational pull of the space station?
• Where did they get facehuggers from?
• Is reading sheet music difficult?
• Looking for the name of a possibly fictional science fiction TV show
• Why does the guardian who admits guilt pay more than when his guilt is established by witnesses?

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Resource.SetField method (Project)

• 7 contributors

Sets the value of the specified resource custom field.

expression . SetField ( _FieldID_ , _Value_ )

expression A variable that represents a Resource object.

The following example shows how to access an enterprise resource custom field by using the SetField method and the GetField method for the Resource object together with the FieldNameToFieldConstant and FieldConstantToFieldName methods.

To use the example, use Project Web App to create an enterprise resource text custom field named, for example, TestEntResText .

Restart Project Professional with a Project Server profile, so that it includes the new custom field.

Create a project, build the team from enterprise resources, and then assign a resource to the first task.

The TestEnterpriseResourceCF macro uses the FieldNameToFieldConstant method to find the resourceField number, for example, 205553667.

The macro shows the number and text value in a message box, by using the GetField method.

The macro sets a new value for the custom field, by using the SetField method.

The macro gets the field name by using the FieldConstantToFieldName method, and then shows the field name and new value in another message box.

For an example that uses a local resource custom field, see the GetField method.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Was this page helpful?

Additional resources