calculating Duration1..20 
Author Message
 calculating Duration1..20

Hi,
I have a project which I use Start1  & finish1 and so on.

I am finding a way to automatically recalculate the corresponding
duration1..20 when I make changes in Start1 or finish1.

One way I can think of is to use Project's VBA Project_Change event
but I do not know how to check which field is being changed. I can't
use activecell as upon "enter" key the activecell is jump to next task
..

any idea anyone?

Thanks
VBA newbie



Sat, 02 Jul 2005 18:02:39 GMT  
 calculating Duration1..20
If you are using Project 2000 or 2002 this would be a good use for a
calculated field rather than VBA.
The advantage of calculated fields is that they recalculate automatically
without need for any events.
Their limit is that they can only work with data specific to each task (or
assignment) and can't access the values of other tasks.

Post what it is you want calculated and someone can verify that it is
possible with a calculated field instead of VBA.

--
Please try to keep replies in this group. I do check e-mail, but only
infrequently.
For Macros and other things check http://masamiki.com/project

-Jack Dahlgren, Project MVP

+++++++++++++++++++

Quote:
> Hi,
> I have a project which I use Start1  & finish1 and so on.

> I am finding a way to automatically recalculate the corresponding
> duration1..20 when I make changes in Start1 or finish1.

> One way I can think of is to use Project's VBA Project_Change event
> but I do not know how to check which field is being changed. I can't
> use activecell as upon "enter" key the activecell is jump to next task
> ..

> any idea anyone?

> Thanks
> VBA newbie



Sun, 03 Jul 2005 02:53:29 GMT  
 calculating Duration1..20
Ming,
If what you are trying to do is calculate the duration difference
between start1 through 20 and finish1 through 20 for each task and put
those values into the duration1 through 20 fields respectively then
you should be able to do that easily by putting the following formula
into each of the duration fields:

durationX = DateDifference(startX, finishX) / 480
where "X" is the field number

An alternate approach is to use the following macro. The macro will
also work with Project 98 which does not have cell formula
functionality, except the code will need to be modified since there
are only 10 start, finish and duration fields. The "480" factor
assumes an 8 hour work day.

Sub Cal_Dur()
For Each t In ActiveProject.tasks
    If Not t Is Nothing Then
        For i = 1 To 20
            Select Case i
                Case 1
                    Dur = Application.DateDifference(t.Start1,
t.finish1)
                Case 2
                    Dur = Application.DateDifference(t.Start2,
t.Finish2)
                Case 3
                    Dur = Application.DateDifference(t.Start3,
t.Finish3)
                Case 4
                    Dur = Application.DateDifference(t.Start4,
t.Finish4)
                Case 5
                    Dur = Application.DateDifference(t.Start5,
t.Finish5)
                Case 6
                    Dur = Application.DateDifference(t.Start6,
t.Finish6)
                Case 7
                    Dur = Application.DateDifference(t.Start7,
t.Finish7)
                Case 8
                    Dur = Application.DateDifference(t.Start8,
t.Finish8)
                Case 9
                    Dur = Application.DateDifference(t.Start9,
t.Finish9)
                Case 10
                    Dur = Application.DateDifference(t.Start10,
t.Finish10)
                Case 11
                    Dur = Application.DateDifference(t.Start11,
t.Finish11)
                Case 12
                    Dur = Application.DateDifference(t.Start12,
t.finish12)
                Case 13
                    Dur = Application.DateDifference(t.Start13,
t.finish13)
                Case 14
                    Dur = Application.DateDifference(t.Start14,
t.finish14)
                Case 15
                    Dur = Application.DateDifference(t.Start15,
t.finish15)
                Case 16
                    Dur = Application.DateDifference(t.Start16,
t.finish16)
                Case 17
                    Dur = Application.DateDifference(t.Start17,
t.finish17)
                Case 18
                    Dur = Application.DateDifference(t.Start18,
t.finish18)
                Case 19
                    Dur = Application.DateDifference(t.Start19,
t.finish19)
                Case 20
                    Dur = Application.DateDifference(t.Start20,
t.finish20)
            End Select
            Dur = Dur / 480
            FNam = "duration" & CStr(i)
            SetTaskField Field:=FNam, Value:=Dur, TaskID:=t.ID
        Next i
    End If
