Probably dead obvious...

[Ecniv]

Um...

Nope no idea. Tried putting that function (which returns "Test") and it didn't run. Just got the #Name?

I take it the function when run in the VBA window works fine. But the function doesn't run from the spreadsheet. And it doesn't error. No idea . quirk of MS Excel...

Which version are you running (being curious) ?

EDIT:
Version Excel 2000 here...
Shoved the code into a module (not the ThisSheet module) and it works fine.
Is it something in your code? (not likely) or perhaps a re-install of excel is required.
Tested with this code:

Public Function CollateAndCalcShare() As String
    CollateAndCalcShare = "Test" & Cells(2, 3)
End Function

The function is in cells(2,2) (B2)