Fixedcosts using TimeScaleData method does not seem to work! Anysuggestions?

[Rudiger Wolf]

Hi

I was wondering if there was a bug with the TimeScaleData method?

I'm trying to get hold of the fixed costs associated with a task. When I
run the subroutine below with Type:= pjTaskTimescaledFixedCost or Type:=
pjTaskTimescaledActualFixedCost the resultant data is always zero.

BUT when I use Type:=pjTaskTimescaledCumulativeCost then I get the
expected values coming back.

Is this a bug in the method or am I doing something wrong? How do I get
the time scaled fixed costs out of MS-Project via VBA?

Thanks
Rudi Wolf

Sub FixedCostsPerDay()

For Each T In ActiveProject.Tasks
If Not T Is Nothing Then
First = T.Start
Last = T.Finish
Debug.Print T.Name

Set TaskFixedCosts = T.TimeScaleData(First, Last, _
Type:=pjTaskTimescaledCumulativeCost, _
TimescaleUnit:=pjTimescaleDays)

Debug.Print TaskFixedCosts.Count
For i = 1 To TaskFixedCosts.Count
theFC = TaskFixedCosts(i).Value
If IsNumeric(theFC) Then Debug.Print "Fixed Cost for day
i=" & i & " £ = " & theFC
Next i
End If
Next T

End Sub

 

[Rod Gill]

Hi,

The code below works for me. Note that it is much better to Dim all your
variables for more reliable code and faster running code. Note also that the
Fixed cost has to be Prorated otherwise all the cost is at the start or
finish of the task.

Sub FixedCostsPerDay()
Dim T As Task
Dim TaskFixedCosts As TimeScaleValues
Dim tsv As TimeScaleValue
Dim theFC As Currency
Dim First As Date, Last As Date
Dim i As Long

For Each T In ActiveProject.Tasks
If Not T Is Nothing Then
First = T.Start
Last = T.Finish
Debug.Print T.Name

Set TaskFixedCosts = T.TimeScaleData(First, Last, _
Type:=pjTaskTimescaledCumulativeCost, _
TimescaleUnit:=pjTimescaleDays)

Debug.Print TaskFixedCosts.Count
For Each tsv In TaskFixedCosts
theFC = Val(tsv.Value)
Debug.Print "Fixed Cost for day " & tsv.Index & " = £" &
theFC; ""
Next
End If
Next T
End Sub

 

[Rudiger Wolf]

Hi Rod

Thanks for your code. This works well for me in MSP2003.

But why does the TimeScaleData method not return any fixed costs only
when change the type to extract only the fixed costs rather than the
Cumulative Cost.

Type:=pjTaskTimescaledFixedCost

Thanks
Rudi Wolf

 

[Rod Gill]

What type is your fixed cost, Prorata, start or finish?

 

[Rudiger Wolf]

What type is your fixed cost, Prorata, start or finish?

Start.

Task fixed cost shows up in the cumulative total as expected but not
individually.
I get the same effect when using the time data export to excel wizard in
the analysis add in.

Rudi

 

[John]

Start.

Task fixed cost shows up in the cumulative total as expected but not
individually.
I get the same effect when using the time data export to excel wizard in
the analysis add in.

Rudi

Rudi,
Pardon me for jumping in (Rod is probably off-line for a while). If your
Fixed Cost accrues at the start then it has no timescale spread. However
Cumulative Cost will still show a "spread" because it is "cumulative".
Note that the cumulative value does not increase over time because it
was all accrued at the beginning.

I would hope the Analyze Timescale Data in Excel utility/add-in gives
the same result - it uses the TimeScaleData Method in its code.

Hope this helps.
John
Project MVP

 

[Rod Gill]

To see fixed cost spread over the duration of the task, you need Prorata as
the fixed cost type.

 

[Rudiger Wolf]

To see fixed cost spread over the duration of the task, you need Prorata as
the fixed cost type.

Hi Rod & John

I've found the problem!

I have Calculation Mode for project set on Manual.

For some reason changing a tasks fixed cost in the gantt view will not
produces any result when running TimeScaleData, before recalculating
with F9, with Type:=pjTaskTimescaledFixedCost yet it does for
Type:=pjTaskTimescaledCumulativeCost.

By adding the line "Application.CalculateAll" to the subroutine I have
been able to get the desired result. Below is the code I'm now playing
with. It enables me to write to a file, readable by excel. By Creating
a similar subroutine for resources and combining it with the fixed cost
data I am able to see what cash flow is from a resource point of view
for the project.

Thanks for your help.

Rudi


Sub FixedCostCashFlowPerDay()
Dim T As Task
Dim TaskFixedCosts As TimeScaleValues
Dim tsv As TimeScaleValue
Dim theFC As Currency
Dim First As Date, Last As Date
Dim i As Long

Dim FileLine As String
Dim MyFile As String
Dim fnum As Integer

FileLine = ""

'set location and name of file to be written
MyFile = "C:\Temp\" & ActiveProject.Name & "_Project_FixedCosts" & ".csv"

'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum

'write Column Headers
FileLine = "Task,Date,FixedCost"
Print #fnum, FileLine

Application.CalculateAll


For Each T In ActiveProject.Tasks
If Not T Is Nothing Then
First = T.Start
Last = T.Finish

Set TaskFixedCosts = T.TimeScaleData(First, Last, _
Type:=pjTaskTimescaledFixedCost, _
TimescaleUnit:=pjTimescaleDays)

'Debug.Print TaskFixedCosts.Count
For Each tsv In TaskFixedCosts
theFC = Val(tsv.Value)
If theFC > 0 Then
FileLine = T.Name & "," & First + (tsv.Index -
1) & "," & theFC
Print #fnum, FileLine
End If
Next
End If
Next T

Close #fnum

End Sub

 

[John]

Hi Rod & John

I've found the problem!

I have Calculation Mode for project set on Manual.

For some reason changing a tasks fixed cost in the gantt view will not
produces any result when running TimeScaleData, before recalculating
with F9, with Type:=pjTaskTimescaledFixedCost yet it does for
Type:=pjTaskTimescaledCumulativeCost.

By adding the line "Application.CalculateAll" to the subroutine I have
been able to get the desired result. Below is the code I'm now playing
with. It enables me to write to a file, readable by excel. By Creating
a similar subroutine for resources and combining it with the fixed cost
data I am able to see what cash flow is from a resource point of view
for the project.

Thanks for your help.

Rudi

Rudi,
You're welcome, I think. I guess we must have misunderstood your
question. I interpreted your post to ask why you couldn't see fixed cost
timescaled values over the period. Rod asked if you had fixed cost
accrual set to start, prorated, or finish. You said start. I then
explained why that would not give any fixed cost spread but why it WOULD
give cumulative cost spread. Your "fix" was to add the CalculateAll
Method to the code. Well, you can calculate until all the glaciers on
earth melt and it will still not generate timescale values for fixed
cost that accrues at the start. However I do note in your latest code
that data is only written to the .csv file for timescale values greater
than zero. That will be one value, the first one.

But if you're happy, we're happy.
John