none
Excel added class name to my UDFs, those formulas are not working when I coded those same UDFs using XLL RRS feed

  • Question

  • We have been using UDFs built with Excel automation add-in for many years. Recently, I coded those UDFs using XLL with the same name. When I opened workbooks saved before, all the formula inside had the class name of the Excel automation add-in in front of the UDFs, like: "=assemblyname.classname.myFunc()". Those UDFs can't be found until I replace all them by removing the classname in front of the formula. This is an issue for any user who will use my XLL UDFs. Is there any way to let Excel search for myFunc instead of assemblyname.classname.myFunc? Where does excel save the classname in the formula even though it doesn't show up in a cell normally? Thanks a lot for any suggestions.    


    Thursday, December 7, 2017 11:18 PM

Answers

  • Hi LookForAnswer,

    As soon as a file which uses the UDF is opened on a system that uses a different addin path, all cells that use your UDF show #NAME! errors. Also, the complete path to the original location of the add-in is shown in each formula.

    below are some work around for this issue.

    Use fixed location

    Of course the simplest way to avoid the problem is by fixing the location of your addin. Tell all your users where the addin should be installed (or even better: create a setup tool that doesn't allow it to be installed elsewhere). Your #Name! errors will not resurface.

    Don't use an addin

    Well, not exactly so, you could still have an addin. But instead of keeping your UDF code inside the addin, you create a facility that copies the UDF routine into each workbook that uses it.

    This is a neat solution, but it requires that your user has the security option "Trust Access to Visual Basic Project" set.

    Redirect the UDFs to the new location

    This is the technique I'll describe extensively in this article. The addin checks each workbook the user opens whether this new workbook contains a link to the addin. If so, it ensures the link points to the proper location.

    Reference:

    Fixing Links To UDFs in Addins

    Methods to Fix #Name! Errors

    Creating a Reference to PERSONAL.XLSB for User Defined Functions (UDF’s)

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by LookForAnswer Friday, December 8, 2017 1:55 PM
    Friday, December 8, 2017 2:36 AM
    Moderator

All replies

  • Hi LookForAnswer,

    As soon as a file which uses the UDF is opened on a system that uses a different addin path, all cells that use your UDF show #NAME! errors. Also, the complete path to the original location of the add-in is shown in each formula.

    below are some work around for this issue.

    Use fixed location

    Of course the simplest way to avoid the problem is by fixing the location of your addin. Tell all your users where the addin should be installed (or even better: create a setup tool that doesn't allow it to be installed elsewhere). Your #Name! errors will not resurface.

    Don't use an addin

    Well, not exactly so, you could still have an addin. But instead of keeping your UDF code inside the addin, you create a facility that copies the UDF routine into each workbook that uses it.

    This is a neat solution, but it requires that your user has the security option "Trust Access to Visual Basic Project" set.

    Redirect the UDFs to the new location

    This is the technique I'll describe extensively in this article. The addin checks each workbook the user opens whether this new workbook contains a link to the addin. If so, it ensures the link points to the proper location.

    Reference:

    Fixing Links To UDFs in Addins

    Methods to Fix #Name! Errors

    Creating a Reference to PERSONAL.XLSB for User Defined Functions (UDF’s)

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by LookForAnswer Friday, December 8, 2017 1:55 PM
    Friday, December 8, 2017 2:36 AM
    Moderator
  • Thanks for the links. I like the 3rd approach.
    Friday, December 8, 2017 1:56 PM