custom field erro

Question

I recorded this macro to automatically insert a formula into a custom field, but this error always appears. Can someone help me?

Insert a formula into a custom field via VBA. Displaying error message.

this is the code I'm using:

CustomFieldSetFormula FieldID:=pjCustomTaskText1, Formula:="format(IIf([Custo]=0;[% trabalho concluído];[COTA]/[Custo]*100);#0.0) & %"
CustomFieldPropertiesEx FieldID:=pjCustomTaskText1, Attribute:=pjFieldAttributeFormula, SummaryCalc:=pjCalcFormula, GraphicalIndicators:=False, AutomaticallyRolldownToAssn:=False

Answer 1

There are a few different things wrong with the code.

1. Some delimiters in the statement are commas and some are semicolons. All delimiters need to be the same, depending on your system setting
2. You are trying to format the whole IIF statement and that's not what you want. You only want to format the false argument of the IIF statement
3. The formula is being entered as a string statement and as such will have opening and closing quotes to delineate the string. However the format function also has a string argument (i.e. the format specification) and that must be shown in double quotes. The same thing applies to the % symbol, it needs to be shown as a sting in double quotes.

Try this construct, it works for me: CustomFieldSetFormula FieldID:=pjCustomTaskText2, Formula:="IIf([Custo]=0,[% trabalho concluido],format([COTA]/[Custo]*100,""#0.0"") & ""%"")" CustomFieldPropertiesEx FieldID:=pjCustomTaskText2, Attribute:=pjFieldAttributeFormula, SummaryCalc:=pjCalcNone, GraphicalIndicators:=False, AutomaticallyRolldownToAssn:=False