MS Project VBA module - Task.Start +1 not recognizing that Saturday IS a working day

Question

Hello,

MS Project 2010 on Windows 7.

I'm using a wonderful VBA macro I found to force certain tasks to certain days of the week, since I can only do "prototypes" on Saturdays etc.

http://www.techrepublic.com/article/using-vba-to-tie-a-project-task-start-date-to-a-certain-day-of-the-week/

Here's the code

Do Until Weekday(T.Start) = 7

                    Debug.Print T.Start

                    Debug.Print Weekday(T.Start)

                    T.Start = T.Start + 1

                Loop

Here's a sample of the debug.prints

4/25/2016 8:00:00 AM 

 2 

4/26/2016 8:00:00 AM 

 3 

4/27/2016 8:00:00 AM 

 4 

4/28/2016 8:00:00 AM 

 5 

4/29/2016 8:00:00 AM 

 6 

5/2/2016 8:00:00 AM 

 2 

I forced the calendar on this project to recognize Saturday and Sunday as working days.  I can force this task (T) to a saturday in project proper, but as you see, it goes from Friday to Monday in the code.

Last night, I tried this with one line item and it worked.  Now trying to apply it to the entire file, it goes infinite loop at this point.  

I don't know how to resolve this.  As far as I can tell the code is correct, and the working days are setup and working in Project proper, but for some reason, the VBA doesn't recognize "working days" correctly while running.

Thanks for your help.

(I have to do this for dozens of tasks, so I don't want to switch to the "milestone" method I've seen before.

Answer 1

Why use VBA for this? Create a calendar that only has Saturdays working. Then assign that calendar to all Prototype tasks.

Answer 2

Blue,

Yeah, a more complete story would have been helpful at the onset.

Is there a reason for insuring the Text20 string is properly capitalized, other than to match the Select statements? If not, you don't need the text conversion. Use the Option Compare Text statement at the beginning (i.e. before the Sub()). That automatically takes care of any capitalization issues. I use it regularly in any macro where I need to look at a text string.

Okay, I see how you are incrementing task start dates to meet a target. I can't quite fathom such a requirement in real life but what exactly is not working? I ran you code on a text file and it seems to do what you intend, so what's the issue?

Do you have your project calendar set for a 6 or 7 day week? If not, do you at least have those tasks you want to force to a Saturday or Sunday to have a 6 or 7 day Task Calendar? Without one of those, Project won't move a task's start to a non-working day.

John

Answer 3

Hello,

Thanks for the response. 

The code is trying to increment to a target.  Sorry for only sharing a snippet.

The do loop is because I want to drive it to target( e.g. Saturday=7) not just increment.

For Each T In ActiveProject.Tasks

    'Only check the tasks that contain text in the Text20 field

    If T.Text20 <> "" Then

        'Ensure the contents of the Text field are in the right

        'case. The contents need to be first letter capitalized

        'and the rest of the text in lower case

        T.Text20 = StrConv(T.Text20, 3)

        'Store the original value of Task Start date so that it can

        'be rest in case of an error

        D = T.Start

        'Depending upon the contents of the Text20 field run

        'code that moves the tasks to the specified date

        Select Case T.Text20

            Case "Sunday"

                Do Until Weekday(T.Start) = 1

                    T.Start = T.Start + 1

                Loop

            Case "Monday"

                Do Until Weekday(T.Start) = 2

                    T.Start = T.Start + 1

                Loop

           Case "Tuesday"

                Do Until Weekday(T.Start) = 3

                    T.Start = T.Start + 1

                Loop

            Case "Wednesday"

                Do Until Weekday(T.Start) = 4

                    T.Start = T.Start + 1

               Loop

            Case "Thursday"

                Do Until Weekday(T.Start) = 5

                    T.Start = T.Start + 1

                Loop

            Case "Friday"

                Do Until Weekday(T.Start) = 6

                    T.Start = T.Start + 1

                Loop

            Case "Saturday"

                Do Until Weekday(T.Start) = 7

                    Debug.Print T.Start

                    Debug.Print Weekday(T.Start)

                    T.Start = T.Start + 1

                Loop

        End Select

    End If

Next T

Blue,

The Do loop seems a little overkill for what you want. Try the following code. Note, make sure you have the Project Calendar set for a 6 or 7 day workweek (I set mine for a 7 day workweek for this example).

Hope this helps.

John

Answer 4

Blue,

The Do loop seems a little overkill for what you want. Try the following code. Note, make sure you have the Project Calendar set for a 6 or 7 day workweek (I set mine for a 7 day workweek for this example).

Hope this helps.

John