Help! Make this code work for any Project Text column...

M

Mark Durrenberger

Thank Jack D for writing this little ditty for summing up activities at
summary tasks.
Now I'd like a single routine for ANY text column... The version you see
below works for only Text14...

Sub SumSummaryText14()

Dim OutlineIndent As Integer
Dim MaximumOutlineIndent As Integer
Dim TemporarySum As Double
Dim Children As Tasks
Dim Child As Task
Dim Activity As Task
MaximumOutlineIndent = 0
'
' Determine maximum number of outline levels
'
For Each Activity In ActiveProject.Tasks
If Not Activity Is Nothing Then ' check for null activities
If Activity.OutlineLevel > MaximumOutlineIndent Then
MaximumOutlineIndent = Activity.OutlineLevel
End If 'outline level
End If ' nothing
Next Activity
'
' Start at the lowest level and sum things up
'
For OutlineIndent = MaximumOutlineIndent To 0 Step -1
For Each Activity In ActiveProject.Tasks
If Not Activity Is Nothing Then
If Activity.Summary And (Activity.OutlineLevel = OutlineIndent) Then
TemporarySum = 0
Set Children = Activity.OutlineChildren
For Each Child In Children
TemporarySum = TemporarySum + val(Child.Text14)
Next Child
Activity.Text14 = CStr(TemporarySum)
End If ' summary
End If 'nothing
Next Activity
Next OutlineIndent

End Sub


One suggestion is to use a case statement - I don't know how to make that
work - sure I understand the case statement

Call SumSummary("Text20")

Sub SumSummary(Column as string)

Select Case Column
Case "Text20" ? What goes here?
Case "Text19" ? What goes here?
Case "Text18" ? What goes here?
End Select


Thanks,
Mark
 
J

JackD

First I'd use the recursion version.
Then I'd copy the recursion procedule 30 times modifying the field that is
used.

recur1
recur2
....

Then case statement in the main procedure.

myChoice = inputbox(prompt and stuff here)

Select case myChoice
case = "Text1"
recur1
case = "Text2"
recur2
....
case else
msgbox "You have not made a valid choice"
End Select


Note that using in a text field may cause some problems. What result are you
going to expect if you sum text values?

-Jack
 
M

Mark Durrenberger

Re:text value - for simplicity, I pulled the code out that converts the text
number to a number - roll with it for now and assume that I do know what I'm
doing when adding numbers in a text column...

30 recursive routines will solve the problem (and that's what I'm doing in
the meantime) but come on, there has got to be a better way...

this is more a learning opportunity that I first imagined - and I've spent a
good part of the day googling around trying out different solutions to no
avail.

The 30 routines are a kludge I'm willing to live with (actually I only need
5 or 6) but I still want to solve the problem the right way.

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
 
R

Rod Gill

Hi,

activeproject.Tasks(1).GetField(pjTaskText1) returns the value in Text1, so
all you would need is a Select Case statement to call the routine with the
correct value constant that represents the relevant text field. Than pass
that value to getfield (). One routine should therefore fit the bill?

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/
 
J

JackD

Still a 30 element case statement though, right?
I'm not seeing anyway to have the code modify itself on the fly.

-Jack
 
J

John

Jack,
Maybe I don't quite understand what Mark is trying to do and maybe I'm
jumping in out of turn, but there shouldn't be a need to use 30 case
statements. Using Rod's idea, the argument of the GetField method can be
set up as a variable. Simply set up a loop to modify the variable (e.g.
Text1, then Text2, etc.) and use it as the argument for the GetField
method. So in effect, the code is being modified on the fly.

I used this method in a macro I wrote back in 1999 for comparing two
Project files. I needed to be able to change the field I was comparing
each time through a loop.

John
 
R

Rod Gill

Hi,

I think you would need three loops as the constants for pjText1-30 come in 3
groups of numbers. They're not contiguous.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/
 
J

John

