VBA Methods similar to the Built-in Functions in the Project category

Initially published on October 14, 2015 | Updated on May 17th, 2022

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

Although there may be some differences, the built-in functions other than the “Proj” functions have methods/functions of the identical names performing the same operations in MS Project VBA. As a result, you can easily convert a custom field formula to a VBA routine, for example, when you want to execute the same formula also for the assignment lines. At this point, the question to ask would probably be that “then what are the equivalents of the built-in functions starting with the prefix name “Proj” ?”. These functions and the corresponding methods are as follows:

ProjDateValue is not on the list but there are a couple of date related functions such as DateValue and CDate in VBA that can be used in place of ProjDateValue. Let us see how the equivalent methods work for automatically scheduled tasks. Note that some methods are global, therefore, they do not require to specify the object qualifier Application which is the default object in MS Project VBA. An eBook covering all the built-in functions is available here.

DateDifference method

The DateDifference method included in MS Project’s development environment works similar to the ProjDateDiff function. It also recognizes the date serial numbers passed through the Variant parameters and it always uses 00:00 (or 12:00 AM) as the default times when the date information does not include any time value. This is the syntax:

Application.DateDifference( start_date, end_date [,calendar] )

The ProjDateDiff function returns negative results if start_date is later than end_date, but the method returns 0. The Object type parameter calendar is optional (see the square brackets around it) and it is used to pass a calendar object. Follow the steps below in order to see how it works:

Suppose that the current day on the computer's clock is a Monday or Tuesday. 

• Press <Alt + F11> and then <Ctrl + G> to open the Immediate Window in MS Project’s development environment.

• The project calendar is “Standard”, so the method uses it by default in the statement below:

As demonstrated above, if you omit  the parameter calendar, both ProjDateDiff and DateDifference use the project calendar by default. Suppose that there is a task calendar applied to the task; then when you omit the third parameter, DateDifference keeps using the project calendar as calendar, while ProjDateDiff uses the task calendar. See the example below:

 

 

 

Immediate Window

? Application.DateDifference( ActiveCell.Task.Start, Activecell.Task.Finish, ActiveProject.BaseCalendars("24 Hours")) <enter> 480 ? Application.DateDifference( ActiveCell.Task.Start, Activecell.Task.Finish ) <enter> 420

 

Both ProjDateDiff( [Start], [Finish] ) and ProjDateDiff( [Start], [Finish], "24 Hours") ) return 480.

• As it is seen below, the output from Application.DateDifference which is based on the unmodified base calendar “24 Hours”, is the same as the one from DateDiff:

