Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving a UDF from a Workbook to an Add-In (xl97, Win2000)

    I have a UDF that has been residing in a workbook and now I want to move it to an add-in. When I delete the UDF from the workbook, I get a NAME error when I try to use the function. It appears to be looking for the function in the workbook instead of the add-in. Is there a fix for this?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving a UDF from a Workbook to an Add-In (xl97, Win2000)

    how do you open the addin? (you should add it to the addins list (tools, addins) and check it there so it is installed. Then the functions should work. It might be that you have to check them though, if there is a workbook name in front of the functions name, you'll have to remove that.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving a UDF from a Workbook to an Add-In (xl97, Win2000)

    The add-in is installed as you described via ToolsAdd-ins and checking the box. If I change the function name in the workbook and the add-in, Excel executes the function correctly.

    I've always noticed that with UDFs, the case of the UDF name (i.e. which letters are capitalized in the name) is determined by your first usage of the UDF in your workbook. This makes me believe that Excel is building a table of UDF names.

    In my current situation, the function name continues to revert to all lower case because that's how it was originally used in the workbook, even though I've deleted the UDF from the workbook, opened the workbook, deleted references to the function, removed the add-in, saved the workbook, and re-installed the add-in all in an attempt to delete this entry from this presumed UDF name table.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving a UDF from a Workbook to an Add-In (xl97, Win2000)

    You might try copying all sheets from the workbook to a new workbook (copy them all in one go to avoid links).
    Make sure there are no cells with more than 255 characters in them, those may get truncated and you'll need to copy the cells instead of the entire worksheet.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Moving a UDF from a Workbook to an Add-In (xl9

    Hi,
    I was actually doing something similar the other day as I had moved a few functions into a dll and I found that I had to remove the function from the workbook, remove all instances of the function from cells in the workbook, save it, close excel and restart, then re-enter the new function. A bit of a pain but it did work.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving a UDF from a Workbook to an Add-In (xl9

    I found out that some prior references to the UDF were not removed from the workbook. Once I cleaned all of those up, the add-in worked perfectly. Thanks everyone for your help.

Posting Permissions

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