Geek Answers Handbook

Call excel worksheet function from excel cell

I have a set of custom vba functions that are in the excel module, which are then called from an Excel spreadsheet ... at this point everything works fine.

I was asked to move vba from the module to the worksheet code page. When I did this, I found that I could not call any functions from the cells in the worksheet ... the names simply did not appear 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 function in another module or worksheet?

EDIT:

I found if I call the file_name .functionname, it gives an error message that includes "The name conflicts with the name of the embedded Excel or the name of another object in the book" ... where, if I use the file_name. Most of all, it simply resolves #NAME?

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

+6
excel-vba module worksheet-function worksheet
source share
2 answers

Not. Functions in a worksheet object cannot be called from a worksheet as user-defined functions.

The Worksheet object is designed to respond to events that occur on a worksheet. You cannot place user-defined functions in it. Custom functions must live in the module.

If your custom function really works in the module, you will not have problems with calling it from code elsewhere ... including on the "code-behind" worksheet.

+7
source share

You must put the code in a standard module. Check out this link.

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

+4
source share

More articles:


All Articles