none
Copying task.priority to resource.assignment.number1 RRS feed

  • Question

  • Dear experts,

    I'm stuck in the following requirement: I'd like to copy the task.priority value to the correct resource.assignment.number1 field.

    My code snippet has three nested for loops and is shown below. My issue is with the third loop, this loops through all of the resource assignments. Is there any more direct (efficient) way to accomplish my requirement?

    Sub()

                For Each t In ActiveProject.Tasks
                    intTaskID = t.UniqueID
                    intPrio = t.Priority
                    For Each r In ActiveProject.Tasks.UniqueID(intTaskID).Resources
                        For Each a In r.Assignments
                            If a.TaskID = intTaskID Then a.Number1 = intPrio
                        Next a
                    Next r
                Next t

    End Sub

    Thanks!

    Andre


    • Edited by Huismu5 Monday, April 23, 2018 4:36 PM Formatting
    Monday, April 23, 2018 4:30 PM

Answers

  • Andre,

    Yes, I simply expand the third loop as shown

    Sub()
    For Each t In ActiveProject.Tasks
        intTaskID = t.UniqueID
        intPrio = t.Priority
            For Each r In ActiveProject.Tasks.UniqueID(intTaskID).Resources
                For Each a In r.Assignments
                    If a.TaskID = intTaskID Then
                        a.Number1 = intPrio
                        Exit For
                    End If
                Next a
            Next r
    Next t

    End Sub

    Hope this helps.

    John

    • Marked as answer by Huismu5 Tuesday, April 24, 2018 1:22 PM
    Monday, April 23, 2018 5:20 PM

All replies

  • Andre,

    Yes, I simply expand the third loop as shown

    Sub()
    For Each t In ActiveProject.Tasks
        intTaskID = t.UniqueID
        intPrio = t.Priority
            For Each r In ActiveProject.Tasks.UniqueID(intTaskID).Resources
                For Each a In r.Assignments
                    If a.TaskID = intTaskID Then
                        a.Number1 = intPrio
                        Exit For
                    End If
                Next a
            Next r
    Next t

    End Sub

    Hope this helps.

    John

    • Marked as answer by Huismu5 Tuesday, April 24, 2018 1:22 PM
    Monday, April 23, 2018 5:20 PM
  • Thanks John, this is so obvious, can't believe I missed it! This saves 1000s of loops per execution.

    Tuesday, April 24, 2018 1:29 PM
  • Andre,

    You're welcome and thanks for the feedback. I use this method in many of my macro that have potentially large loops where I'm only looking for one result.

    John

    Tuesday, April 24, 2018 2:59 PM
  • John, for flexibility, i've converted my code into a sub and a function to copy any task property to any assignment property. Can you (or anyone else that would like to chime in) advise me on "best practices"?

    E.g.: I'm naming the variables in the sub the same as the name as the variables in the function, which may not be advisable. Even more basic, should I use a sub or a function? Maybe I want to return a value if the operation fails.

    My code is as follows:

    Sub test()
    Dim tProp As String
    Dim aProp As String
    Dim test As String
    Dim t As Task
    Dim a As Assignment
    Dim intTaskID As Integer

    tProp = "Priority"
    aProp = "Number1"

    For Each t In ActiveProject.Tasks
        intTaskID = t.UniqueID
            For Each r In ActiveProject.Tasks.UniqueID(intTaskID).Resources
                For Each a In r.Assignments
                    If a.TaskUniqueID = intTaskID Then
                        tProp2aProp t:=t, tProp:=tProp, a:=a, aProp:=aProp
                        Exit For
                    End If
                Next a
            Next r
    Next t

    End Sub

    Function tProp2aProp(ByRef t As Task, tProp As String, a As Assignment, aProp As String)

        tPropValue = CallByName(t, tProp, VbGet)
        CallByName a, aProp, VbLet, tPropValue

    End Function


    • Edited by Huismu5 Tuesday, April 24, 2018 4:49 PM Spelling
    Tuesday, April 24, 2018 4:49 PM
  • Andre,

    I rarely use functions, rather I create subroutines for operations that are likely to be repeated. As far as using the same variable names, the fewer names you use, the easier it is to keep track of everything. If I have a separate subroutine, I put shared variables in a Public statement. Public variables are available to all subs in a given procedure. For example, this is a snippet of code from one of my macros that has several subroutines.

    Option Explicit
    Option Compare Text
    Public Const Hdr = "Dynamic to Static Converter"
    Public Const ver = " - 2.14"

    Public SubNam(300) As String   'allow for up to 300 inserted subprojects total in 5 levels

    Public SubPrjCnt As Integer, i As Integer, TskCount As Integer, p1 As Integer, p2 As Integer
    Public p3 As Integer, p4 As Integer, PointEnd As Integer, unlinkCount As Integer, LinksMade As Integer
    Public ValidSub As Integer, NumSub As Integer, StRow As Integer
    Public ans As Single, Constr As Single

    Hope this helps.

    John

    Tuesday, April 24, 2018 7:04 PM
  • Andre,

    I'd advise you to look for Rod Gill's book on VBA Programming for Microsoft Project.  

    Best practice would call for "Option Explicit" at the top of the module; that enforces explicit declaration of your variables (some of which remain undeclared in your code.)  Any public variable declarations as suggested by John also go up there.

    Variable names are for human eyes mostly, and I agree with John that fewer/simpler is better.  Public variables don't need to be passed as arguments, and this can really streamline your code.

    It's strange (to me) that vba allows your "Function" code.  It doesn't return anything and is technically a sub not a function; pretty sure it wouldn't be allowed in vb.net.

    Also, unless you are using a resource pool, I don't understand the need for the nested task/resource/task loops and some of the intermediate variables in your routine.  Seems simpler just to loop through the resource assignments and get the related task priority. i.e. except for a couple declarations, the code below does everything your sub and function do.

    For Each R In ActiveProject.Resources
        For Each a In R.Assignments
            CallByName a, aProp, VbLet, CallByName(a.Task, tProp, VbGet)
        Next a
    Next R
    

    CallByName is a new method for me.  Seems pretty cool; I'll find a place to use it.

    Your instincts were correct about having to "return a value if the operation fails."  Error handling is extremely important if you are making a general-purpose, flexible tool.  Rod Gill has some good pointers on this in his book.  In vba you're pretty much limited to using On Error Resume Next and looking for Err.Num<>0 to trap errors.

    Good luck, tom

    Tuesday, April 24, 2018 8:19 PM
  • Thank you Tom and John,

    I read varying opinions on the use of public variables, it seems to be one of those things that some try to avoid at all cost, while others use it all the time.

    We do use a resource pool, so the task loop ensures only the project's tasks are processed even if the pool is open in the background.

    Rg,

    Andre 

    Wednesday, April 25, 2018 1:09 PM
  • Andre,

    Again, you're welcome and thanks for the feedback. I can't imagine who doesn't like the use of public variables but then to each his own.

    John

    Wednesday, April 25, 2018 1:24 PM