TimeScaleData in MS Project shows "Unexpected error in method"

[Reinhard]

Hi there,

using the VBA code for accessing time scaled data I encounter the above
error message (Unexpected error in method - translated from my german
version) for the value field if reading more than nine elements (months).
I've read in various posts in the web I'm not alone here.
Does anyone have a suggestion for avoiding this trouble?

The following line of code ends up with my problem (German date format reads
as 09-30-2008 until 09-30-2009 in US format).

Set pTSV = .ActiveProject.ProjectSummaryTask.TimeScaleData("30.09.2008",
"30.09.2009", TimescaleUnit:=2)

I'm using MS Excel 2007 andMS Project 2007.

I've read various answers there is an issue when reading more than 9
elements. This can be fixed by using System.GC.collect() in VB.NET. But I'm
not using .NET.

Any help appreciated, I'm lost here.

Reinhard

 

[Rod Gill]

Hi,

What's the error and on what line of code, the TimeScaleData line or a line
reading the data? In VBA I don't know of any problem. I regularly produced
code reading more than 9 time slices.

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com

Hi there,

using the VBA code for accessing time scaled data I encounter the above
error message (Unexpected error in method - translated from my german
version) for the value field if reading more than nine elements (months).
I've read in various posts in the web I'm not alone here.
Does anyone have a suggestion for avoiding this trouble?

The following line of code ends up with my problem (German date format
reads
as 09-30-2008 until 09-30-2009 in US format).

Set pTSV = .ActiveProject.ProjectSummaryTask.TimeScaleData("30.09.2008",
"30.09.2009", TimescaleUnit:=2)

I'm using MS Excel 2007 andMS Project 2007.

I've read various answers there is an issue when reading more than 9
elements. This can be fixed by using System.GC.collect() in VB.NET. But
I'm
not using .NET.

Any help appreciated, I'm lost here.

Reinhard

__________ Information from ESET Smart Security, version of virus
signature database 5035 (20100416) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5035 (20100416) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Reinhard]

Hi Rod,

the error comes up from the line of code I posted, where the data returned
is invalid. Accessing the "Value" field in the pTSV I get the error in one of
the data elements (varies, sometimes the third, sometimes the tenth).

After having executed the line of code posted I can see in the VB debugger
the contents of the e.g. pTSV.Item12.Value. It shows:
: Value : <Unerwarteter Methodenfehler.> : Variant :
ProjectBudget.UpdateProjectData
The german phrase <Unerwarteter Methodenfehler> translates to <Unexpected
error in method> as far as my english allows to translate.

This I've found in the web as being a known issue in the TimeScaleData
function, as described in my initial posting.

I post the complete sequence of code (still under development) to show what
I aim at. The resources are given extra information (i.e. cost center
definition in Text2). I want to create an array where the first element
describes the cost center and the follwing twelve elements show the effort
read from the project plan. This gives good information about the resource
usage during the business year.

As described above my problem arises after examining the pTSV structure
having executed the first line of code, in addition during the nexecution I
marked the line of code really stopping the execution with the comment '####
error.

'use the information from the project summary task TSV
Set pTSV =
..ActiveProject.ProjectSummaryTask.TimeScaleData("01.10.2008", "30.09.2009",
TimescaleUnit:=2)

'loop through all resources and write out values
Set rs = .ActiveProject.Resources
For Each r In rs
If Not r Is Nothing Then

Set TSV = r.TimeScaleData("01.10.2008",
"30.09.2009", TimescaleUnit:=2) ' = months
'loop through all timescale data and write
to array
For iRover = 1 To TSV.Count
If Not TSV(iRover).Value = "" Then
'#### error
'work hours are written
Select Case r.Text2
Case MPPKSTArray(0, 0)
MPPKSTArray(0, iRover) =
MPPKSTArray(0, iRover) + TSV(iRover).Value / (60)

Case MPPKSTArray(1, 0)
MPPKSTArray(1, iRover) =
MPPKSTArray(1, iRover) + TSV(iRover).Value / (60)

Case MPPKSTArray(2, 0)
MPPKSTArray(2, iRover) =
MPPKSTArray(2, iRover) + TSV(iRover).Value / (60)

Case MPPKSTArray(3, 0)
MPPKSTArray(3, iRover) =
MPPKSTArray(3, iRover) + TSV(iRover).Value / (60)

End Select
End If
Next iRover

End If
Next r

Hope that helps in understanding my problem.

Reinhard

 

[Jan De Messemaeker]

Hi Reinhard,

Yes this brings up memories from way back.
Does it help if instead of tsv.value you systematically use val(tsv.value)?

Greetings,

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[Reinhard]

Hi Jan,

unfortunately this doesn't help at all.
I'm still running into the exception.

As I'm working on a business year (12 months) at the moment I'm using two
TSV structures and two calls for the complete story - and it works!

This is not finally what I'm going to accept as a solution because the next
step is to create S-line information (german: Meilenstein Trend Analyse) out
of the project file. Here I'll end up with as many weeks as you can think of
(perhaps even more than 50 weeks for a project running long enough).

