Home Forums Discussion Help with VBA Formula for Status Colors

This topic contains 5 replies, has 2 voices, and was last updated by  Megan 4 months, 4 weeks ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #411648 Reply

    Megan

    I am not well-versed in VBA, so I’m hoping someone can assist me :).

    We are on a complex Program and need a more complex Color Status field. I have the following defined separately but will need to be in a single equation when I’m done. I tried to combine them into a single line of code, I don’t get errors in VB Editor, but when I enter as a Formula for a field, I get a syntax error.

    'Future Task
        'On Track (Start/Finish = Baseline)
            White = IIf([Baseline Start] > [Status Date] And ([% Complete] = 0), 0)
        'Forecast Late Finish (Finish after Baseline Finish)
            Red Flag = IIf([Finish] > [Baseline Finish] And ([% Complete] = 0) And ([Start] > [Baseline Finish] Or [Start] = [Baseline Finish]), 6)
        'Forecast Late Start (Start after Baseline Start)
            Yellow Flag = IIf([Start] > [Baseline Start] And ([% Complete] = 0) And ([Finish] > [Baseline Finish] Or [Finish] = [Baseline Finish]), 7)
        'Forecast Finish early, Start early or on-time
            Green Flag = IIf([Finish] < [Baseline Finish] And ([% Complete] = 0) And ([Start] > [Baseline Finish] Or [Start] = [Baseline Finish]), 8)
    
          
    'Current/Past Tasks
        'Complete
            Grey = IIf([% Complete] = 100, 5)
            
        'On Schedule (Start early or on-time, Finish on-time) and % Complete on time
            Green = IIf(([Baseline Start] < [Status Date] Or [Baseline Start] = [Status Date]) And ([Start] < [Baseline Start] Or [Start] = [Baseline Start]) And ([% Complete] < 100) And (([Finish] = [Baseline Finish]) Or ([Finish] < [Baseline Finish])), 1)
          
        'Start Late, Finish Early or On Time; or % Complete not on track (should be 50% complete, but only 40%)
            Yellow = IIf(([Status] = "Late") And (([Finish] = [Baseline Finish]) Or ([Finish] < [Baseline Finish])) Or (IIf(([% Complete] > 100 * (Abs(([Status Date] - [Start]) / ([Finish] - [Start])))), 3)))
        
        'Finish Late (any start); or should have started but % Complete = 0
            Red = IIf(([Finish] > [Baseline Finish]) And ([% Complete] < 100), 4)
    #411660 Reply
    Sai Prasad
    Sai Prasad
    Participant
    • Community Leader
    • Forum Pro

    On the Yellow variable, you have subtracted [Start] from [Status Date] and [Finish] with – operator. This will not work in Formula. You need to use the function ProjDateDiff. I have combined all the formulas into one

    IIf([Baseline Start]>[Status Date] And ([% Complete]=0),0,IIf([Finish]>[Baseline Finish] And ([% Complete]=0) And ([Start]>[Baseline Finish] Or [Start]=[Baseline Finish]),6,IIf([Start]>[Baseline Start] And ([% Complete]=0) And ([Finish]>[Baseline Finish] Or [Finish]=[Baseline Finish]),7,IIf([Finish]<[Baseline Finish] And ([% Complete]=0) And ([Start]>[Baseline Finish] Or [Start]=[Baseline Finish]),8,IIf([% Complete]=100,5,IIf(([Baseline Start]<[Status Date] Or [Baseline Start]=[Status Date]) And ([Start]<[Baseline Start] Or [Start]=[Baseline Start]) And ([% Complete]<100) And (([Finish]=[Baseline Finish]) Or ([Finish]<[Baseline Finish])),1,IIf(([Finish]>[Baseline Finish]) And ([% Complete]<100),4,IIf(([Baseline Start]<[Status Date] Or [Baseline Start]=[Status Date]) And ([Start]<[Baseline Start] Or [Start]=[Baseline Start]) And ([% Complete]<100) And (([Finish]=[Baseline Finish]) Or ([Finish]<[Baseline Finish])),1,IIf(([Status]=”Late”) And (([Finish]=[Baseline Finish]) Or ([Finish]<[Baseline Finish])) Or ([% Complete]>100*(Abs(ProjDateDiff([Status Date],[Start])/ProjDateDiff([Finish],[Start])))),3)))))))))

    #411706 Reply

    Megan

    Thank you! I have the formula figured out except for the last line. I get a syntax error on the = signs. Any ideas??

    New_EWI = _
     IIf(([Finish Variance] = 0) And ([Baseline Start] > [Status Date]) And ([% Complete] = 0), 0, _
    (IIf(([Baseline Start] <= [Status Date]) And ([Start] = [Baseline Start]) And ([% Complete] < 100) And ([% Complete] > 0) And ([Finish] = [Baseline Finish]), 1, (IIf(([Baseline Start] <= [Status Date]) And ([Start] < [Baseline Start]) And ([% Complete] < 100) And ([% Complete] > 0) And ([Finish] <= [Baseline Finish]), 2, _
    (IIf(([Baseline Start] <= [Status Date]) And ([% Complete] < 100) And ([% Complete] > 0) And ([Baseline Finish] > [Status Date]) And (([Finish] <= [Baseline Finish])), 3, _
    (IIf(([Finish] > [Baseline Finish]) And ([% Complete] < 100) And ([Baseline Finish] >= [Status Date]) _
    Or (([Start] <= [Baseline Start]) And ([% Complete] = 0) And ([Baseline Start] <= [Status Date])) _
    Or (([% Complete] < 100) And ([Baseline Finish] <= [Status Date])), 4, _
    (IIf([% Complete] = 100 And ([Finish] < [Status Date]), 5, _
    (IIf(([Finish Variance] > 0) And ([Baseline Start] > [Status Date]) And ([% Complete] = 0) And ([Finish] > [Baseline Finish]))), 6, _
    (IIf(([Finish Variance] < 0) And ([% Complete] = 0) And ([Start]) > [Status Date])), 7, _
    (IIf(([% Complete] = 100) And ([Finish] > [Status Date]), 8, _
    (IIf(([Baseline Finish] = ProjDateValue("NA")), 9)))))))))))))))
    #411708 Reply
    Sai Prasad
    Sai Prasad
    Participant
    • Community Leader
    • Forum Pro

    Lookng at the format of the code with _ at the end of each line, you are using this code in VBA. ProjDateValue works only in custom field formula. Replace (IIf(([Baseline Finish] = ProjDateValue(“NA”)), 9) with (IIf(([Baseline Finish] = “NA”), 9)

    #411710 Reply

    Megan

    That didn’t work either. I still get an error on the = sign: “The formula contains a syntax error or contains a reference to an unrecognized field or function name.”

    IIf(([Finish Variance]=0) And ([Baseline Start]>[Status Date] And ([% Complete]=0)),0,(IIf(([Baseline Start]<=[Status Date]) And ([Start]=[Baseline Start]) And ([% Complete]<100) And ([% Complete]>0) And ([Finish]=[Baseline Finish]),1,(IIf(([Baseline Start]<=[Status Date]) And ([Start]<[Baseline Start]) And ([% Complete]<100) And ([% Complete]>0) And ([Finish]<=[Baseline Finish]),2,(IIf(([Baseline Start]<=[Status Date]) And ([% Complete]<100) And ([% Complete]>0) And ([Baseline Finish]>[Status Date]) And (([Finish]<=[Baseline Finish])),3,(IIf(([Finish]>[Baseline Finish]) And ([% Complete]<100) And ([Baseline Finish]>=[Status Date]) Or (([Start]<=[Baseline Start]) And ([% Complete]=0) And ([Baseline Start]<=[Status Date])) Or (([% Complete]<100) And ([Baseline Finish]<=[Status Date])),4,(IIf([% Complete]=100 And ([Finish]<[Status Date]),5,(IIf([Finish Variance]>0 And ([Baseline Start]>[Status Date] And ([% Complete]=0) And [Finish]>[Baseline Finish]),6,(IIf([Finish Variance]<0 And ([% Complete]=0) And ([Start]>[Status Date]),7,(IIf([% Complete]=100 And ([Finish]>[Status Date]),8,(IIf([Baseline Finish] = “NA”), 9)))))))))))))))))))

    #411711 Reply

    Megan

    And… I am using this in a custom field in MSP, but I started creating the formula in VBA to ensure each line/criteria works and narrow down errors. When I paste the code into the MSP field, I remove the _ at the ends.

Viewing 6 posts - 1 through 6 (of 6 total)
Reply To: Help with VBA Formula for Status Colors
Your information:




80 − = 77