Formulas-customize fields

[Helge Svee]

I want to put in information about amount of work(eg 10000 m2) and
capacity(eg 1,5manhours per m2) in two custom fields.

Based on this I want the amount of Work calculated. And from this calculated
Work I want the duration to automaticly be calculated when I already have
assigned resourses to the activity(activity type Fixed Units).

Is this possible?

 

[John]

I want to put in information about amount of work(eg 10000 m2) and
capacity(eg 1,5manhours per m2) in two custom fields.

Based on this I want the amount of Work calculated. And from this calculated
Work I want the duration to automaticly be calculated when I already have
assigned resourses to the activity(activity type Fixed Units).

Is this possible?

Helge,
Well, pretty much anything is possible, depending on how much effort you
are willing to expend. I assume when you say you want to Work changed
based on your formula you are referring to the Work field. The reason I
make this assumption is because you also call one of your proposed
custom fields, "work".

Of themselves, formulas in custom fields cannot alter the contents of
Project's basic fields - formulas can only calculate a value for the
custom field. However, there is a way to modify this functionality
although it may create other problems (e.g. corruption).

Create a custom field (e.g. Number1) with the formula based on entries
you have made in two spare fields (e.g. Number2 and Number3). Then Paste
Link this custom field value to the Work Field. As the data in the two
spare fields is changed, the custom field will re-calculate, (assuming
Tools/Options/Calculation tab is set for automatic). The link will then
change the Work field which will also then change the Duration.

The downside of the above approach is that Paste Links are prone to
corruption and therefore I don't recommend it. I personally would use
VBA. The downside with VBA is it of course requires a knowledge of how
to write Project macros, and changes to the Work and Duration fields
will only occur when the macro is run.

Hope this helps.
John
Project MVP

 

[Helge Svee]

Thank you for your answer.

I have two follow up questions for you.

Can you describe what you mean with: "prone to corruption...." It this
method unstable or is it other problem you have experienced.

And the next question: The solution using VBA code. Is it difficult to make?
I have done some VBA-coded macros in Excel before where I teached myself
using record functions and learned the code from these automatic functions.
Can you give me some advice or list some functions I will need. Eg. how to
get access to alter Projects basic fields..... and how to automaticly run
macros when changing values in certain fields.






John skrev:

 

[John]

Thank you for your answer.

I have two follow up questions for you.

Can you describe what you mean with: "prone to corruption...." It this
method unstable or is it other problem you have experienced.

And the next question: The solution using VBA code. Is it difficult to make?
I have done some VBA-coded macros in Excel before where I teached myself
using record functions and learned the code from these automatic functions.
Can you give me some advice or list some functions I will need. Eg. how to
get access to alter Projects basic fields..... and how to automaticly run
macros when changing values in certain fields.

Helge,
I wouldn't say paste links are unstable. They do work but they require a
lot of discipline to set up and maintain. It is very easy to corrupt a
paste link especially when breaking them. Most people don't realize that
the link has two parts because there is no indication in the source file
(i.e. Edit/Links) that a link exists. If not broken properly, a link
fragment can remain. Over time these fragments will corrupt the file.
This is one problem. I already mentioned the issue of moving the linked
files to different folders (or copying them). There are other issues
with updating paste links especially if there is a large number. The
bottom line - avoid them - there are better methods.

Depending on your level of VBA experience, the code to automate
re-linking can be easy or difficult. I have done it a couple of times
(the code is available but it is not freeware).

Learning VBA by recording macros is a great way to start however
recorded macros are not the most efficient and can't utilize the full
realm of Project objects. Recorded macros (it doesn't matter which
application), run with foreground processing. That is, it selects
objects in the current view and operates on them. VBA code that runs
with background processing is much more efficient and flexible. It
operates directly on Project's objects and is independent of the active
view. If you are interested in learning more about Project VBA there is
an excellent tutorial on our MVP website at:
http://www.mvps.org/project/links.htm
Go to the bottom of the page and look for the link to, "Project 98
Visual Basic Environment Training Materials". Even though it says it is
for Project 98, it is equally applicable to all current versions of
Project. The only real difference is that later versions of Project add
to the Project object library, allowing for greater flexibility.

Another good resource for users new to VBA is fellow MVP, Jack
Dahlgren's website at: http://masamiki.com/project/macros.htm
Jack has many Project VBA examples and some words of wisdom that should
help you get started.

John
Project MVP