is this possible?

[Bob]

Is it possible to define a custom excel function in a worksheet object
instead of a standard code module so that it is included anytime that
the worksheet is copied to a new book? I'm having a hard time calling
a function defined at the worksheet level. I have been told that I
need to use the fully-qualified function name, but after finding many
possible formats for calling such a function have not worked. Does
anybody know how to make this work?

Any help anyone can offer would be much appreciated.

 

[Pete_UK]

Have you tried putting the UDF in your Personal.xls file? Just make
sure it refers to the active workbook, otherwise it will act upon the
Personal.xls file.

Hope this helps.

Pete

 

[Bob]

I haven't tried putting the function in Personal.xls. I don't know if
this will accomplish what I'm trying to do though. I'm trying to run
the function from the worksheet object so that the worksheet can be
copied to a new workbook and the function will still work without
throwing a #NAME? error. The new workbook could then be sent to
others and still contain good information, regardless of the PC that
it is viewed on.

My guess is that putting the function in Personal.xls will solve my
problem on my PC, but if I send the copied worksheet to someone else,
the #NAME? error will appear...but I'll give it a shot.

Thanks for the suggestion Pete.

Bob

 

[Don Guillett Excel MVP]

Is it possible to define a custom excel function in a worksheet object
instead of a standard code module so that it is included anytime that
the worksheet is copied to a new book?  I'm having a hard time calling
a function defined at the worksheet level.  I have been told that I
need to use the fully-qualified function name, but after finding many
possible formats for calling such a function have not worked.  Does
anybody know how to make this work?

Any help anyone can offer would be much appreciated.

AFAIK this is not possible. If you give us full info or send me your
file, perhaps it is possible to design a worksheet change event to do
the same thing. Just a thought.

 

[Pete_UK]

You might also think about putting the code in an Add-in: this
tutorial describes how quite well:

http://www.fontstuff.com/vba/vbatut03.htm

Note the penultimate sentence in the article - if you want to send the
file to someone else you will also have to send them the add-in.

Maybe Don will be able to come up with something...

Pete