+ Reply to Thread
Results 1 to 8 of 8

Using a user-defined function from the Personal workbook

  1. #1
    Registered User
    Join Date
    04-30-2004
    Posts
    15

    Using a user-defined function from the Personal workbook

    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.

  2. #2
    Die_Another_Day
    Guest

    Re: Using a user-defined function from the Personal workbook

    I have the same problem. My solution was to use an add-in

    Die_Another_Day


  3. #3
    Bob Phillips
    Guest

    Re: Using a user-defined function from the Personal workbook

    Precede it with Personal.xls!, such as

    =Personal.xls!MyFunc(MyParam)

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    "mworth01" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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.
    >
    >
    > --
    > mworth01
    > ------------------------------------------------------------------------
    > mworth01's Profile:

    http://www.excelforum.com/member.php...fo&userid=8991
    > View this thread: http://www.excelforum.com/showthread...hreadid=538657
    >




  4. #4
    Die_Another_Day
    Guest

    Re: Using a user-defined function from the Personal workbook

    Cool, thanks

    Die_Another_Day


  5. #5
    Die_Another_Day
    Guest

    Re: Using a user-defined function from the Personal workbook

    Cool, thanks

    Die_Another_Day


  6. #6
    Registered User
    Join Date
    04-30-2004
    Posts
    15
    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.

  7. #7
    Niek Otten
    Guest

    Re: Using a user-defined function from the Personal workbook

    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

    "mworth01" <[email protected]> wrote in message
    news:[email protected]...
    |
    | 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...fo&userid=8991
    | View this thread: http://www.excelforum.com/showthread...hreadid=538657
    |



  8. #8
    Bob Phillips
    Guest

    Re: Using a user-defined function from the Personal workbook

    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)

    "mworth01" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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...fo&userid=8991
    > View this thread: http://www.excelforum.com/showthread...hreadid=538657
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1