Rod,
But I think you can also simply construct the constant using text (i.e.
"pjTextx" where "x" is in a single loop. I'd have to try this to see if
it works though.

John
 
J

John

Rod,
As a followup to my post of yesterday, I tried the idea of constructing
the constant using text. It didn't work. However, it can still be done
with only one loop. Even though the three groups of long constants are
not contiguous, a simple 'if' statement can be used to detect the "end"
of each group and then change the "seed" index used to start the next
group.

John
 
M

Mark Durrenberger

Rod, Jack (and other readers)
I've tried the "getfield" method - and though I'm closer, I can't quite get
it to work. See the code below.

The compiler gives an error message on the line surrounded by '**********
comments.

The message is: "Function call on left hand side of assignment must return
Variant or Object"

other things I tried (that failed) are below the code listing

Your thoughts,
Mark

Option Explicit
Sub test()
Call oakSumSummaryText(pjTaskText17) ' pass the working column to the
summing routine
End Sub
'======
Sub oakSumSummaryText(Field As Long)

Dim OutlineIndent As Integer
Dim MaximumOutlineIndent As Integer
Dim TemporarySum As Double
Dim Children As Tasks
Dim Child As Task
Dim Activity As Task
Dim j As Variant

MaximumOutlineIndent = 0
' Determine maximum number of outline levels
For Each Activity In ActiveProject.Tasks
If Not Activity Is Nothing Then ' check for null activities
If Activity.OutlineLevel > MaximumOutlineIndent Then
MaximumOutlineIndent = Activity.OutlineLevel
End If 'outline level
End If ' nothing
Next Activity

' Start at the lowest level and sum things up

For OutlineIndent = MaximumOutlineIndent To 0 Step -1
For Each Activity In ActiveProject.Tasks
If Not Activity Is Nothing Then
If Activity.Summary And (Activity.OutlineLevel = OutlineIndent) Then
TemporarySum = 0
Set Children = Activity.OutlineChildren
For Each Child In Children
TemporarySum = TemporarySum + Val(Child.GetField(Field)) ' This
works fine
Next Child
'********************************************
Activity.GetField(Field) = CStr(TemporarySum) '!!!!!!!!!! This boms
the compiler...
'********************************************
End If ' summary
End If 'nothing
Next Activity
Next OutlineIndent

End Sub

'
I also tried

dim J as variant
J = activity.getfield(field)
Activity.J = CStr(TemporarySum)
 
K

Khal

How about using SetField() instead?
-----Original Message-----
Rod, Jack (and other readers)
I've tried the "getfield" method - and though I'm closer, I can't quite get
it to work. See the code below.

The compiler gives an error message on the line surrounded by '**********
comments.

The message is: "Function call on left hand side of assignment must return
Variant or Object"

other things I tried (that failed) are below the code listing

Your thoughts,
Mark

Option Explicit
Sub test()
Call oakSumSummaryText(pjTaskText17) ' pass the working column to the
summing routine
End Sub
'======
Sub oakSumSummaryText(Field As Long)

Dim OutlineIndent As Integer
Dim MaximumOutlineIndent As Integer
Dim TemporarySum As Double
Dim Children As Tasks
Dim Child As Task
Dim Activity As Task
Dim j As Variant

MaximumOutlineIndent = 0
' Determine maximum number of outline levels
For Each Activity In ActiveProject.Tasks
If Not Activity Is Nothing Then ' check for null activities
If Activity.OutlineLevel > MaximumOutlineIndent Then
MaximumOutlineIndent = Activity.OutlineLevel
End If 'outline level
End If ' nothing
Next Activity

' Start at the lowest level and sum things up

For OutlineIndent = MaximumOutlineIndent To 0 Step -1
For Each Activity In ActiveProject.Tasks
If Not Activity Is Nothing Then
If Activity.Summary And (Activity.OutlineLevel = OutlineIndent) Then
TemporarySum = 0
Set Children = Activity.OutlineChildren
For Each Child In Children
TemporarySum = TemporarySum + Val(Child.GetField (Field)) ' This
works fine
Next Child
'********************************************
Activity.GetField(Field) = CStr
Click to expand...
(TemporarySum) '!!!!!!!!!! This boms
 
J

John

Mark,
Well yes, using SetField is appropriate for the second use (i.e. where
you got the failure). In the "child" loop you are gathering values from
the subtasks so GetField is appropriate. However, immediately after that
loop you are trying to put the resultant value into the field at the
summary line and SetField is appropriate for that.

John
 
J

JackD

John said:
Rod,
As a followup to my post of yesterday, I tried the idea of constructing
the constant using text. It didn't work. However, it can still be done
with only one loop. Even though the three groups of long constants are
not contiguous, a simple 'if' statement can be used to detect the "end"
of each group and then change the "seed" index used to start the next
group.

John
Click to expand...

OK, so post it.

I'm curious to see an example using getfield

-Jack
 
J

John

Jack,
With regard to my statement about using a "seed" index, I was simply
queuing off of Rod's statement that there are three non-contiguous
groups of constants for the field IDs, I didn't actually check all 30 to
see how the groups are made up. However, it is always possible to come
up with an algorithm to do the indexing.

I lifted the following code snippit from my FileCompare macro. In this
macro, "rfieldid" is the ID of the selected cell and later,
"FName(colcount)" is from a lookup table. If I were to re-write the
macro today, I would probably simplify the process (i.e. eliminate some
foreground processing (selection of cells) and use a more efficient
method in place of a lookup table).

For Each t In area
BaseRFarr(BaseRowNum) = t.GetField(FieldID:=rfieldid)
BaseIDarr(BaseRowNum) = t.ID
For colcount = 3 To TotFields
BaseFields(BaseRowNum, colcount) = _
t.GetField(FieldID:=FName(colcount))
Next colcount
If BaseRowNum < BaseTasks Then
BaseRowNum = BaseRowNum + 1
End If
Next t

Did this address your curiosity?

John
 
M

Mark Durrenberger

Well, I guess I would have used it If I knew it existed ... thanks for the
tip. I'll drop it in and try out the new version...

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
 
J

John

Jack,
I actually had forgotten about the GetField method until Rod mentioned
it. I don't think I've used it since I wrote the compare macro several
years ago.

I forgot to mention another example. Check the thread above, Mark has
incorporated the GetField method in what I think is some code you
provided to him originally.

John
 
M

Mark Durrenberger

Hi all, here is the (non recursive) version of the code - his now works for
any text field but is easily modified for any number field as well (an
exercise left for the reader :) thanks for the assistance.
Mark

Sub oakSumSummaryText(ProjectField As Long)

Dim OutlineIndent As Integer
Dim MaximumOutlineIndent As Integer
Dim TemporarySum As Double
Dim Children As Tasks
Dim Child As Task
Dim Activity As Task

blnPublicSkipOnChange = True ' be sure to skip the on-change event code

MaximumOutlineIndent = 0
'
' Determine maximum number of outline levels
'
For Each Activity In ActiveProject.Tasks
If Not Activity Is Nothing Then ' check for null activities
If Activity.OutlineLevel > MaximumOutlineIndent Then
MaximumOutlineIndent = Activity.OutlineLevel
End If 'outline level
End If ' nothing
Next Activity
'
' Start at the lowest level and sum things up
'
For OutlineIndent = MaximumOutlineIndent To 0 Step -1
For Each Activity In ActiveProject.Tasks
If Not Activity Is Nothing Then
If Activity.Summary And (Activity.OutlineLevel = OutlineIndent) Then
TemporarySum = 0
Set Children = Activity.OutlineChildren
For Each Child In Children
TemporarySum = TemporarySum +
oakGetHours(Child.GetField(ProjectField)) '*****
'**** oakgethours is a routine I wrote to read the text number from the
specified field,
'**** determine the units and convert them to hours
Next Child
Activity.SetField ProjectField, CStr(TemporarySum) & " hrs" '*****
setfield
End If ' summary
End If 'nothing
Next Activity
Next OutlineIndent

blnPublicSkipOnChange = False ' Reset the on-change event

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
Enterprise Global Macros not valid for Project 0
macro is to update column 13 and 14 of worksheet "Voice BB Pending"as per column 2 and 3 of "Activit 1
whats wrong with this vba code? 1
InputBox for column letter problem 2
Advice on multiple macros, same code just a different column 9
Too much data for this code? 4
Macro - copy outline level 0 down 2

Top