What about this garbage collection issue you can find in the web discussions?
Is there anything worth to describe here?

Regards,

Reinhard

 

[Rod Gill]

I would code this:

Dim Res As Resource
Dim tsv As TimeScaleValue
Dim tsvs As TimeScaleValues
For Each Res In ActiveProject.Resources
Set tsvs = ActiveProject.ProjectSummaryTask.TimeScaleData( _
StartDate:="01.10.2008", EndDate:="30.09.2009",
Type:=pjTaskTimescaledBaselineWork)
For Each tsv In tsvs
If Val(tsv.Value) <> 0 Then
'Select case etc.
End If
Next tsv
Next Res

If you name one parameter you must name them all. ,Value property can have
several values, so Val forces a numerical value.
--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com

Hi Jan,

unfortunately this doesn't help at all.
I'm still running into the exception.

As I'm working on a business year (12 months) at the moment I'm using two
TSV structures and two calls for the complete story - and it works!

This is not finally what I'm going to accept as a solution because the
next
step is to create S-line information (german: Meilenstein Trend Analyse)
out
of the project file. Here I'll end up with as many weeks as you can think
of
(perhaps even more than 50 weeks for a project running long enough).

What about this garbage collection issue you can find in the web
discussions?
Is there anything worth to describe here?

Regards,

Reinhard


__________ Information from ESET Smart Security, version of virus
signature database 5037 (20100418) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5037 (20100418) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Lars Hammarberg]

But unfortunately requires the number to be converted with Val to be using a
period as decimal separator...

 

[Reinhard]

Hi Rod, Hi Lars,

the solution you suggest Ron does not help.
I can read in the debug window even that the value I try to access is invalid.
Forcing it to be a numerical value does not improve the situation. And,
Lars, it is not affected by the value format. The value is inaccessible, due
to the fact the errenous content in the value field is there directly after
the funtion call.

There has been no answer yet to my question around the garbage collection
issue. Is there anything I can do around here to try whether this helps?
I don't expect the error to be a consequence of poor code - not as I see
myself to be the godfather of VBA programming but I've tried various code
excerpts from the web, followed all your suggestions and the error always
shows up.

Reinhard

 

[Rod Gill]

The idea behind forcing a numerical result is you ignore all zero results,
avoiding the error. Is this happening on all your projects or just one or
two? If only on one or two, then maybe a file corruption?

All I can say is I've used Timescaledata for many years in many projects for
many customers with no problems, even reading 50+ weeks of data.


Is the time period that causes the problem within the project's start and
finish dates? If not, try only interrogating the periods that are within the
project's start and finish dates.

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com

Hi Rod, Hi Lars,

the solution you suggest Ron does not help.
I can read in the debug window even that the value I try to access is
invalid.
Forcing it to be a numerical value does not improve the situation. And,
Lars, it is not affected by the value format. The value is inaccessible,
due
to the fact the errenous content in the value field is there directly
after
the funtion call.

There has been no answer yet to my question around the garbage collection
issue. Is there anything I can do around here to try whether this helps?
I don't expect the error to be a consequence of poor code - not as I see
myself to be the godfather of VBA programming but I've tried various code
excerpts from the web, followed all your suggestions and the error always
shows up.

Reinhard

__________ Information from ESET Smart Security, version of virus
signature database 5041 (20100419) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5041 (20100419) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Rod Gill]

Garbage collection is only a VSTO .Net issue. VBA does its own memory
management in the background and other than forcing object variables to
Nothing there isn't anything you can do.

If you save this project to xml file, then open into a new .mpp file then
re-run the code, does it work? This process should remove most corruption.

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com

The idea behind forcing a numerical result is you ignore all zero results,
avoiding the error. Is this happening on all your projects or just one or
two? If only on one or two, then maybe a file corruption?

All I can say is I've used Timescaledata for many years in many projects
for many customers with no problems, even reading 50+ weeks of data.


Is the time period that causes the problem within the project's start and
finish dates? If not, try only interrogating the periods that are within
the project's start and finish dates.

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com






__________ Information from ESET Smart Security, version of virus
signature database 5041 (20100419) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5041 (20100419) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Lars Hammarberg]

Before retreiving the value for a given day - check if that day is a work
day by examining the boolean 'workweekday' for that day - if it is a working
day, you *should* not have any trouble.

--

 

[Reinhard]

Hi Rod, Hi Lars,

I'm not sure, but it appears I've found a clue to solving my problem.
After having tried all of what you described and slowly feeling frustrated I
started the macro without the debugger stopping the execution - and it worked!

I've seen influences on the execution of software in embedded systems
development when using debuggers, but not in VBA so far.

Since today in the morning I can now happily read out whatever time period
I want, even if passing the MPP project's time boundaries - if I don't stop
the execution of the code dealing with the TSVs by setting breakpoints.
Otherwise I can force my error situation as often as I want.

I want to thank you for your support - it kept me going on!

Reinhard