Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    San Francisco, California, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    #NAME? when function moved from module to add-in (Excel 2000 VBA)

    I wrote a user-defined function in a VBA module and created formulae in the spreadsheet using the function. Since it was generally useful, I put the function in an xla. Now I want to remove the VBA module so that the formulae in the spreadsheet use the function in the add-in. Although the function appears in the Paste Function and Edit Function dialog boxes (I can tell it's the new one because the arguments have different names), I get #NAME? errors when I hit Enter, whether the formula existed in the spreadsheet before or I create a new one.

    There seems to be an internal inconsistency in Excel in that it recognizes the function in the add-in when listing user-defined functions but doesn't when it calculates. I'm guessing there's some reference to the removed module somewhere that is generating the #NAME? error, but I can't find it anywhere.

    Has anyone had this problem and found a solution? Thanks.

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

    Re: #NAME? when function moved from module to add-in (Excel 2000 VBA)

    How is the addin loaded, have you installed it using tools, addins or is it in the XLSTART directory? (should be the former, not the latter)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #NAME? when function moved from module to add-

    > I put the function in an xla

    I'm afraid that I don't quite understand what you did.

    1) I build Public Functions in a User Module in an execl workbook (XLS) and can use those functions as formulae in spreadsheet cells.
    <pre>Public Function str20040910() As String
    str20040910 = "hello"
    End Function</pre>

    2) I can save the workbook as an XLA.
    3) In EXCEL I choose Tools, Addins, and Browse to the XLA file.
    4) I can now use =str20040919 as a cell forumla to generate "hello" in a cell.

    Are you following this route?

  4. #4
    New Lounger
    Join Date
    Apr 2004
    Location
    San Francisco, California, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #NAME? when function moved from module to add-in (Excel 2000 VBA)

    To ensure that people using the xla always get the latest version, I'm actually opening it from another xla that is in the XLSTART directory.

    I tried installing it using Tools >> Add-ins, but I'm still getting the #NAME? errors.

    Eric

  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: #NAME? when function moved from module to add-

    Hi Eric,
    When I did something similar a while ago (actually moved from workbook to dll rather than xla but I suspect the same principle applies) I found I had to delete all formulae that used the functions, save close and reopen the workbook and then reinput the formulae at which point everything worked fine. Ours not to reason why....
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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