Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: making a user-defined function accessible to all workbooks

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've got a couple of user-defined functions that I coded in via one workbook (wkb A), but would like these functions to be accessible to any workbook (wkb B...Z). How is this done so that when I start a new blank wkb I can use these user-defined functions there as well?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Save the file with the function with an xla extension. This will tell Excel that it's an add-in workbook. Then Select Tools > Add-Ins from the menu and browse to the xla file you saved. The function will now be available to all workbooks in Excel.
    It's never too late to learn something new.

    Ricky

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the help, Ricky!

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 19:25, Ricky Morris wrote:
    Save the file with the function with an xla extension. This will tell Excel that it's an add-in workbook. Then Select Tools > Add-Ins from the menu and browse to the xla file you saved. The function will now be available to all workbooks in Excel.
    Thanks from me too Ricky!

    Yogi Anand

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can also save the functions in personal.xls
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Al:
    I have some UDFs saved in Personal Workbook but they work only in Worksheets of the Personal Workbook and not in Worksheets of other Workbooks (even though the UDF is declared as Public). Any ideas what else needs to be done?

    [ This Message was edited by: Yogi Anand on 2002-04-17 16:25 ]

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-17 16:24, Yogi Anand wrote:
    Hi Al:
    I have some UDFs saved in Personal Workbook but they work only in Worksheets of the Personal Workbook and not in Worksheets of other Workbooks (even though the UDF is declared as Public). Any ideas what else needs to be done?

    [ This Message was edited by: Yogi Anand on 2002-04-17 16:25 ]
    You have to fully qualify the call, i.e. tell Excell where to find the function.

    =[Your Workbook.xls]MyFunct(Arg1, Arg2)

    should work.

    Bye,
    Jay


  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-17 16:39, Jay Petrulis wrote:
    On 2002-04-17 16:24, Yogi Anand wrote:
    Hi Al:
    I have some UDFs saved in Personal Workbook but they work only in Worksheets of the Personal Workbook and not in Worksheets of other Workbooks (even though the UDF is declared as Public). Any ideas what else needs to be done?

    [ This Message was edited by: Yogi Anand on 2002-04-17 16:25 ]
    You have to fully qualify the call, i.e. tell Excell where to find the function.

    =[Your Workbook.xls]MyFunct(Arg1, Arg2)

    should work.

    Bye,
    Jay
    Thanks Master Jay! That might be it ... I will try that.

  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Yogi,

    If the brackets don't work, try referencing the workbook with an exclamation point.

    I know it's something like this, but have never needed it, so I have no experience with the syntax.

    Bye,
    Jay

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jay:
    It worked with the ! preceded by the name of the workbook.xls -- I did not need to enclose the name of the workbook in []s.

    THANKS!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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