Problem running Project Macro VBA   

Hi,

I'm trying to make a macro that automatically assigns resources to tasks, 
depending on the values of two custom text fields.

Imagine, that in the Gantt Chart view I've two custom fields, "text1" and 
"text4", and the same thing in resource view, so if I put in the gantt view 
text1="E1", and text4="F1" (or anything else), the macro should assign to 
each task the resources that have text1="E1" and text4="F1" (or anything 
else, depending on the text), based in the resource list field.

But everytime I tryed to ran the macro, the Microsoft Project crashes itself 
for a long time and stop running.

The code i'm usign is this one:

--- code ---

Sub AutoAssign()
Dim tskT As Task
Dim rcsR As Resource
Dim asnA As Assignment
Dim lngUnits As Long
Dim blnFound As Boolean
 
lngUnits = 1
 
For Each tskT In ActiveProject.Tasks
    If Not (tskT Is Nothing) Then
        For Each rcsR In ActiveProject.Resources
            If Not (rcsR Is Nothing) Then
                If (rcsR.Text1 = tskT.Text1) And (rcsR.Text4 = tskT.Text4) 
Then
                    For Each asnA In tskT.Assignments
                        If asnA.ResourceID = rcsR.ID Then
                            blnFound = True
                        End If
                    Next asnA
                             
                    If blnFound = False Then
                        tskT.Assignments.Add TaskID:=tskT.ID, 
ResourceID:=rcsR.ID, Units:=lngUnits
                    Else
                        blnFound = False
                   End If
                End If
            End If
        Next rcsR
    End If
Next tskT
End Sub

--- End Code ---

I don't know what is happening but it crashes everytime, in MS Project 2003 
and even in MS Project 2007.

I hope someone can help me figuring it out, cause I don't know what to do 
more and how can I solve this.

Thanks in advance,

VaReTaS

date: Wed, 20 Aug 2008 03:58:05 -0700   author:   VaReTaS

Re: Problem running Project Macro VBA   

Hi,

I ran the code without any problem.
Still there are a few questions I have.
Are you aware that when Text1 and Text4 are empty, both for the task and for 
a resource, the resource is assigned?
This being said, do you have any external tasks in your project? You can't 
assign resoruces to external tasks.

Hope this helpe,

-- 
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
"VaReTaS"  wrote in message 
news:42BF6978-6A00-4175-8D0C-E44588F30E5C@microsoft.com...
> Hi,
>
> I'm trying to make a macro that automatically assigns resources to tasks,
> depending on the values of two custom text fields.
>
> Imagine, that in the Gantt Chart view I've two custom fields, "text1" and
> "text4", and the same thing in resource view, so if I put in the gantt 
> view
> text1="E1", and text4="F1" (or anything else), the macro should assign to
> each task the resources that have text1="E1" and text4="F1" (or anything
> else, depending on the text), based in the resource list field.
>
> But everytime I tryed to ran the macro, the Microsoft Project crashes 
> itself
> for a long time and stop running.
>
> The code i'm usign is this one:
>
> --- code ---
>
> Sub AutoAssign()
> Dim tskT As Task
> Dim rcsR As Resource
> Dim asnA As Assignment
> Dim lngUnits As Long
> Dim blnFound As Boolean
>
> lngUnits = 1
>
> For Each tskT In ActiveProject.Tasks
>    If Not (tskT Is Nothing) Then
>        For Each rcsR In ActiveProject.Resources
>            If Not (rcsR Is Nothing) Then
>                If (rcsR.Text1 = tskT.Text1) And (rcsR.Text4 = tskT.Text4)
> Then
>                    For Each asnA In tskT.Assignments
>                        If asnA.ResourceID = rcsR.ID Then
>                            blnFound = True
>                        End If
>                    Next asnA
>
>                    If blnFound = False Then
>                        tskT.Assignments.Add TaskID:=tskT.ID,
> ResourceID:=rcsR.ID, Units:=lngUnits
>                    Else
>                        blnFound = False
>                   End If
>                End If
>            End If
>        Next rcsR
>    End If
> Next tskT
> End Sub
>
> --- End Code ---
>
> I don't know what is happening but it crashes everytime, in MS Project 
> 2003
> and even in MS Project 2007.
>
> I hope someone can help me figuring it out, cause I don't know what to do
> more and how can I solve this.
>
> Thanks in advance,
>
> VaReTaS

