0

When looking at the possible PjAssignmentTimescaledData options (here), one that is missing that I need to extract is the Remaining Work field. Has anyone ever been able to figure out how to use VBA to extract out weekly assignments based on remaining work?

What I have is in a section of my VBA is:

ass.TimeScaleData(tsk.Start, tsk.Finish, pjAssignmentTimescaledActualWork, pjTimescaleWeeks)

but I would imagine i could replace

pjAssignmentTimescaledActualWork

with

pjAssignmentTimescaledRemainingWork

but that does not work.

Am I asking for something that just doesn't exist or looking at this whole operation in a backwards way?

vba-67678
  • 1
  • 2
  • Have you tried `pjAssignmentTimescaledRemainingCumulativeWork` or `pjAssignmentTimescaledRemainingCumulativeActualWork`? – Rachel Hettinger Nov 18 '21 at 21:22
  • I did try both of the above, but the issue is the "cumulative" extracts on a larger basis than it should on a per assignment basis. The math does not work out. I'm 98% certain that what I am looking for is just not possible where I could get the remaining work field specifically by assignment by week like I can with pjAssignmentTimescaledWork – vba-67678 Nov 19 '21 at 15:15
  • Well then perhaps do the delta between scheduled work and actual—two TimescaleData calls. – Rachel Hettinger Nov 19 '21 at 17:31
  • 1
    I didn't even know that was possibility! > Dim prj As Project Dim tsk As Task Dim ass As Assignment Dim tsd As TimeScaleValue Dim tsvw As TimeScaleValue 'time scale work Dim tsva As TimeScaleValue ' time scale actual work For Each tsk In prj.Tasks If Not (tsk Is Nothing) Then For Each ass In tsk.Assignments Set tsvw = ass.TimeScaleData(tsk.Start, tsk.Finish, pjAssignmentTimescaledWork, pjTimescaleWeeks) Set tsva = ass.TimeScaleData(tsk.Start, tsk.Finish, pjAssignmentTimescaledActualWork, pjTimescaleWeeks) Set res = prj.Resources(ass.ResourceID) For Each tsd In tsvw – vba-67678 Nov 19 '21 at 20:52
  • If that works, post it as an answer and accept it so that the solution will be available to future readers. – Rachel Hettinger Nov 20 '21 at 01:41
  • No luck with that option as describe above. I am not experienced with adding more than one element in VBA at a time, so this is kind of unchartered waters for me. Any advice? – vba-67678 Nov 22 '21 at 15:09

1 Answers1

0

Unfortunately, pjAssignmentTimescaledRemainingWork does not exist (which is annoying). To get the time phased remaining work, you need to take the value you get from pjAssignmentTimescaledWork and subtract the value you get from pjAssignmentTimescaledActualWork. Here's some sample code to get the time phased remaining work of selected tasks:

Public Sub GetTimeScaledRemainingValues()

    Dim a As Assignment
    Dim t As Task
    
    Dim totalTSVs As TimeScaleValues
    Dim actualTSVs As TimeScaleValues
    
    Dim totalTSV As TimeScaleValue
    Dim actualTSV As TimeScaleValue
    
    Dim remainingValue As Double
    
    For Each t In Application.ActiveSelection.Tasks
        For Each a In t.Assignments
            'get the total TSV values and store in a variable
            Set totalTSVs = a.TimeScaleData(t.Start, t.Finish, pjAssignmentTimescaledWork, pjTimescaleWeeks)
            'get the actual TSV values and store in a variable
            Set actualTSVs = a.TimeScaleData(t.Start, t.Finish, pjAssignmentTimescaledActualWork, pjTimescaleWeeks)
            
            'Loop through the total TSVs and try to find and substract the actual values
            For Each totalTSV In totalTSVs
                'Use this loop to find the actual TSV that has the same start date as the total TSV value we are currently looking at. These values will cover the same period
                For Each actualTSV In actualTSVs
                    If actualTSV.StartDate = totalTSV.StartDate Then
                        'If the actual value is zero the property returns an empty string, so we have to check for this because we cannot subtract an empty string from a number, it will cause a VBA error.
                        If actualTSV.Value <> "" Then
                            'subtract the actual value from the total value to get the remaining
                            remainingValue = totalTSV.Value - actualTSV.Value
                        Else
                            remainingValue = totalTSV.Value
                        End If
                        
                        'the Value property of TSV returns in minutes. divide by 60 to get the remaining hours
                        remainingValue = remainingValue / 60
                        
                        Exit For
                    End If
                Next actualTSV
                
                'print out the remaining value information
                Debug.Print "There are " & remainingValue & " remaining hours of " & a.ResourceName & " on " & t.Name & " between " & totalTSV.StartDate & " to "; totalTSV.EndDate
                
            Next totalTSV
        Next a
    Next t

End Sub

Here's a sample of what my output looks like:

There are 16 remaining hours of Security Engineer on Create Security Requirements Traceability Matrix (SRTM) between 3/7/2021 to 3/14/2021

Kenny Arnold
  • 406
  • 2
  • 8
  • Hi Kenny. Yes, I've come to this conclusion on this is what has to be done, but having issues on how to make it happen. I'm only use to writing one element in VBA at a time, so this is kind of unchartered waters for me. Any advice? – vba-67678 Nov 22 '21 at 15:06
  • @vba-67678 I've added some sample code to my answer that hopefully gets you rolling. – Kenny Arnold Nov 23 '21 at 00:15