How to build a user-defined function to be used in formulas 
Author Message
 How to build a user-defined function to be used in formulas

Hi there,

Is it possible to build a function in VBA, and then use that function when
building a calculation formula for a user-defined field?
 ----
Example of where I need that:

 Often we plan common projects with a customer, where resources from the
 customer as well as our resources work on the same task.

 If I add the "work" field on the gant chart view, it gives me the
combined work of all resources (both companies)
This is correct, but I also want to add the information, what is the
work that only the resources of our company do.
So I thought it should be possible to assign resources from each company
into a different group, and add to the view a  user-defined field, lets
say Duration9.

Now the question is how to construct the formula for that user-defined
field.
Obviously, no built-in functions can give me the answer.
If I could build an own VBA function, than I could scan all assignments,
that
belong to the task, and sum up the work for each assignment resource,
that belongs to the resource group designating "our company".
I assume, some work-around could be made, like writing even handlers,
triggered each time a task is modified by user, and calculate and set
the userdefined value from the code. But this is highly messy.
----------------
Anybody can help on this?

 Dani



Thu, 13 Feb 2003 03:00:00 GMT  
 How to build a user-defined function to be used in formulas

To start with, I would simply create a report that does this for you.  This
will only work in Project 2000

View, Resource Usage
Project, Group, Resource Group
Collapse the assignments, then you will see total work for each resource
grouped by Resource Group.  This isn't exactly what you want, but is a good
start.

--
Rod Gill
Microsoft Project MVP
If you want Automated reporting across multiple project files and
Time sheets that seamlessly update Project files
Click www.projectlearning.com/ now.


Quote:
> Hi there,

> Is it possible to build a function in VBA, and then use that function when
> building a calculation formula for a user-defined field?
>  ----
> Example of where I need that:

>  Often we plan common projects with a customer, where resources from the
>  customer as well as our resources work on the same task.

>  If I add the "work" field on the gant chart view, it gives me the
> combined work of all resources (both companies)
> This is correct, but I also want to add the information, what is the
> work that only the resources of our company do.
> So I thought it should be possible to assign resources from each company
> into a different group, and add to the view a  user-defined field, lets
> say Duration9.

> Now the question is how to construct the formula for that user-defined
> field.
> Obviously, no built-in functions can give me the answer.
> If I could build an own VBA function, than I could scan all assignments,
> that
> belong to the task, and sum up the work for each assignment resource,
> that belongs to the resource group designating "our company".
> I assume, some work-around could be made, like writing even handlers,
> triggered each time a task is modified by user, and calculate and set
> the userdefined value from the code. But this is highly messy.
> ----------------
> Anybody can help on this?

>  Dani



Fri, 14 Feb 2003 05:31:16 GMT  
 How to build a user-defined function to be used in formulas
Dani

UDFs(User defined functions) are available in Excel but unfortunately not in
Project. So you may have to go ahead with your work around solution.

Regards
Venkata Krishna


Quote:
> Hi there,

> Is it possible to build a function in VBA, and then use that function when
> building a calculation formula for a user-defined field?
>  ----
> Example of where I need that:

>  Often we plan common projects with a customer, where resources from the
>  customer as well as our resources work on the same task.

>  If I add the "work" field on the gant chart view, it gives me the
> combined work of all resources (both companies)
> This is correct, but I also want to add the information, what is the
> work that only the resources of our company do.
> So I thought it should be possible to assign resources from each company
> into a different group, and add to the view a  user-defined field, lets
> say Duration9.

> Now the question is how to construct the formula for that user-defined
> field.
> Obviously, no built-in functions can give me the answer.
> If I could build an own VBA function, than I could scan all assignments,
> that
> belong to the task, and sum up the work for each assignment resource,
> that belongs to the resource group designating "our company".
> I assume, some work-around could be made, like writing even handlers,
> triggered each time a task is modified by user, and calculate and set
> the userdefined value from the code. But this is highly messy.
> ----------------
> Anybody can help on this?

>  Dani



Fri, 14 Feb 2003 03:00:00 GMT  
 How to build a user-defined function to be used in formulas
One tricky thing that you could do would be to actually make the
calculations in excel.  You can actually pase a range of cells from Project
into a range of cells in Excel. Then you can make the formula, etc in Excel
and paste the range of cells containing the results back into Project.. Use
Paste-Link as text both directions and be sure not to create a circular
reference. Dates are a bit problematic due the textual nature of the Project
Dates containing the units ("m","d","h"), etc.

Guy Cox, MS Project MVP


Quote:
> Dani

> UDFs(User defined functions) are available in Excel but unfortunately not
in
> Project. So you may have to go ahead with your work around solution.

> Regards
> Venkata Krishna



> > Hi there,

> > Is it possible to build a function in VBA, and then use that function
when
> > building a calculation formula for a user-defined field?
> >  ----
> > Example of where I need that:

> >  Often we plan common projects with a customer, where resources from the
> >  customer as well as our resources work on the same task.

> >  If I add the "work" field on the gant chart view, it gives me the
> > combined work of all resources (both companies)
> > This is correct, but I also want to add the information, what is the
> > work that only the resources of our company do.
> > So I thought it should be possible to assign resources from each company
> > into a different group, and add to the view a  user-defined field, lets
> > say Duration9.

> > Now the question is how to construct the formula for that user-defined
> > field.
> > Obviously, no built-in functions can give me the answer.
> > If I could build an own VBA function, than I could scan all assignments,
> > that
> > belong to the task, and sum up the work for each assignment resource,
> > that belongs to the resource group designating "our company".
> > I assume, some work-around could be made, like writing even handlers,
> > triggered each time a task is modified by user, and calculate and set
> > the userdefined value from the code. But this is highly messy.
> > ----------------
> > Anybody can help on this?

> >  Dani



Sun, 16 Feb 2003 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Criteria using a User-Defined function

2. user defined functions used in crystal report

3. (newbie) returning values from user-defined function using multiple arguments

4. VB project using excel user defined functions

5. using ISNA in an excel user-defined function

6. User Defined Formula

7. User Defined Formulas in Strings [help]

8. Functions - User Defined Functions

9. Pb w/ formulas : user defined text

10. User-defined error when the user doesn't define any errors

11. User Control - User-defined type not defined

12. User Defined Functions

 

 
Powered by phpBB® Forum Software