none
Converting Dates to Long in MS Project VBA RRS feed

  • Question

  • Hello,
    Using VBA in MS Project I want to convert the Act. Start value to a Long,
    Similarly I want to convert the % Comp. and a few others to a Long, Double or Int, then run a formula with those
    numbers giving me output.
    The purpose of the output is to tell me if I have tasks scheduled in the past or completed in the future.

    One way to do it would be insert a column Number1 put a formula in that field to get the root number.
    But Cmon! that is amateur!! 


    Tuesday, July 9, 2013 1:56 AM

All replies

  • Mark,

    Unless I'm missing something in your question, this is a piece of cake. Simply use one of the data conversion functions available in VBA (e.g. cLng(expression), cDbl(expression), or cInt(expression).

    However, there are a number of built-in filters that may already give the information you are after. If not, you can always create a custom filter with VBA that could give you the info you need. Further, the DateDifference Method is a good way to compare dates and in some case a simple subtraction will work.

    Hope this helps.

    John

    Tuesday, July 9, 2013 2:35 AM
  • Hi John, no doubt the answer will be insultingly simply,
    I have executed your above suggestions to attain more errors.
    Maybe you can see my fault.

    I have thought about date difference, but I do not think it will work in my case.
    My If statement is:
    If (Not N/A And Percent Complete <> 100 And (ActStart+ActDuration) - Status Date < 0.01) Then
    Tell user, you either have tasks scheduled in the past or completed in the future.  




    Tuesday, July 9, 2013 4:11 AM


  • only allowed 2 images per upload.
    Tuesday, July 9, 2013 4:11 AM
  • Hi,

    The whole answer is don't try to convert dates to anything else. Use Dateadd, DateDifference functios: they are there for the purpose.

    Greetings,

    Tuesday, July 9, 2013 10:53 AM
    Moderator
  • Ok I will further look into those functions.

    Cheers

    Tuesday, July 9, 2013 3:38 PM
  • Mark,

    I don't think anything is "insultingly simple". You shouldn't feel insulting simply because you don't know or haven't learned something. However sometimes it is easy to overthink something. In this case I think it's a combination of overthinkging and underthinking. At any rate the following code should do what you want. This will set a flag for all tasks that have a scheduled finish date before the status date and are not complete, or an actual finish date after the status date. It will also flag the associated summary line to provide perspective. It will then apply a filter to isolate the flagged tasks.

    Sub PastFuture()
    Dim t As Task
    Dim sd As Date
    Dim pc As Single
    sd = ActiveProject.StatusDate
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            pc = t.PercentComplete
            If ((t.ScheduledFinish < sd And pc < 100) _
                Or (t.ActualFinish < 50000 And t.ActualFinish > sd)) _
                Then t.Flag1 = True: t.OutlineParent.Flag1 = True
        End If
    Next t
    FilterEdit Name:="PastFuture", taskfilter:=True, create:=True, _
        overwriteexisting:=True, FieldName:="flag1", test:="equals", _
        Value:="yes", ShowInMenu:=False, showsummarytasks:=False
    FilterApply Name:="PastFuture"

    End Sub

    When learning to program with VBA your best friend is the object browser. You can get to it by clicking on the object browser icon just below the "Add-in" menu item in the VB Editor window. Your second best friend is Rod Gill's book on Project VBA. You can find out more about it at: http://www.projectvbabook.com

    However, speaking of overthinking, this whole thing could be done with a filter. All you need to do is to create a custom date field (e.g. Date1) that contains the Project Status Date. Then create this filter:


    Or..... you could also use the "If" statement in the VBA code above in a custom flag field.

    So many choices, you should feel you mind being boggled.

    Hope this helps

    John





    Tuesday, July 9, 2013 3:46 PM