Strange Issue with TimeScaleValues

M

Michael A.

Hello All,

To start with, the following code works in VBA without issue:

Set Tsvs = resAsgnmt.TimeScaleData(prStart, prEnd,
pjAssignmentTimescaledWork, pjTimescaleMonths)
'Obtain the timescale data for each resource
j = 2
For Each Tsv In Tsvs
allResources(i, j) = allResources(i, j) + Val(Tsv.Value)
resourcesTotalWork(i, j) = resourcesTotalWork(i, j) + Val(Tsv.Value)
j = j + 1
Next Tsv

I convert this into Visual Studio as - it is just about identical:

tsvs = resAsgnmt.TimeScaleData(prStart, prEnd,
MSProject.PjAssignmentTimescaledData.pjAssignmentTimescaledWork,
MSProject.PjTimescaleUnit.pjTimescaleMonths)
j = 2
Dim tsv As MSProject.TimeScaleValue
For Each tsv In tsvs
resourceWorkAlloc(i, j) = resourceWorkAlloc(i, j) + Var(tsv.Value)
resourcesTotalWork(i, j) = resourcesTotalWork(i, j) + Var(tsv.Value)
Next tsv

The problem:
When the code cycles through the 'tsv' values, it works for some instances
but then all of a sudden throws an unhandled unexpected error as follows:

System.Runtime.InteropServices.COMException was unhandled by user code
ErrorCode=-2146827284
HelpLink="D:\Program Files\Microsoft Office\Office12\VBAPJ.CHM#131072"
Message="An unexpected error occurred with the method."
Source=""

I see here that for some reason it doesn't like the empty string and yet it
the code has successfully processed empty strings earlier.

Would anyone have any thoughts as to why this would occur and what I may do
to resolve?

I appreciate your time and consideration.


Kind regards
Michael A.
 
R

Rod Gill

tsv.value returns "" for empty cells you see in a Usage View. Try using
Val(tsv.value) which converts the "" to zero.

--

Rod Gill
Microsoft MVP for Project

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




Michael A. said:
Hello All,

To start with, the following code works in VBA without issue:

Set Tsvs = resAsgnmt.TimeScaleData(prStart, prEnd,
pjAssignmentTimescaledWork, pjTimescaleMonths)
'Obtain the timescale data for each resource
j = 2
For Each Tsv In Tsvs
allResources(i, j) = allResources(i, j) + Val(Tsv.Value)
resourcesTotalWork(i, j) = resourcesTotalWork(i, j) + Val(Tsv.Value)
j = j + 1
Next Tsv

I convert this into Visual Studio as - it is just about identical:

tsvs = resAsgnmt.TimeScaleData(prStart, prEnd,
MSProject.PjAssignmentTimescaledData.pjAssignmentTimescaledWork,
MSProject.PjTimescaleUnit.pjTimescaleMonths)
j = 2
Dim tsv As MSProject.TimeScaleValue
For Each tsv In tsvs
resourceWorkAlloc(i, j) = resourceWorkAlloc(i, j) + Var(tsv.Value)
resourcesTotalWork(i, j) = resourcesTotalWork(i, j) + Var(tsv.Value)
Next tsv

The problem:
When the code cycles through the 'tsv' values, it works for some instances
but then all of a sudden throws an unhandled unexpected error as follows:

System.Runtime.InteropServices.COMException was unhandled by user code
ErrorCode=-2146827284
HelpLink="D:\Program Files\Microsoft Office\Office12\VBAPJ.CHM#131072"
Message="An unexpected error occurred with the method."
Source=""

I see here that for some reason it doesn't like the empty string and yet
it
the code has successfully processed empty strings earlier.

Would anyone have any thoughts as to why this would occur and what I may
do
to resolve?

I appreciate your time and consideration.


Kind regards
Michael A.

__________ Information from ESET Smart Security, version of virus
signature database 4663 (20091205) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4663 (20091205) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
M

Michael A.

Hello Rod,

You are absolutely correct? It doesn't matter if I use Val(tsv.Value) to
return a 0 or use tsv.Value to return ""; for instance, the following ought
to be valid:

if tsv.Value = "" then.....

It seems that the first few times the code trys to reference tsv.Value or
Val(tsv.Value), there aren't any problems, and then all of a sudden I start
getting the COMexception and therefore cannot get any timescaledata at all.

