none
MS Project Custom Field value = max of other custom fields RRS feed

  • Question

  • Hi,

     

    I am trying to create a custom field that contains the maximum value of 7 other custom fields.  I expected this would be a simple process using the "formula" capabilities of customised fields.  However there is no "MAX" function and no help or documentation seems to be available.

    Is there a way to perform this in formulas or in VBA?

    Geoff

    Wednesday, January 4, 2012 8:35 PM

Answers

  • Let's try that in a bit more user friendly format....

    Sub MaxValue()

        Dim T As Task
       
        Dim MaxValue As String
       
        For Each T In ActiveProject.Tasks
       
        MaxValue = T.Number1
           
        If MaxValue < T.Number2 Then
            MaxValue = T.Number2
        End If
       
        If MaxValue < T.Number3 Then
            MaxValue = T.Number3
        End If
       
        If MaxValue < T.Number4 Then
            MaxValue = T.Number4
        End If
       
        T.Number5 = MaxValue
       
        Next T

    End Sub


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Wednesday, January 4, 2012 8:51 PM
    Moderator

All replies

  • A formula could be tricky but doable, here's some quick VBA code that would do something like that: Sub MaxValue() Dim T As Task Dim MaxValue As String For Each T In ActiveProject.Tasks MaxValue = T.Number1 If MaxValue < T.Number2 Then MaxValue = T.Number2 End If If MaxValue < T.Number3 Then MaxValue = T.Number3 End If If MaxValue < T.Number4 Then MaxValue = T.Number4 End If T.Number5 = MaxValue Next T End Sub
    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Wednesday, January 4, 2012 8:51 PM
    Moderator
  • Let's try that in a bit more user friendly format....

    Sub MaxValue()

        Dim T As Task
       
        Dim MaxValue As String
       
        For Each T In ActiveProject.Tasks
       
        MaxValue = T.Number1
           
        If MaxValue < T.Number2 Then
            MaxValue = T.Number2
        End If
       
        If MaxValue < T.Number3 Then
            MaxValue = T.Number3
        End If
       
        If MaxValue < T.Number4 Then
            MaxValue = T.Number4
        End If
       
        T.Number5 = MaxValue
       
        Next T

    End Sub


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Wednesday, January 4, 2012 8:51 PM
    Moderator
  • Love your work Andrew - thanks v much.

     

    Geoff.

    Wednesday, January 4, 2012 8:59 PM
  • You can also try the following:

    Sub FillMax()
    Dim T As Task
    For Each T In ActiveProject.Tasks
        If Not T Is Nothing Then
            T.Number8 = myMax(T.Number1, T.Number2, T.Number3, T.Number4, T.Number5, T.Number6, T.Number7)
        End If
    Next T
    End Sub


    Public Function myMax(ByVal x As Integer, ParamArray y())
        Dim i As Integer
        Dim max As Integer
        max = x
        For i = LBound(y) To UBound(y)
            max = IIf(max < y(i), y(i), max)
        Next
        myMax = max
    End Function

    Regards
    Barbara

    Wednesday, January 4, 2012 8:59 PM
    Moderator
  • I'd go with Barbara's solution.  :-)
    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Wednesday, January 4, 2012 9:04 PM
    Moderator