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.

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
            WBSSortKey = WBSSortKey & Format(s(i), "00")
        End If
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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dr. Thomas HenkelmannDirector Consulting ServicesCommented:

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
Dr. Thomas HenkelmannDirector Consulting ServicesCommented:
crap, hit return too early...once again in the snippet below. I haven't check your functions though...


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

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dec789Author Commented:
Thomas thank you for your time and effort.  I had to tweak my functions but your portion worked great.

Thanks again,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Project Management Software

From novice to tech pro — start learning today.