Using a custom function in MS Project

I do not use MS Project but I do a lot of work on data extracted from Project.  I'm trying to use  two custom functions in Project but can't figure out how to implament them.  I would like to customize a Project Number field and a Text field with the two functions below.  Both functions would use the Project field "Outline Number" as their attribute.

Examples:  
Text8 field customized with this formula   WBSSortKey([Outline Number])
Number8 field customized with this formula SortKey([Outline Number])

Is this possible?  If so how do I go about getting it done?

Thank you for your time.
Function WBSSortKey(v As Variant) As String
    Dim s() As String, i As Integer
    s = Split(v, ".")
    WBSSortKey = Format(s(0), "000|")
    For i = 1 To UBound(s)
        If i > 0 And CInt(s(i)) > 99 Then
            WBSSortKey = "#VALUE!"
            Exit Function
        Else
            WBSSortKey = WBSSortKey & Format(s(i), "00")
        End If
    Next
End Function

Function SortKey(rg As Range) As Double
  
    Const dot As String = "."
    Const NullString As String = ""
    Const MaxLevels As Long = 4 'Maximum number of levels
    Const MaxSubLevels = 99 'Maximum number of sublevels in each level; must be 10^x-1
      
    Dim i As Long
    Dim temp
      
    temp = Split(rg.Text, dot)
      
    For i = 0 To UBound(temp)
        SortKey = SortKey + temp(i) * 10 ^ (((MaxLevels - 1) - i) * Log(MaxSubLevels + 1) / Log(10))
    Next i
End Function

Open in new window

dec789Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Dr. Thomas HenkelmannConnect With a Mentor Director Consulting ServicesCommented:
crap, hit return too early...once again in the snippet below. I haven't check your functions though...

Regards

Thomas
Sub UpdateAllTasks()

    Dim oTask As Task
    
    For Each oTask In ActiveProject.Tasks
        If Not oTask Is Nothing Then
            oTask.Text8 = WBSSortKey(oTask.OutlineNumber)
            oTask.Number8 = SortKey(oTask.OutlineNumber)
        End If
    Next

End Sub

Open in new window

0
 
Dr. Thomas HenkelmannDirector Consulting ServicesCommented:
Hi,

you can't assign a custom formula to a field directly. What you basically need to do is to run through all tasks in a macro and then set the fields with your formula.

Sub UpdateAllTasks
dim oTask as Task

for each oTask in activeproject.tasks



End sub
0
 
dec789Author Commented:
Thomas thank you for your time and effort.  I had to tweak my functions but your portion worked great.

Thanks again,
Don
0
All Courses

From novice to tech pro — start learning today.