Comparing resource names in VBA

[Kevin Towers]

What I am trying to do is to get a filter that searches all tasks within a
date range and for a single resource.... all with VBA. I can do this easily
with autofilter or the new filter menu items, but have not figured out how to
create a common filter within VBA. So I've resorted to doing it manually in
VBA by scanning the tasks and setting flags. I am looping through the
project task trying to find those that match a particular resource. The
following code generates a runtime error (#1101. "Argument value is not
valid")

If CheckField(jTask.ResourceNames, R.Name, "contains") Then
jTask.Flag5 = True
End If

I have confirmed that the ResourceNames string is correct and the R.name
string is correct.

Does anyone have any idea why this error is happening?


Thanks

Kevin

 

[John]

What I am trying to do is to get a filter that searches all tasks within a
date range and for a single resource.... all with VBA. I can do this easily
with autofilter or the new filter menu items, but have not figured out how to
create a common filter within VBA. So I've resorted to doing it manually in
VBA by scanning the tasks and setting flags. I am looping through the
project task trying to find those that match a particular resource. The
following code generates a runtime error (#1101. "Argument value is not
valid")

If CheckField(jTask.ResourceNames, R.Name, "contains") Then
jTask.Flag5 = True
End If

I have confirmed that the ResourceNames string is correct and the R.name
string is correct.

Does anyone have any idea why this error is happening?


Thanks

Kevin

Kevin,
Well, it's telling you that either the "field" or "value" argument in
the CheckField Method is incorrect. Without seeing the rest of your
code, it is guesswork to try and figure out what the complier doesn't
like. It's kinda like taking your car to the repair shop and simply
saying, "it's broken, please fix it".

With regard to creating a filter in VBA, I always use the FilterEdit
Method. It is the VBA equivalent of manually creating a filter. For your
case I would either apply the Date Range filter or ask the user for the
date range parameters and then loop through all tasks (or the filtered
set) and look for the particular resource name. I would probably also
use a flag field to identify the found tasks and then apply a filter on
that flag, all in VBA. In pseudo-code,

1. Use InputBox to get start and finish date parameters
2. Set up a loop to go through all tasks. If the file is large, I might
filter on the date range and then only loop on the found set. For
example:
For Each t in ActiveProject.Tasks
or
For Each t in ActiveSelection.Tasks
3. Use simple "If" statements to check for Start, Finish and
ResourceNames.
4. Clear a flag field just to make sure there is no extraneous data.
5. If the resource name is found and is in the date range, set a flag
field
6. After looping through all tasks, use FilterEdit Method to check for
the flag

Hope this helps.
John
Project MVP

 

[Kevin Towers]

Kevin,
Well, it's telling you that either the "field" or "value" argument in
the CheckField Method is incorrect. Without seeing the rest of your
code, it is guesswork to try and figure out what the complier doesn't
like. It's kinda like taking your car to the repair shop and simply
saying, "it's broken, please fix it".

With regard to creating a filter in VBA, I always use the FilterEdit
Method. It is the VBA equivalent of manually creating a filter. For your
case I would either apply the Date Range filter or ask the user for the
date range parameters and then loop through all tasks (or the filtered
set) and look for the particular resource name. I would probably also
use a flag field to identify the found tasks and then apply a filter on
that flag, all in VBA. In pseudo-code,

I realize that the error is conserning the parameters. Both parameters come
direclty from the resource or task objects. When the macro breaks, I am able
to look at the values of each parameter. They appear to be OK.

1. Use InputBox to get start and finish date parameters
2. Set up a loop to go through all tasks. If the file is large, I might
filter on the date range and then only loop on the found set. For
example:
For Each t in ActiveProject.Tasks
or
For Each t in ActiveSelection.Tasks
3. Use simple "If" statements to check for Start, Finish and
ResourceNames.
4. Clear a flag field just to make sure there is no extraneous data.
5. If the resource name is found and is in the date range, set a flag
field
6. After looping through all tasks, use FilterEdit Method to check for
the flag

This is exactly what I am doing. Here is the main section of my code:

For Each R In ActiveProject.Resources
If R.Assignments.Count > 0 Then

'clear the flags field
For Each jTask In ActiveProject.Tasks
If Not jTask Is Nothing Then
jTask.Flag5 = False
jTask.Flag6 = False
End If
Next jTask

'set the flag field if it matches the current resource
For Each jTask In ActiveProject.Tasks
If Not jTask Is Nothing Then
If jTask.ResourceNames <> "" Then
If CheckField(jTask.ResourceNames, R.Name, "contains") Then
jTask.Flag5 = True
End If
End If
End If
Next jTask

'now set Flag6 only if it is within our date range
For Each jTask In ActiveProject.Tasks
If Not jTask Is Nothing Then
If jTask.Flag5 = True And jTask.Start >= from_date And
jTask.Finish <= to_date Then
jTask.Flag6 = True
End If
End If
Next jTask


'filter to show just the selected tasks
FilterEdit Name:="select", TaskFilter:=True, Create:=True,
OverwriteExisting:=True, _
FieldName:="Flag6", Test:="equals", Value:=True, ShowInMenu:=False,
ShowSummaryTasks:=True
FilterApply Name:="select"


Is there another way of comparing strings in VBA (other than CheckField)?
Anything similiar to strstr() in C?

Thanks

Kevin

 

[Jan De Messemaeker]

How about Instr?

 

[Brian K - Project MVP]

I realize that the error is conserning the parameters. Both parameters
come
direclty from the resource or task objects. When the macro breaks, I am
able
to look at the values of each parameter. They appear to be OK.


This is exactly what I am doing. Here is the main section of my code:

For Each R In ActiveProject.Resources
If R.Assignments.Count > 0 Then

'clear the flags field
For Each jTask In ActiveProject.Tasks
If Not jTask Is Nothing Then
jTask.Flag5 = False
jTask.Flag6 = False
End If
Next jTask

'set the flag field if it matches the current resource
For Each jTask In ActiveProject.Tasks
If Not jTask Is Nothing Then
If jTask.ResourceNames <> "" Then
If CheckField(jTask.ResourceNames, R.Name, "contains") Then
jTask.Flag5 = True
End If
End If
End If
Next jTask

'now set Flag6 only if it is within our date range
For Each jTask In ActiveProject.Tasks
If Not jTask Is Nothing Then
If jTask.Flag5 = True And jTask.Start >= from_date And
jTask.Finish <= to_date Then
jTask.Flag6 = True
End If
End If
Next jTask


'filter to show just the selected tasks
FilterEdit Name:="select", TaskFilter:=True, Create:=True,
OverwriteExisting:=True, _
FieldName:="Flag6", Test:="equals", Value:=True, ShowInMenu:=False,
ShowSummaryTasks:=True
FilterApply Name:="select"


Is there another way of comparing strings in VBA (other than CheckField)?
Anything similiar to strstr() in C?

Thanks

Kevin

The way I would do this might not be as efficient as just checking the
Resource Names field but I know it works. Do a loop through all the
Assignment Objects for each task and check the Resource Name property for
that assignment object.

Sub asdf()
Dim T As Task
Dim A As Assignment

For Each T In ActiveProject.Tasks
For Each A In T.Assignments
If A.ResourceName = "foo" Then
T.Flag1 = True
Else
T.Flag1 = False
End If
Next A
Next T
End Sub

 

[John]

I realize that the error is conserning the parameters. Both parameters come
direclty from the resource or task objects. When the macro breaks, I am able
to look at the values of each parameter. They appear to be OK.


This is exactly what I am doing. Here is the main section of my code:

For Each R In ActiveProject.Resources
If R.Assignments.Count > 0 Then

'clear the flags field
For Each jTask In ActiveProject.Tasks
If Not jTask Is Nothing Then
jTask.Flag5 = False
jTask.Flag6 = False
End If
Next jTask

'set the flag field if it matches the current resource
For Each jTask In ActiveProject.Tasks
If Not jTask Is Nothing Then
If jTask.ResourceNames <> "" Then
If CheckField(jTask.ResourceNames, R.Name, "contains") Then
jTask.Flag5 = True
End If
End If
End If
Next jTask

'now set Flag6 only if it is within our date range
For Each jTask In ActiveProject.Tasks
If Not jTask Is Nothing Then
If jTask.Flag5 = True And jTask.Start >= from_date And
jTask.Finish <= to_date Then
jTask.Flag6 = True
End If
End If
Next jTask


'filter to show just the selected tasks
FilterEdit Name:="select", TaskFilter:=True, Create:=True,
OverwriteExisting:=True, _
FieldName:="Flag6", Test:="equals", Value:=True, ShowInMenu:=False,
ShowSummaryTasks:=True
FilterApply Name:="select"


Is there another way of comparing strings in VBA (other than CheckField)?
Anything similiar to strstr() in C?

Thanks

Kevin

Kevin,
First, let me answer your last question. Visual Basic has several
methods that can be used to compare strings. The best method to use
depends on what kind of strings (i.e. whole string, middle characters,
single character, etc.) are being compared. I use the Instr Function
quite often but I also use a simple If statement (e.g. If t.Text1 =
"bob" Then).

I haven't studied your code thoroughly but on the surface it seems a
little backward. The resource loop and task loops are not helping each
other. The outer loop will go through all resources, not just those that
are assigned to each task. In effect, if the runtime error didn't occur,
Flag5 will be set true for all tasks (or at least those tasks with
resource assignments). I don't think this was your intent. I agree with
Brian's response. Loop through the tasks and look at the assignment
property when comparing.

Also, I thought you wanted to look for a particular resource who was
assigned to all tasks in a given date range. If so, some type of input
is needed from the user to specify which resource. For this I would use
an InputBox Function.

Finally, without actually testing your code, I suspect the runtime error
occurs because normally there are no assignments on Summary Lines. When
the inner loop goes through the tasks the first one is likely to be a
Summary line and hence there is no resource name associated with the
task.

Hope this helps.
John
Project MVP

 

[Ron]

John wrote

Finally, without actually testing your code, I suspect the runtime error
occurs because normally there are no assignments on Summary Lines. When
the inner loop goes through the tasks the first one is likely to be a
Summary line and hence there is no resource name associated with the
task.

Hope this helps.
John
Project MVP

Did anyone manage to overcome this error with CheckField. I am having the
same problem. The string values look like strings and have the same value in
the watch window.

I am really interested.

Regards

Ron

 

[John]

Did anyone manage to overcome this error with CheckField. I am having the
same problem. The string values look like strings and have the same value in
the watch window.

I am really interested.

Regards

Ron

Ron,
I doubt there is a problem with the CheckField Method (although it is
possible). More likely, VBA is flagging a real error and it is "just a
matter of figuring out why VBA is choking" (sometimes easier said than
done, I realize).

What exactly are you trying to do? It may well be there is an easier way
to get what you want that doesn't involve using the CheckField Method.

John
Project MVP

 

[ronmcfarlane]

I am trying to do a report for each team member (1 resource per page )
listing the tasks assigned to them over the next 2 weeks. At the end of
the week they will enter on the form the actual days spent, actual work
done, actual finish and estimated work to complete. I will then use
this to update the schedule.

The who's doing what when report is similar but not quiet what I want.

Psuedo code something like

For each resource in the resource sheet

Filter on (tasks that have started or are due to in the next 2
weeks) or (tasks that should have completed or are due to in the next 2
weeks)
If Task count > 0
Print the report
EndIf
Next Resource


I was using the Checkfield method to update a flag in a similar way to
the earlier posting

Thanks for you assistance.

 

[John]

I am trying to do a report for each team member (1 resource per page )
listing the tasks assigned to them over the next 2 weeks. At the end of
the week they will enter on the form the actual days spent, actual work
done, actual finish and estimated work to complete. I will then use
this to update the schedule.

The who's doing what when report is similar but not quiet what I want.

Psuedo code something like

For each resource in the resource sheet

Filter on (tasks that have started or are due to in the next 2
weeks) or (tasks that should have completed or are due to in the next 2
weeks)
If Task count > 0
Print the report
EndIf
Next Resource


I was using the Checkfield method to update a flag in a similar way to
the earlier posting

Thanks for you assistance.

Ron,
Yeah that "who's doing what when report" is very noisy - sorry, it's
Monday.

I see you posted a second query so I'll answer on that one.

John
Project MVP