none
VBA for MS Project detect estimated durations RRS feed

  • Question

  • Ahoy my expert shipmates!

    I'm stuck.. as always - sorry

    Here's what I'm trying to do:

    Use VBA to loop through tasks and detect tasks in which:

    1. Duration is estimated

    2. Duration is decimalised

    Here's what I've tried:

    Sub CheckDurations()
        
    Dim t As Task
    Dim i As Integer
    Dim Duration As Integer
    
    i = 1
    'Remove any filters
    FilterApply Name:="All Tasks"
    
    'Show all Sub-tasks
    SelectSheet
        OutlineShowAllTasks
    
       
    For Each t In ActiveProject.Tasks
        
        If Not t Is Nothing Then
           SelectRow Row:=i, RowRelative:=False
             
         On Error GoTo Continue
                  
            Select Case True
            Case (t.Duration Like "*d?")
            t.Duration = InputBox("Duration is Estimated", "Check Duration!")
            
            Case (t.Duration Like "#.*" And t.Active = True)
            t.Duration = InputBox("Duration is Decimalised", "Check Duration!")
            
            End Select
                  
            End If
        End If
        
    Continue:
        i = i + 1
        Next t
        
    End Sub

    Here's the problem. t.Duration doesn't equal the text in the field. Nor anything like it!

    By using a MsgBox (t.Duration,vbOKonly,Duration) I have found that durations 'appear' to be a numeric value 48 times the duration in days. i.e. a measure of how many half-hours sum the total duration(?!)

    There doesn't appear to be any difference between the duration info in the MsgBox between estimated or actual durations.


    I know I'm a mere VB idiot, but can you point me in the right direction?

     Many thanks,

    Phil

    P.S. On a sub-note, I'm aware that my error-handling is horrific, ugly and crude (much like most of my work!) but I'll try to address that with the help of Google later today.

    P.S. on a sub-note I'm pretty sure my error handling could do with some work, but I'll cross that bridge..err.. later today with the help of Google!
    P.S. on a sub-note I'm pretty sure my error handling could do with some work, but I'll cross that bridge..err.. later today with the help of Google!
    • Moved by Chenchen Li Tuesday, September 20, 2016 7:56 AM Project
    Monday, September 19, 2016 12:58 PM

Answers

  • Use the Estimated field to tell if the duration is estimated (if t.Estimated then ...). Durations are stored in minutes so you'll need to get that into days to see if it's fractional. Use (if t.Duration mod 480 <> 0 then ...) for an 8 hour day. Use (activeproject.HoursPerDay * 60) if you want to be more accurate.

    • Marked as answer by PhilipDay Tuesday, September 20, 2016 1:45 PM
    Tuesday, September 20, 2016 1:11 PM

All replies

  • Hi,

    Thanks for posting here,

    Since your issue is about MS Project programming using VBA , I would move this thread into the following forum, there would be more community members to help you and focus on the specific issue about MS Project.

    https://social.technet.microsoft.com/Forums/projectserver/en-US/home?forum=project2010custprog

    Sorry for any inconvenience and have a nice day! 

    Regards,

    Celeste

    Tuesday, September 20, 2016 7:55 AM
  • Hi Celeste, 

    Thanks for moving the thread, sorry that I posted it in the wrong place.

    Phil

    Tuesday, September 20, 2016 9:49 AM
  • Use the Estimated field to tell if the duration is estimated (if t.Estimated then ...). Durations are stored in minutes so you'll need to get that into days to see if it's fractional. Use (if t.Duration mod 480 <> 0 then ...) for an 8 hour day. Use (activeproject.HoursPerDay * 60) if you want to be more accurate.

    • Marked as answer by PhilipDay Tuesday, September 20, 2016 1:45 PM
    Tuesday, September 20, 2016 1:11 PM