none
Reference to User Defined Function in an external xlam file broken (#name?) after upgrade to Office 2016 RRS feed

  • Question

  • I have some workbooks which use UDF's in a common xlam file (in the same folder as the workbooks on a server share). I have added the xlam file as an Excel Add-In (Developer->excel Add-Ins) and referred to the functions just with the function name in each cell using them...

       e.g. =CurrentContractParam(...)

    This has worked for at least a year under Office 2013 (MS 365). But I after upgrading to Office 2016 all the links to these functions are broken (#NAME?) and the reference to the function in each cell is showing the full path in the detail window...

    e.g. ='G:\Documents\Salaries\2016\Profit Centre\ProfitCentreTools2016.xlam'!CurrentContractParam(...)

    If I open the source (xlam) file before opening the workbook the problem does not arise. OR If I delete the path in each cell then the formula calculates correctly again.

    Because of the number of users and workbooks I want to understand this and solve it centrally, not have to manually (or with a macro) remove the paths in all cells in all workbooks using the slam library functions. 

    - why does this problem suddenly surface upgrading to Office 2016?

    - is there a sensible workaround?

    Thursday, July 7, 2016 5:13 PM

Answers

  • Hi CHSteve,

    You had asked,"can you open the xlam file before executing UDF".

    Yes, you need to add code in Workbook_Open event in UDF Excel file in "ThisWorkbook" module.

    in that event you have to check that the Specified Excel File is open or not.

    If it is already open you can skip the steps to open file and can execute UDF.

    If file is not open then open it programmatically and then execute UDF.

    We are using Workbook_open event so when you open the workbook that contain the UDF at that time this code will get executed. so you always get the Xlam file open when you try to use UDF.

    please visit the link below to get information regarding Workbook.open() method and Workbook_open Event.

    Workbooks.Open Method (Excel)

    Workbook.Open Event (Excel)

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, August 4, 2016 2:15 AM
    Moderator

All replies

  • Hi CHSteve,

    I need to check you UDF at our side.

    can you able to share the code of that.

    I think the xlam file is created in office 2013 version.

    maybe it is possible that some functionalities are get changed between office 2013 and office 2016.

    but generally it should work without any problem.

    without checking your UDF at this stage I can only recommend you to copy the code from that file and create a new xlam file in office 2016 and try to test whether you get same output or something different.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 8, 2016 3:35 AM
    Moderator
  • Hi Deepak

    I now discovered that we have the same problem with another desktop with Excel 2013 still running, so it is presumably not directly to do with the update of the first desktop to 2016, nor any UDF code incompatibilty with 2016. I have checked that all macros are enabled under the trust centre and that the network share is trusted. 

    I also tried removing all references to the UDF file in the registry and then re-adding the file under the Excel Add-ins manager. Still no joy. Same problem - I must open the xlam file before opening any workbook referencing its UDF's.

    Most desktops still have no problem - only one that I updated to 2016 and one still on excel 2013.

    Can I force the xlam to be loaded early enough for the workbook to see it open when resolving its UDF references?

    ThNks for your help

    Wednesday, August 3, 2016 2:06 PM
  • Hi CHSteve,

    You had asked,"can you open the xlam file before executing UDF".

    Yes, you need to add code in Workbook_Open event in UDF Excel file in "ThisWorkbook" module.

    in that event you have to check that the Specified Excel File is open or not.

    If it is already open you can skip the steps to open file and can execute UDF.

    If file is not open then open it programmatically and then execute UDF.

    We are using Workbook_open event so when you open the workbook that contain the UDF at that time this code will get executed. so you always get the Xlam file open when you try to use UDF.

    please visit the link below to get information regarding Workbook.open() method and Workbook_open Event.

    Workbooks.Open Method (Excel)

    Workbook.Open Event (Excel)

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, August 4, 2016 2:15 AM
    Moderator