resource assignment

All replies

• 

  

  0

  Sign in to vote

  peirrot75,

  First of all, where do you set the value of "i"? There must be more to your procedure than you are showing.

  The reason you are getting the run-time error is because you check each assignment of the task and if that assignment is not DG3, then the DG3 resource is added. At that point the loop should exit, but it keeps checking each assignment and when it sees that the next assignment is not DG3, it will attempt to assign it and that generates the error.

  I would loop through all assignments for the task. If the DG3 resource is assigned, exit the loop. If it is not found, then add the DG3 resource after the loop.

  Hope this helps.

  If this answers your question, please mark it as th answer.

  John

  Thursday, July 31, 2014 4:45 PM
• 

  

  0

  Sign in to vote

  Thanks John for your answer.

  The value i is set in an other part of the code as following:

  ----------------------------------------------------------

  Public Sub MàJ_Indemnites()
  
  nb_taches = ActiveProject.Tasks.Count
  
  For i = 1 To nb_taches
  Set Task_i = ActiveProject.Tasks(i)
  
  Call ImportDonnéesPourMàJ
  'MsgBox (SOUD)
  
      If Task_i.Summary = False Then
          If TskCout > 0 Then
  
              Call DecompCalendrier
              Call DetermNbIndem
              Call Ajustement_Affectation
          End If
      End If
      
  Next i
  
  End Sub

  --------------------------------------------------------------

  To avoid to have a too long code, I prefer to do like that. The code which doesn't work is "Ajustement_Affectation".

  Before you send me the answer, I find solution which works fine but it's not very "clean" as following...

  After a test on the "DG3" assignment existing, I add this assignment or I only adjust the "DG3" assignment units...

  -------------------------------------------------------------------------------------------------------------

  If Not NomRessAffectee = "DG3" Then
          Set RessourceAffectee = ActiveProject.Resources("DG3")
          On Error Resume Next
          Set Affectation = Task_i.Assignments.Add(Task_i.ID, ResourceID:=RessourceAffectee.ID, Units:=NbIndemJrNORMALTotal)
  Else
          If NomRessAffectee = "DG3" Then
              Set RessourceAffectee = ActiveProject.Resources("DG3")
              Affectation.Units = NbIndemJrNORMALTotal
          End If
  End If

  --------------------------------------------------------------------------------------------------------------

  May be you have a better solution! Sorry, i have practiced VBA for few months and it's quite difficult to create code which works fine!

  Thursday, July 31, 2014 5:08 PM
• 

  

  0

  Sign in to vote

  pierrot75,

  Breaking your code into separate procedures can make it easier to test and troubleshoot but I sometimes find it more difficult to follow the flow when everything is broken into separate little procedures. If I'm going to be using a procedure multiple times during execution, then I will certainly make it a separate procedure within the module.

  I've structured the macro to give you an idea of how it can run more efficiently with a couple of options for testing for the DG3 assignment. The following code is filled with comments and it will effectively add the DG3 assignment to every non-summary task. I'm not sure that is what you want, but at least the code should give you a good starting point.

  If this answers your question please mark it as the answer and/or with a vote. Marking it will not close it or prevent further discussion, but it will simply indicate the response was at least helpful or in fact provided a good answer.

  By the way, if you do not already have Rod Gill's book on Project VBA, I highly recommend it. You can get more information at: http://www.project-systems.co.nz/project-vba-book.

  'although it isn't absolutely necessary, it is good practice
  '   to delcare all vaiables. Variables declared as public will be available to all
  '   procedures in the current module
  Option Explicit 'this forces declaration for all variables, it is good practice
  Public t As Task    'I like to keep variable names as simple as possible but that's just my preference
  Public a As Assignment
  Public Resfound As Boolean
  Public ResAff As Long
  Sub MàJ_Indemnites()
  For Each t In ActiveProject.Tasks
      If Not t Is Nothing Then    'this avoids a runtime error is the file has blank task lines
          'don't need the Call statement, just name the subroutine you
          '   wish to jump to. Note: I commented out some code for testing
          'ImportDonnéesPourMàJ
          'MsgBox (SOUD)
          If t.Summary = False Then
              'DecompCalendrier
              'DetermNbIndem
              Ajustement_Affectation
          End If
      End If
      
  Next t
  
  End Sub
  Sub Ajustement_Affectation()
  'this routine can use one of two methods to check for the DG3 assignment
  '   1. loop through all assignments looking for DG3. If found, exit the loop
  '       if not found, add DG3 as a resource
  '   2. examine the whole ResourceNames field string looking for "DG3". This is
  '       quicker but it is not foolproof since a work resource name may by chance
  '       include "dg3" (e.g. Bobdg3, or perrdg3iot, etc.)
  'use one approach or the other but not both
  
  'for the first approach use the following code:
  Resfound = False
  For Each a In t.Assignments
      If a.ResourceName = "DG3" Then
          Resfound = True
          Exit For
      End If
  Next a
  If Resfound = False Then
      ResAff = ActiveProject.Resources("DG3").ID
      t.Assignments.Add TaskID:=t.ID, ResourceID:=ResAff, Units:=1
  End If
  
  'for the second approach use the following code:
  If InStr(1, t.ResourceNames, "DG3") = 0 Then
      ResAff = ActiveProject.Resources("DG3").ID
      t.Assignments.Add TaskID:=t.ID, ResourceID:=ResAff, Units:=1
  End If
  End Sub
  

  John

  Thursday, July 31, 2014 11:05 PM
• 

  

  0

  Sign in to vote

  pierrot,

  Okay, so do you still have a question, or did I answer it?

  John

  Friday, August 1, 2014 2:33 PM
• 

  

  1

  Sign in to vote

  pierrot75,

  Okay, so would you mind marking this post as answered.

  John

  • Marked as answer by pierrot75 Friday, August 1, 2014 6:41 PM
  • Unmarked as answer by pierrot75 Friday, August 1, 2014 6:41 PM
  • Marked as answer by pierrot75 Friday, August 1, 2014 6:41 PM

  Friday, August 1, 2014 6:35 PM