This is my dilemma. Any thoughts?


Thanks
Michael Andersen
 
R

Rod Gill

So if you wrap your sum code with
If tsv.value<>"" then
resourceWorkAlloc(i, j) = resourceWorkAlloc(i, j) + Var(tsv.Value)
resourcesTotalWork(i, j) = resourcesTotalWork(i, j) + Var(tsv.Value)
End if

I assume as well that the j dimension of the array declaration is big
enough?
--

Rod Gill
Microsoft MVP for Project

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




Michael A. said:
Hello Rod,

You are absolutely correct? It doesn't matter if I use Val(tsv.Value) to
return a 0 or use tsv.Value to return ""; for instance, the following
ought
to be valid:

if tsv.Value = "" then.....

It seems that the first few times the code trys to reference tsv.Value or
Val(tsv.Value), there aren't any problems, and then all of a sudden I
start
getting the COMexception and therefore cannot get any timescaledata at
all.

This is my dilemma. Any thoughts?


Thanks
Michael Andersen





__________ Information from ESET Smart Security, version of virus
signature database 4668 (20091207) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4668 (20091207) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
M

Michael A.

Hello Rod,

Following on from my previous note, I wrote the following in Visual Studio:

Dim tsk As Task
Dim resAsgnmt As Assignment
Dim ptsvs As TimeScaleValues
Dim ptsv As TimeScaleValue

For Each tsk In Application.ActiveProject.Tasks
For Each resAsgnmt In tsk.Assignments
ptsvs =
resAsgnmt.TimeScaleData(Application.ActiveProject.ProjectSummaryTask.Start, _

Application.ActiveProject.ProjectSummaryTask.Finish, _

PjAssignmentTimescaledData.pjAssignmentTimescaledWork, _

PjTimescaleUnit.pjTimescaleMonths)
For Each ptsv In ptsvs
System.Diagnostics.Debug.Print(ptsv.Value)
Next
Next
Next


Very simple. There are no build errors. I am using MS Project Professional
2007. The .mpp project consists of seven months from project start to
finish. Hence, the timescaledata iteration above is based on a count of 7.

When executing the first (and only) resource assignment of the first task
has all its timescaledata written to the immediate window. On the next task,
the timescaledata iteration only occurs 3 times before a COMexception error
is produced, which on further investigation says "an unexpected error
occurred with the method".

Using this simple code in Visual Studio, it is not possible to extract the
timephased data.

Any thoughts?

Regards
Michael A.
 
R

Rod Gill

The project may last 7 months, but it may span 8 months. try a count of 8.

What's different about task 2?

You need a If not tsk is nothing test in case of a blank task

--

Rod Gill
Microsoft MVP for Project

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




Michael A. said:
Hello Rod,

Following on from my previous note, I wrote the following in Visual
Studio:

Dim tsk As Task
Dim resAsgnmt As Assignment
Dim ptsvs As TimeScaleValues
Dim ptsv As TimeScaleValue

For Each tsk In Application.ActiveProject.Tasks
For Each resAsgnmt In tsk.Assignments
ptsvs =
resAsgnmt.TimeScaleData(Application.ActiveProject.ProjectSummaryTask.Start,
_

Application.ActiveProject.ProjectSummaryTask.Finish, _

PjAssignmentTimescaledData.pjAssignmentTimescaledWork, _

PjTimescaleUnit.pjTimescaleMonths)
For Each ptsv In ptsvs
System.Diagnostics.Debug.Print(ptsv.Value)
Next
Next
Next


Very simple. There are no build errors. I am using MS Project
Professional
2007. The .mpp project consists of seven months from project start to
finish. Hence, the timescaledata iteration above is based on a count of
7.

When executing the first (and only) resource assignment of the first task
has all its timescaledata written to the immediate window. On the next
task,
the timescaledata iteration only occurs 3 times before a COMexception
error
is produced, which on further investigation says "an unexpected error
occurred with the method".

Using this simple code in Visual Studio, it is not possible to extract the
timephased data.

Any thoughts?

Regards
Michael A.



__________ Information from ESET Smart Security, version of virus
signature database 4668 (20091207) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4668 (20091207) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 

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

Filtering on import to excel 1
Using TimeScaledData with C# and Project 2003 0

Top