• And these are the examples for the date difference calculations with resource calendars and resource base calendars (Wednesdays are defined as nonworking days in Resource #2’s calendar):

The Application.DateDifference method does not accept dates that fall outside MS Project’s range of valid dates. The following statement returns 87307200 minutes which is a Long type value (the total amount of duration minutes that MS Project’s range of valid dates span on the timescale of unmodified base calendar “24 Hours”):

Application.DateDifference( "January 1, 1984", "December 31, 2149", GlobalBaseCalendars(“24 Hours”) )

And this is the formula with the DateDiff function returning the same result as above (the total amount of elapsed minutes between the two dates at the limits of MS Project’s range of valid dates):

DateDiff( "n", "January 1, 1984", "December 31, 2149")

Note that ProjDateDiff(#1/1/1984#,#12/31/2149#,"24 Hours") also returns 87307200 to a proper custom field.

Note    If the base calendar “24 Hours” has not been accessed before in the active project plan file, as this usually happens when testing the formula in a blank file, then the formula ProjDateDiff( start_date, end_date, “24 Hours” ) may return #ERROR. In this case, either use the Organizer dialog box to copy the base calendar “24 Hours” from Global.mpt to the active project plan file or use the base calendar “24 Hours” at least once in the active project plan file, for example, by temporarily selecting as the project calendar.

The EffectiveDateSubtract, EffectiveDateAdd, and EffectiveDateDifference properties should be used to perform calculations on the dates of the manually scheduled tasks starting or finishing on the non-working times defined in the effective calendar.

For T1 above, the formula ProjDateDiff( [Start], [Finish] ) returns 0 to the Number1 field since Saturday and Sunday are defined as non-working times in the project calendar which is the default calendar for the last optional parameter of the function. Now let us set focus to the task T1 by clicking any cell in the task’s row and then enter the following statements in the Immediate Window:

As it is seen above, the Application.DateDifference method returns 0. On the other hand, EffectiveDateDifference gives the accurate result, provided that both the Start and Finish fields contain valid dates for the manually scheduled task (that is, the task is not a placeholder).

DateAdd and DateSubtract methods

The DateAdd and DateSubtract methods included in MS Project’s development environment are used to add and subtract durations from the dates, and they work similar to the ProjDateAdd and ProjDateSub functions. Both methods return a date value. The syntax expressions are as follows:

Application.DateAdd( date, duration [, calendar] )

Application.DateSubtract( date, duration [, calendar] )

The date and duration are Variant parameters; see the DateDifference method for information on the calendar parameter.

See the demonstrations on these methods below:

• Press <Alt + F11> and then <Ctrl + G> to open the Immediate Window in MS Project’s development environment. And try the following entries:

Note that both methods use the default start time when a date value or a date string passed through the date parameter does not include any time value. Negative duration parameter values and the date parameter values that fall outside MS Project’s range of valid dates cause runtime error. A decimal duration value used as the actual parameter is rounded off to the nearest even integer before being passed to both methods. Passing a date string containing duration days greater than 72270 (e.g., “72271d”) causes runtime error. Both methods cannot return dates that fall outside MS Project’s range of valid dates; the following statements demonstrate how methods work with the largest minutes passed:

DurationFormat method

The DurationFormat method included in MS Project’s development environment works similar to the ProjDurConv function.

• Press <Alt + F11> and then <Ctrl + G> to open the Immediate Window in MS Project’s development environment and test the function as follows:

The statement DurationFormat( “72271 days” ) produces runtime error.

DurationValue method

The DurationValue method included in MS Project’s development environment works similar to the ProjDurValue function and returns the number of minutes (a Long value) corresponding to a duration expression passed through a Variant parameter. Any duration entry that is accepted in a duration type field can be passed as a duration string to the method.

DurationValue(1) returns the number of minutes per day (i.e., the Hours Per Day value * 60) in the active project plan, if the Duration is entered in box is set to <Days>; that is, the actual parameter value 1 is interpreted as 1 day.

Passing durations greater than 72270 days causes runtime error; this is also the limit when entering a duration value to a duration type field (e.g., Duration1) by typing in (note that the maximum value accepted by the Duration field depends on a project’s timescale). The method does not accept negative durations.

See the examples below:

• Click <Alt + F11> and then <Ctrl + G> to open the Immediate Window in MS Project’s development environment. And try the following entries:

DateFormat method

The DateFormat method included in MS Project’s development environment works similar to the ProjDateConv function; it recognizes the date serial numbers. See the examples below:

• Press <Alt + F11> and then <Ctrl + G> to open the Immediate Window in MS Project’s development environment.

• Pass a valid date string to the CDate function and multiply the output with 1 in order to obtain the date serial number including time. And then pass this serial number to the DateFormat method. See the outputs from the tests with the method below:

In order to convert a serial number to a date in a particular format, a formula such as ProjDateConv( CDate(42717.33680),pjDateDefault ) can be used. The pjDateDefault in both DateFormat and ProjDateConv are redundant since the output will be in MS Project’s current date display format by default when dateformat (that is, the second parameter) is omitted. The DateFormat method accepts only the dates that fall into MS Project’s range of valid dates; out of range date passed causes a runtime error.

The DateFormat method returns a String type value, as a result, it should be handled as text information. The help article containing function descriptions does not specify a data type for the return value of the ProjDateConv function in terms of the data types available in MS Project’s development environment (e.g., String), but its output is also text information that can be stored in custom text fields and handled as text information in formulas; and also recognized as valid date information in custom date fields obviously by the help of MS Project’s implicit text-to-date conversion automatically performed in the background.