help translating into VBA to place in ent global

[Erin]

Can anyone help translate this to VBA code? It's currently in MS Project
formula but there is a character restriction to the code when i place it in
the formula feature in the enterprise number field area -- therefore, i was
thinking a solution might be to store it as a VBA macro within the enterprise
global ? Would i need to open up and recalc all my projects if i do this?
I'll paste the code i'm currently using below -- i need this code to set
EnterpriseProjectNumber4 and tehn i can remove the formula from the function
within customize enterprise fields...

*************
=IIf(Enterprise_Project_Text33="Deleted",600,IIf(Enterprise_Project_Text33="Completed",600,IIf(Enterprise_Project_Text33="Duplicate",600,IIf(Enterprise_Project_Text24="Emergency",Enterprise_Project_Number2,IIf(Enterprise_Project_Text24="Routine",200+Enterprise_Project_Number2,Enterprise_Project_Number2+(IIf(Enterprise_Project_Date2="NA",IIf(Enterprise_Project_Date3="NA",100,IIf(DATEVALUE(Enterprise_Project_Date3)>(NOW()+1360),400,IIf(DateValue(Enterprise_Project_Date3)>(NOW()+1095),300,IIf(DateValue(Enterprise_Project_Date3)>(NOW()+730),200,100)))),IIf(DateValue(Enterprise_Project_Date2)>(NOW()+1360),400,IIf(DateValue(Enterprise_Project_Date2)>(NOW()+1095),300,IIf(DateValue(Enterprise_Project_Date2)>(NOW()+730),200,100))))))))))

Thanks in advance for any help

(e-mail address removed)

 

[Mark VII]

Can anyone help translate this to VBA code? <<

Hi Erin --

I'll pass on trying to reverse engineer that nested IIF, but have a
suggestion or two.

Are you familar with how to write a macro that would loop through all the
tasks in the plan, applies the necessary logic, and updates the field that
you're currently trying to create a formula for? Any programming background?


A macro could loop through the tasks, and the nesting of IIF's could be
replaced by appropriately nested If-Then-Else structures.

Here's roughly what it would look like. Of course, the If-Then-Else's would
be much more complex, in order to implement your logic.

dim tsk as MSProject.Task

for each tsk in ActiveProject.Tasks
if tsk.fieldname = <some sort of value> then
if tsk.anotherfieldname = <some value> then
'* action
else
'* another action
end if
else
'* some sort of action
end if

next tsk

I avoid nesting IIF's more than two deep like the plague.

HTH,
Mark