Help with VBA Formula for Status Colors

Home Forums Discussion Forum Help with VBA Formula for Status Colors

Viewing 5 reply threads
  • Author
    Posts
    • #411648
      Megan
      Guest

      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
      Sai Prasad
      Participant

      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
      Megan
      Guest

      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
      Sai Prasad
      Participant

      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
      Megan
      Guest

      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
      Megan
      Guest

      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 5 reply threads
  • You must be logged in to reply to this topic.