Sum at summary code

[Mark Durrenberger]

Hi folks, I'm looking for a piece of code to sum up a number column at
summary tasks (like what is done in the Work column).

I'm trying to write a recursive routine and now my brain hurts - If I
ever get mine to work, I'll post it here.

Anyone have something they are willing to share?

Thanks
Mark


--
_________________________________________________________
Mark Durrenberger, PMP
Principal, Oak Associates, Inc, www.oakinc.com
"Advancing the Theory and Practice of Project Management"
________________________________________________________

The nicest thing about NOT planning is that failure
comes as a complete surprise and is not preceded by
a period of worry and depression.

- Sir John Harvey-Jones

 

[JackD]

Have you tried the following:

right click column header
choose customize
Where it says "Calculation for group and summary tasks" click on "Rollup"
and choose "Sum" from the drop down box.

If you want code to do it, then it is fairly simple.

Sub mark()
Dim i, maxoutlinelevel As Integer
Dim tempsum As Long
Dim kids As Tasks
Dim kid, t As Task
maxoutlinelevel = 0

'find out how many levels of outline there are.
'this uses brute force - there may be an easier way
For Each t In ActiveProject.Tasks
If t.OutlineLevel > maxoutlinelevel Then
maxoutlinelevel = t.OutlineLevel
End If
Next t

'start at the lowest level and sum things up
For i = maxoutlinelevel To 0 Step -1
For Each t In ActiveProject.Tasks
If t.Summary And (t.OutlineLevel = i) Then
tempsum = 0
Set kids = t.OutlineChildren
For Each kid In kids
tempsum = tempsum + kid.Number4
Next kid
t.Number4 = tempsum
End If
Next t
Next i
End Sub

Add the usual stuff for checking that there are no blank tasks etc.
Recursion would be simpler, but not all that much simpler:

Sub runOnSummaryTask()
Dim t As Task
'clear all existing summary values
For Each t In ActiveProject.Tasks
If t.Summary Then
t.Number4 = 0
End If
Next t
'start recursive procedure
getkids ActiveProject.ProjectSummaryTask
End Sub

Sub getkids(t As Task)
Dim t1 As Task
For Each t1 In t.OutlineChildren
getkids t1
t.Number4 = t.Number4 + t1.Number4
Next t1
End Sub

-Jack

 

[Mark Durrenberger]

yes you are right not elegant but does the trick...

I'm actually working with numbers in a text field so I can't use the built
in stuff...

I'll modify what you've posted to work with 'text' numbers..

the "outline children" method is what I think i've been looking for.

Thanks
Mark


--
_________________________________________________________
Mark Durrenberger, PMP
Principal, Oak Associates, Inc, www.oakinc.com
"Advancing the Theory and Practice of Project Management"
________________________________________________________

The nicest thing about NOT planning is that failure
comes as a complete surprise and is not preceded by
a period of worry and depression.

- Sir John Harvey-Jones

 

[JackD]

You did see the recursion example too right? It is elegant.

-Jack

---------------------------------------------

 

[Mark Durrenberger]

Nope hadn't noticed. Yes it is pretty.
Mark


--
_________________________________________________________
Mark Durrenberger, PMP
Principal, Oak Associates, Inc, www.oakinc.com
"Advancing the Theory and Practice of Project Management"
________________________________________________________

The nicest thing about NOT planning is that failure
comes as a complete surprise and is not preceded by
a period of worry and depression.

- Sir John Harvey-Jones

 

[Mark Durrenberger]

Jack,
I'd like to pass "Number8" or "Number9" to the routine so the routine is
generic and works for any number field how is that done ...

I'm guessing
call mark(t.Number4)
'
sub mark(ColumnToSum as task)

then later:
tempsum = tempsum + kid.ColumnToSum

 

[JackD]

Um, I think that they introduced some way to get the name of a column and to
refer to it in code, but I'm not up to speed on it so I'm no help.

If I had to have it done today I'd probably do it with a form and have the
user pick one from the list, then use a tremendous case statement to execute
the correct code.

-Jack

 

[Mark Durrenberger]

Yes that would be one hell of a case statement

I've posted on VB General - perhaps I can get some help there...

Thanks
Mark


--
_________________________________________________________
Mark Durrenberger, PMP
Principal, Oak Associates, Inc, www.oakinc.com
"Advancing the Theory and Practice of Project Management"
________________________________________________________

The nicest thing about NOT planning is that failure
comes as a complete surprise and is not preceded by
a period of worry and depression.

- Sir John Harvey-Jones

 

[JackD]

Actually not too bad. Afterall, the actual code is only about three lines.

-Jack