date: Wed, 20 Aug 2008 14:21:28 +0200   author:   Jan De Messemaeker

Re: Problem running Project Macro VBA   

Hi,

I haven't though that way, maybe because I've those fields empty, in most 
tasks I tried, it takes much longer to assign all resources to those tasks 
that doesn't have anything it the custom text field.

I'll try to set it all well and then run again the macro.

Thanks a lot.

VaReTaS

"Jan De Messemaeker" wrote:

> Hi,
> 
> I ran the code without any problem.
> Still there are a few questions I have.
> Are you aware that when Text1 and Text4 are empty, both for the task and for 
> a resource, the resource is assigned?
> This being said, do you have any external tasks in your project? You can't 
> assign resoruces to external tasks.
> 
> Hope this helpe,
>

date: Wed, 20 Aug 2008 06:07:00 -0700   author:   VaReTaS

Re: Problem running Project Macro VBA   

Hi,

Finally I found the problem and, has you said, it was in the empty text 
fields, so MS Project takes a long run to assign all my hundreds of resources 
to my hundreds of tasks. :p

Now it works all fine.

By the way, do I have a way to have the macro always running despite of have 
to run it everytime I need? Meaning that everytime I put some text in text1 
and text4 it assigns resources to tasks?!

I almost forgot to ask something else... once I run the macro it assigns the 
resources to tasks depending in the text I've in the text1 and text4 fields, 
but if I change the text in the custom fields it doesn't update the 
assignments to the tasks, leaving the assigns entered before. Do I've a way 
to correct this?!?

And once again thanks a lot for the help...

VaReTaS

"Jan De Messemaeker" wrote:

> Hi,
> 
> I ran the code without any problem.
> Still there are a few questions I have.
> Are you aware that when Text1 and Text4 are empty, both for the task and for 
> a resource, the resource is assigned?
> This being said, do you have any external tasks in your project? You can't 
> assign resoruces to external tasks.
> 
> Hope this helpe,
>

date: Wed, 20 Aug 2008 06:54:05 -0700   author:   VaReTaS

Re: Problem running Project Macro VBA   

Hi,

Two questions.
First, you could code the macro in an event (Projectbeforetaskchange) but on 
top of he fact that working with events isn't that obvious, here your code 
will have to be very delicate since (a) you have to check two fields - the 
event will fire already on the first change!) and the action taken (assign a 
resource) will again fire the event... possible but not easy to control.
An alternative is to run the macro in a Project event (Before Save, before 
close or Open) where you are sure it won't get into a loop.

About deleting other assignments: can be doine but you must be absolutely 
sure about the logic. If Text1 and/or Text4 have changed, do you want to 
delete all assignments before assigning the new ones? And if no, which ones 
would you delete? How to identify them?

Hope this helps,

-- 
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
"VaReTaS"  wrote in message 
news:202645A2-E4F1-44FA-9EC7-E24745D0E464@microsoft.com...
> Hi,
>
> Finally I found the problem and, has you said, it was in the empty text
> fields, so MS Project takes a long run to assign all my hundreds of 
> resources
> to my hundreds of tasks. :p
>
> Now it works all fine.
>
> By the way, do I have a way to have the macro always running despite of 
> have
> to run it everytime I need? Meaning that everytime I put some text in 
> text1
> and text4 it assigns resources to tasks?!
>
> I almost forgot to ask something else... once I run the macro it assigns 
> the
> resources to tasks depending in the text I've in the text1 and text4 
> fields,
> but if I change the text in the custom fields it doesn't update the
> assignments to the tasks, leaving the assigns entered before. Do I've a 
> way
> to correct this?!?
>
> And once again thanks a lot for the help...
>
> VaReTaS
>
> "Jan De Messemaeker" wrote:
>
>> Hi,
>>
>> I ran the code without any problem.
>> Still there are a few questions I have.
>> Are you aware that when Text1 and Text4 are empty, both for the task and 
>> for
>> a resource, the resource is assigned?
>> This being said, do you have any external tasks in your project? You 
>> can't
>> assign resoruces to external tasks.
>>
>> Hope this helpe,
>>
>

date: Wed, 20 Aug 2008 18:48:14 +0200   author:   Jan De Messemaeker