Excel VBA - calling a function located in a worksheet code module

[TheRobster]

In a normal VBA module I have the following (as an example):

Sub Call_Function_Test
    Dim lngNumber as Long
     lngNumber = WorksheetFunction(10)
    MsgBox (lngNumber)
End Sub

In the code module for a worksheet called Tracker I have the function:

Public Function WorksheetFunction (ByVal input As Long) as Long
WorksheetFunction = input * 5
End Function

Which should send a value of 10 to the function, multiple by 5, return 50 and then display a message box showing "50". However all that happens is I get the error: Compile error: Sub or function not defined.

If the Public Function is in a normal module it works so I'm guessing that VBA can't "see" the function if it's in a worksheet code module. Is this true? If so how do I make it be "seen" in the code?

Cheers
-Rob

[westconn1]

to call a function in an object module from code, you would address the function by modulename (or any sheet object name) .functionname
i do not believe you can do this with worksheet functions as they need to be available to all sheets by default

anyway, good practise to put functions in a standard module, keep object modules for events and related code