Next t
End Sub

John



Sun, 03 Jul 2005 13:06:32 GMT  
 calculating Duration1..20
Great .. one more question .. I can only use formula field for
duration1-20 .. can I do the same for baseline duration?
I have a project that the customer want to "adjust the baseline" (you
know some customers...) and I after adjusting the baseline start and
finish, the duration doesn't recalculate.

Ming Kiat

Quote:

> If you are using Project 2000 or 2002 this would be a good use for a
> calculated field rather than VBA.
> The advantage of calculated fields is that they recalculate automatically
> without need for any events.
> Their limit is that they can only work with data specific to each task (or
> assignment) and can't access the values of other tasks.

> Post what it is you want calculated and someone can verify that it is
> possible with a calculated field instead of VBA.

> --
> Please try to keep replies in this group. I do check e-mail, but only
> infrequently.
> For Macros and other things check http://masamiki.com/project

> -Jack Dahlgren, Project MVP

> +++++++++++++++++++


> > Hi,
> > I have a project which I use Start1  & finish1 and so on.

> > I am finding a way to automatically recalculate the corresponding
> > duration1..20 when I make changes in Start1 or finish1.

> > One way I can think of is to use Project's VBA Project_Change event
> > but I do not know how to check which field is being changed. I can't
> > use activecell as upon "enter" key the activecell is jump to next task
> > ..

> > any idea anyone?

> > Thanks
> > VBA newbie



Mon, 04 Jul 2005 11:33:22 GMT  
 calculating Duration1..20
I was thinking about recalculating the duration fields in all task in
an macro but I'm worrying about performance. My current project has
about 6000 tasks over 1.5 years .. making any change on any tasks and
I have to loop through 6000 tasks sound like a performance nightmare..

Is there a way I just recalculate on the task that I've just modified?

Thanks for the help,
Ming Kiat

Quote:

> Ming,
> If what you are trying to do is calculate the duration difference
> between start1 through 20 and finish1 through 20 for each task and put
> those values into the duration1 through 20 fields respectively then
> you should be able to do that easily by putting the following formula
> into each of the duration fields:

> durationX = DateDifference(startX, finishX) / 480
> where "X" is the field number

> An alternate approach is to use the following macro. The macro will
> also work with Project 98 which does not have cell formula
> functionality, except the code will need to be modified since there
> are only 10 start, finish and duration fields. The "480" factor
> assumes an 8 hour work day.

> Sub Cal_Dur()
> For Each t In ActiveProject.tasks
>     If Not t Is Nothing Then
>         For i = 1 To 20
>             Select Case i
>                 Case 1
>                     Dur = Application.DateDifference(t.Start1,
> t.finish1)
>                 Case 2
>                     Dur = Application.DateDifference(t.Start2,
> t.Finish2)
>                 Case 3
>                     Dur = Application.DateDifference(t.Start3,
> t.Finish3)
>                 Case 4
>                     Dur = Application.DateDifference(t.Start4,
> t.Finish4)
>                 Case 5
>                     Dur = Application.DateDifference(t.Start5,
> t.Finish5)
>                 Case 6
>                     Dur = Application.DateDifference(t.Start6,
> t.Finish6)
>                 Case 7
>                     Dur = Application.DateDifference(t.Start7,
> t.Finish7)
>                 Case 8
>                     Dur = Application.DateDifference(t.Start8,
> t.Finish8)
>                 Case 9
>                     Dur = Application.DateDifference(t.Start9,
> t.Finish9)
>                 Case 10
>                     Dur = Application.DateDifference(t.Start10,
> t.Finish10)
>                 Case 11
>                     Dur = Application.DateDifference(t.Start11,
> t.Finish11)
>                 Case 12
>                     Dur = Application.DateDifference(t.Start12,
> t.finish12)
>                 Case 13
>                     Dur = Application.DateDifference(t.Start13,
> t.finish13)
>                 Case 14
>                     Dur = Application.DateDifference(t.Start14,
> t.finish14)
>                 Case 15
>                     Dur = Application.DateDifference(t.Start15,
> t.finish15)
>                 Case 16
>                     Dur = Application.DateDifference(t.Start16,
> t.finish16)
>                 Case 17
>                     Dur = Application.DateDifference(t.Start17,
> t.finish17)
>                 Case 18
>                     Dur = Application.DateDifference(t.Start18,
> t.finish18)
>                 Case 19
>                     Dur = Application.DateDifference(t.Start19,
> t.finish19)
>                 Case 20
>                     Dur = Application.DateDifference(t.Start20,
> t.finish20)
>             End Select
>             Dur = Dur / 480
>             FNam = "duration" & CStr(i)
>             SetTaskField Field:=FNam, Value:=Dur, TaskID:=t.ID
>         Next i
>     End If
> Next t
> End Sub

