none
fast forward Project time RRS feed

  • Question

  • For simulation purposes, I would like to use VBA in MSProject 2010, to advance the calendar date.

    Essentially having the next day appear, every 5 seconds, and watching the scheduling and resource results.

    I have VS 2008, but it may be possible as an VS 2010 add-in.

    Thanks


    Greg Borge
    • Moved by Brian Kennemer, MVPModerator Friday, November 18, 2011 6:08 PM (From:Project Standard and Professional General Questions and Answers)
    Friday, November 18, 2011 2:58 AM

All replies

  • Hi Greg,

    While a few VBA gurus frequent this forum, you may be a quicker response by posting to the forum devoted to project customization:

    http://social.technet.microsoft.com/Forums/en-US/project2010custprog/threads

    When reposting, you might find you get a quicker response by expanding a bit more on what you need, what you've done, and your specific question.  I personally am not sure what you mean by "having the next day appear, every 5 seconds......"  Are you trying to change project start date to each different day?

    Julie

    Friday, November 18, 2011 6:19 AM
    Moderator
  • Theoretically the code below should work but for me it is not updating the gantt chart so if you step through it line by line it works but if you run it it just waits 15 seconds and then puts you at the end result which is 3 weeks after the project start date.

    A head scratcher for sure but maybe you can play with it and figure it out.

    Sub ScrollingCode()
    
    Dim Count As Integer
    Dim D As Date
    Dim Pausetime, Start, Finish
    
    D = ActiveProject.ProjectStart
    
    EditGoTo Date:=D
    
    For Count = 1 To 3
        Pausetime = 5
        Start = Timer
        Do While Timer < Start + Pausetime
            '5 seconds pass
        Loop
        
        D = DateAdd("d", 7, D)
        EditGoTo Date:=D
    Next Count
    End Sub
    


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Friday, November 18, 2011 6:10 PM
    Moderator
  • What scheduling and resource results are you expecting. All you can get is the scenario where everything happens exactly according to schedule so I question the value in this.

    The Resource and Task usage Views already show work loads by day or week or month etc.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Friday, November 18, 2011 11:26 PM
    Moderator
  • Ahh, I was not thinking about it from that direction. I thought he wanted to scroll the Gantt chart automatically. LOL
    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Friday, November 18, 2011 11:34 PM
    Moderator
  • Well he said Calendar date which could mean a number of things so you could still be right!

    I should also have added that unless the solution needs to be sold or distributed to hundreds of users, VBA will be significantly easier and quicker to develop.


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Saturday, November 19, 2011 2:06 AM
    Moderator
  • Brian,

    When I first read gborge's post I had the same thought as Rod, namely how is this going to be a "simulation" if all it is going to do is scroll. The code will have to incorporate some type of algorithm to simulate progress in one form or another or it will simply be an auto-scroll with no real purpose. However, I was intrigued as to why your code didn't work when in "run" mode and therefore got hung up on the "exercise for the student". It's interesting what I found.

    First a minor item. The declaration for Pausetime, Start, and Finish doesn't do anything. Those variables will be of type variant since no other declaration is made. No declaration at all would give the same result.

    Now for the interesting stuff. One might think that using the EditGoTo Method would force execution into foreground mode and thus cause the screen to be updated. Apparently that's not the case. I fiddled around with various approaches concentrating on a Select Method for forcing foreground processing since I know that works with previous versions of Project VBA. Not so with Project 2010. Thus far I have been unable to find a code sequence that will allow screen updating during execution, at least not for scrolling. Perhaps I'm just not clever enough. It is interesting to note that it WILL work with Project 2007 IF a Select Method is used in conjunction with EditGoTo and a delay loop.

    I realize this doesn't really answer anything but since I spent the time working the issue I thought I'd at least share what I found. Maybe someone else will jump in with a whiz-bang approach and then we'll all be smarter.

    John

    Saturday, November 19, 2011 5:57 PM
  • Hi John, yes Start and PauseTime should both be declared as Single. TO get the screen to update, add a DoEvents command in the loop. DoEvents "Yields execution so that the operating system can process other events."

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Saturday, November 19, 2011 11:10 PM
    Moderator
  • Rod,

    I hoped someone more clever than I would step in. I've never used the DoEvents Function, but it works perfectly in this case. Now, the only thing left is to develop that algorithm to perform the simulation. There's where I'll leave it as an "exercise for the student".

    John

    Sunday, November 20, 2011 2:17 AM
  • Hi Julie;

    Initially, this was only for my wishes, but it's turned into something for client presentations. I want to update the project status by moving the StatusDate bar one hour/day /week at a time.  It provides a more "realistic" view of the project, vs the drone and groan of ppts.

    Here's the code that does this.

    'Macro to advance status day, one day at a time

     

    Sub DayxDay()

     

    For ScheduleDay = 1 To 3

     

    ActiveProject.StatusDate = ActiveProject.StatusDate + 1

     

    MsgBox ActiveProject.StatusDate

    Next ScheduleDay

    End Sub

     

    A simulation is the software model of a business or process, that takes actual business inputs, and produces actual business output.

    This would be called a baseline. Given the business wants to make changes, before spending money on real changes, model the changes in software first.

    I want to change the variables and display the Update results on the screen, real-time, probably displaying the statistics table and certain table information.

    As the following posts point out, the information is already in MSP, but it's slow, dull, boring and static.

    In conversations from last week, it would be great to get the process time down to a granularity of 1 minute. I need the Statistics window updated

    by the minute.  In the code above, I need ActiveProject.StatusMinute.

    Greg

     


    Greg Borge
    Saturday, November 26, 2011 10:19 PM
  • Hi Brian;

    You're close. ActiveProject.StatusDate +1  increments day by day.  Note the code in my reply to Julie.

    Gantt charts also have minute and hour granularity,  but Status Updates does not seem to have minute or hour capability.

    Using .statusDate + dateAdd ( "h", 1, statusDate), to add one hour to the status time didn't work, although the example says it should.

    I can work without a timer, using msgbox instead to pause the loop.

     

    Greg


    Greg Borge
    Saturday, November 26, 2011 11:08 PM
  • If one is entering a "Reply" and click on a vote, the reply disappears.

    The value I'm looking for is a more visual and dynamic display. It can be difficult to get a client to sit for a 20 minute slide show, but they will pay attention for 10 minutes of a vertical green status bar moving day x day or week x week accross the Gantt chart, displaying the corresponding values.

    They will also be more impressed if they suggest a variable change that can be completed and the results displayed within a few seconds.

    I will know more in the coming weeks.


    Greg Borge
    Saturday, November 26, 2011 11:47 PM
  • VBA is the current tool of choice, but managed code may be the real solution.

    From what I have read, Visual Studio 2010 has the hooks for MSP 2010, I only have VS 2008 now.


    Greg Borge
    Saturday, November 26, 2011 11:51 PM
  • John;

    One issue with MSP maybe that, once the project is started,  the engine can not be reset back to the start date.  I do have 1 good way around that.

    Also, MSP has the capability to add formulas to cells, this may help.

    Greg.


    Greg Borge
    Sunday, November 27, 2011 12:04 AM
  • Greg,

    You responded to Julie, then to Brian, then to either everybody or nobody, I can't really tell and then to me. But in all that I don't really see a question, just a bunch of statements. So......do you need more help or not? If not, please mark somebody's response as an answer so the thread is closed. Otherwise please give us a concise question so we can respond. Thanks.

    John

    Sunday, November 27, 2011 2:28 AM
  • My code did not reference StatusDate + 1.

    However, the code below DOES work for me in moving the status date forward 1 hour.

    ActiveProject.StatusDate = Application.DateAdd(ActiveProject.StatusDate, "1h")

     

    That said Im now sure why most customers would want to move the status date forward an hour. the only case where this would be useful is on projects where the durations of tasks were VERY short and status was updated on an hourly basis.

    In the vast majority of projects having the status date default to the end of the day is most applicable.

    Im also not sure what a customer would be doing watching a macro move just the status date hour by  hour. All that would do would be to change the EV data to show that the project was farther and farther behind. To make it mean something your code would also need to update some actual work\durations and some remaining work\durations and set the status date and then rechedule the remaining work past the status date.

    It would seem a better experience to have one of your people walked them through this process rather than having it be an automated thing.


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Sunday, November 27, 2011 4:17 AM
    Moderator
  • Brian,

    I think you meant to say "not" instead of "now" in your first main paragraph. Beyond that, I agree totally with you. The whole idea seems like a whiz-bang marketing show with no practical value, but then, I'm not a salesman.

    John

    Sunday, November 27, 2011 4:21 PM