Using a user-defined function from the Personal workbook

  • Thread starter Thread starter mworth01
  • Start date Start date
M

mworth01

I have created a UDF (named Interpolate) to interpolate within a user
selected table. The function works great when the function is created
in a module within the workbook that I call it. Since this seems to be
a handy function to use in the future, I tried to put it in my Personal
workbook so that I can access it in any workbook that I may be working
in. After doing this, I get the error "#NAME" when I try to use it in
another workbook. I declared the function as Public, even though I
think it does that by default. The function requires 3 inputs from the
user (Target, TableRng and ResultCol) and all other variables used to
define Interpolate are internal to the function (and declared with
Dim).

I'm assuming that I'm missing a statement which will allow this formula
to be used within any workbook. This seems like it should be a basic
question but searching this forum hasn't turned up anything yet. If
you have any advice I'd appreciate it. Thanks.
 
I have the same problem. My solution was to use an add-in

Die_Another_Day
 
Precede it with Personal.xls!, such as

=Personal.xls!MyFunc(MyParam)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Thanks Bob. While that does obviously work, it seems a bit cumbersome
for the end user wishing to use that function. I assume that if you
knew of any other way of accomplishing the same task that you would
have mentioned it?

Also, is there a way to show the function tooltips of a UDF like the
standard Excel functions? For example, when you type =SUM( then a
yellow box appears showing you what you need to enter to make the SUM
function work - in this case it would be SUM(number1, [number2], ...).
I know that you can turn that feature off in Options, but is there a way
to make it appear for a UDF?

Thanks again.
 
Take a look here:

http://www.jkp-ads.com/Articles/DistributeMacro02.htm

Tooltips: No. But you could try and make help available in the function wizard:

http://www.jkp-ads.com/Articles/RegisterUDF00.htm

No easy stuff, though!

--
Kind regards,

Niek Otten

|
| Thanks Bob. While that does obviously work, it seems a bit cumbersome
| for the end user wishing to use that function. I assume that if you
| knew of any other way of accomplishing the same task that you would
| have mentioned it?
|
| Also, is there a way to show the function tooltips of a UDF like the
| standard Excel functions? For example, when you type =SUM( then a
| yellow box appears showing you what you need to enter to make the SUM
| function work - in this case it would be SUM(number1, [number2], ...).
| I know that you can turn that feature off in Options, but is there a way
| to make it appear for a UDF?
|
| Thanks again.
|
|
| --
| mworth01
| ------------------------------------------------------------------------
| mworth01's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8991
| View this thread: http://www.excelforum.com/showthread.php?threadid=538657
|
 
Only other thing I can think of is to put the functions in an add-in.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

User Defined Function being called by changes to an independent workbook 13
User Defined Function - actioned in unrelated WB 5
Application.Caller calls from ActiveWorkbook not the actual cell workbook 1
COM addin won't connect when workbook opened via another workbookfrom another instance 3
UDT in an Add-in 1
Multiple User Defined Categories in Insert Function Form 2
Referencing Defined Name in another workbook 2
Where is User Defined FUnction 3

Back
Top