Using Author and Subject Fields to Pass Data to Formulas
Published on October 21st, 2015
Please first see
Disclaimer, Copyright Notes and
Trademark Notes sections on the bottom of this page.
This document is related to all
standalone desktop versions of MS Project Professional/Standard supporting the
custom field formulas feature.
Instead of comparing
a text field’s value with zero-length string, you can
also use the Len function
to check whether a field holding text information is
blank; the Len function
will return 0 if the field is blank. But note that the
length returned by the Len function
will be one character longer than the actual string
length of a Project category field, such as Author and Subject;
therefore, the function will return 1 for a blank field
and comparing the field values with zero-length string
will result in false.
the Summary tab
of the File
box (FILE |
Project Information | Advanced
Properties) in order to modify the current
setting of the Project category fields Author,Subject and Title.
Note that Project will not allow a blank Title box;
the default is the filename. The default for the author
name is your Windows username.
In this special case, you can compare
the field’s value with a null character as in [Author]
= Chr(0) (note that the null character is identified
with the character code 0 in the ASCII character set).
The Chr function
returns a string containing the character associated
with the specified character code; for example, Chr(65) returns
the letter A since 65 is the code corresponding to the
character A in the ASCII character set. As a result, the
following formula will work in a custom text field:
iif( [Author]=Chr(0); "Enter
Author" ; “Author is ” & [Author] )
The Asc function
returns the character code for the first letter of the
string passed as parameter; for example, Asc(“ABC”) returns
65. Therefore, Asc([Author]) can
also be used to specify the condition tested in the iif function,
Author";"Author is " & [Author])
The expression Asc([Author]) will
return 0 (that is, false) if the field is empty, so
using Not will
enable us to get the true part of the iif function
evaluated when there is no author name entered.
Suppose that you want to pass the “US
Dollar to Euro” exchange rate to a custom field
formula converting the cost value in US Dollars to that
in Euros. You can use the Subject or Author field
to pass a numeric value to a formula as shown in the
iif( Not IsNumeric( [Subject] );
"Enter rate for USD to EUR"; Format( CDbl([Subject]) *
[Cost]; "0.00 €" ) )
You can also use "0.00
“ + chr(128) as
the format specifier if you have difficulty in
producing the Euro sign (€). The CDbl function
converts the Subject field’s
text to a numeric value.
The Subject field
may initially cause the formula to return #ERROR in
a blank project plan file. If this happens, enter
some random text to the Subject box and then delete
all the text. This will fix the problem and the
custom text field will show “Enter
rate for USD to EUR”.
This technique will prove useful when
you want to eliminate the need for user’s access to the
custom field formula for updating the exchange rate (or
any other numeric constant). Also note that both task
and resource custom field formulas can reference the Subject field
since it is a Project level (or category) field.
1 - October 21st, 2015 - Initial document