Help with VBA Formula for Status Colors

[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)

[Sai Prasad]

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)))))))))

[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)))))))))))))))

[Sai Prasad]

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)

[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)))))))))))))))))))

[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.

[Author]

Posts