calling excel worksheet function from excel cell

Question

I have a set of user defined vba functions that sit in an excel module, which are then called from an excel spreadsheet... everything has worked fine at this point.

I've been asked to move the vba from the module to the worksheet's code page. When I did this, I've found I can't call any of the functions from cells on the worksheet... the names simply don't show as existing. Is there a way to call worksheet functions from an excel cell? Also, is there any problem calling a worksheet function from a user defined function in another module or worksheet code behind?

---

EDIT:

I've found if I call by the sheetname.functionname, it throws an error message that includes "The name conflicts with an Excel built-in name or the name of another object in the workbook"... where if I use sheetname.anythingelse it just resolves to #NAME?

Does this mean excel worksheet functions cannot be called from a sheet?

Solution 1

No. Functions in the worksheet object can't be called from the sheet as user-defined functions.

The Worksheet object is intended for responding to events that happen on a worksheet. You can't put user-defined functions there. User defined functions have to live in a Module.

If your user-defined function does live in a module, you won't have any problem calling it from code anywhere else... including in the worksheet "code-behind".

Solution 2

You have to put the code in a standard module. Check this link.

http://www.cpearson.com/excel/writingfunctionsinvba.aspx