none
Automatically entering calender exceptions from resource information MSProject, VBA RRS feed

  • Question

  • Hi there,

    Not sure if this is the wrong location, hopefully not.

    I have two custom fields for Date1 and Date3 which represent dates of calibration for a resource (instrumentation, and devices) I'm trying to generate a macro that automatically inputs these two dates as the start and end date for a resource's exception, therefore blocking the device.

    This macro would then run for all resources in the list, acting as a master resource pool for our instruments and devices.

    the code i've been able to make is below, but it gives an 1101 runtime error, saying the number of implied occurances from this exception's configuration is too large. I know this should be a relatively simple macro to write, but i'm completely lost in the syntax, and can't see the error when using MSProject.

    Sub Macro1()
    Dim R As Resource
    For Each R In ActiveProject.Resources
       
        ActiveProject.Resources(R).Calendar.Exceptions.Add Type:=1, Start:=R.Date1, Finish:=R.Date3, Name:="Calibration"
      
    Next R
         
    End Sub

    Thanks for your help!

    Tuesday, October 14, 2014 7:19 AM

Answers

  • Hi uztnusy,

    I don't think there's anything wrong with your syntax.

    Can you verify the Date1 and Date3 custom fields are filled in for all your resources.

    Sub Macro1()
    Dim R As Resource
    
    For Each R In ActiveProject.Resources
      If Not (R.Date1 = "NA" Or R.Date3 = "NA") Then
            R.Calendar.Exceptions.Add Type:=1, Start:=R.Date1, Finish:=R.Date3, Name:="Calibration"
        Else
            Debug.Print "Resource Custom Field Date1 or Date3 not set for resource " & R.Name
      End If
    Next R
         
    End Sub
    Hope this helps
    • Marked as answer by uztnusy Tuesday, October 14, 2014 9:24 AM
    Tuesday, October 14, 2014 8:26 AM

All replies

  • Hi uztnusy,

    I don't think there's anything wrong with your syntax.

    Can you verify the Date1 and Date3 custom fields are filled in for all your resources.

    Sub Macro1()
    Dim R As Resource
    
    For Each R In ActiveProject.Resources
      If Not (R.Date1 = "NA" Or R.Date3 = "NA") Then
            R.Calendar.Exceptions.Add Type:=1, Start:=R.Date1, Finish:=R.Date3, Name:="Calibration"
        Else
            Debug.Print "Resource Custom Field Date1 or Date3 not set for resource " & R.Name
      End If
    Next R
         
    End Sub
    Hope this helps
    • Marked as answer by uztnusy Tuesday, October 14, 2014 9:24 AM
    Tuesday, October 14, 2014 8:26 AM
  • Hi Toon S,

    Thanks for your help! I did have some resources not filled, which i think was the problem. I created a new dummy item Date2, ensuring all data was entered. I still receive an error message, however after ending the macro, and checking the resource entries, the exceptions have actually been included.

    The error message is another run time error (1101) that "this exception conflicts with a pre-existing exception on this calender and cannot be added."

    I don't have any pre existing expections though, so i'm not sure where the error is originating. could i have missed an automated calendar exception somewhere?

    Sub Macro1()
    Dim R As Resource
    
    For Each R In ActiveProject.Resources
      If Not (R.Date1 = "NA" Or R.Date2 = "NA") Then
            R.Calendar.Exceptions.Add Type:=1, Start:=R.Date1, Finish:=R.Date2, Name:="Calibration"
        Else
            Debug.Print "Resource Custom Field Date1 or Date2 not set for resource " & R.Name
      End If
    Next R
         
    End Sub

    Thanks again

    Simon

    Tuesday, October 14, 2014 8:49 AM
  • Hi Simon,

    Maybe you ran the macro, it failed but did add some exceptions.

    The next time you try to run it again -> it fails because of the exceptions created in the previous run.

    You can cover this case using the lazy 'try catch' way or by first checking the current exceptions for the resource and validating the exception you want to add.

    Hope this helps

    Tuesday, October 14, 2014 9:03 AM
  • Thanks for your response.

    I set up the macro to check current exceptions and Validate as you said, which seems to have the macro working now! 

    Thanks 

    Simon

    Tuesday, October 14, 2014 9:25 AM