> John



Mon, 04 Jul 2005 11:35:50 GMT  
 calculating Duration1..20
Ming,
Unless you have a really old slow PC, you might be surprised how fast
a macro will run with a 6000 task project. I recently wrote a macro
that transferred 10 years of resource data to Excel and fomatted it -
the macro ran in 13 seconds.

To answer your question. The macro I provided was a very basic
solution for my understanding of your issue. Was my understanding
correct? I also note from your reply to Jack's post that you want to
do something similar with the baseline duration field. That can be
incorporated into the macro as well. And, yes the macro can be
modified to recalculate just selected tasks (e.g. just tasks that were
modified). If you want to pursue the macro approach further, write me
direct, it will be much faster than post to Google.

John



Tue, 05 Jul 2005 01:23:33 GMT  
 calculating Duration1..20
No, you can not use a formula for the baseline. You can however write VBA
code to change the baseline if you want.
You could also just reset the baseline for the whole project or just
selected tasks. (tools menu/tracking/save baseline - then pick the options
you want)

--
Please try to keep replies in this group. I do check e-mail, but only
infrequently.
For Macros and other things check http://masamiki.com/project

-Jack Dahlgren, Project MVP

+++++++++++++++++++

Quote:
> Great .. one more question .. I can only use formula field for
> duration1-20 .. can I do the same for baseline duration?
> I have a project that the customer want to "adjust the baseline" (you
> know some customers...) and I after adjusting the baseline start and
> finish, the duration doesn't recalculate.

> Ming Kiat




Quote:
> > If you are using Project 2000 or 2002 this would be a good use for a
> > calculated field rather than VBA.
> > The advantage of calculated fields is that they recalculate
automatically
> > without need for any events.
> > Their limit is that they can only work with data specific to each task
(or
> > assignment) and can't access the values of other tasks.

> > Post what it is you want calculated and someone can verify that it is
> > possible with a calculated field instead of VBA.

> > --
> > Please try to keep replies in this group. I do check e-mail, but only
> > infrequently.
> > For Macros and other things check http://masamiki.com/project

> > -Jack Dahlgren, Project MVP

> > +++++++++++++++++++


> > > Hi,
> > > I have a project which I use Start1  & finish1 and so on.

> > > I am finding a way to automatically recalculate the corresponding
> > > duration1..20 when I make changes in Start1 or finish1.

> > > One way I can think of is to use Project's VBA Project_Change event
> > > but I do not know how to check which field is being changed. I can't
> > > use activecell as upon "enter" key the activecell is jump to next task
> > > ..

> > > any idea anyone?

> > > Thanks
> > > VBA newbie



Tue, 05 Jul 2005 02:53:58 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. PC Install from 20/20 software

2. Stateless search from database (Return 20 and 20 records)

3. How to make a sinus output from 20 Hz to 20 KHz

4. QB45: difference between DIM a$(20) and DIM a(20) AS STRING?

5. Online Chats: Week of May 20-24

6. Combining 20+ Tables into One

7. Dividing incoming email into groups of 20

8. Displaying More than 20 Custom Properties

9. Ann: Nebula control library 1.20.1000.0

10. New version of DownloadWunder.net (1.20.1000.1)

11. Distributing 20+Mb .Net Framework required??

12. 20 Jan 2002, How to create Keyboard Layout File(.kdb),Resouces,Help

 

 
Powered by phpBB® Forum Software