Remove AddIn path from UDF in Excel formula

Question

My addin was xla, now I use excelDNA, so it becomes xll, When I open spreadsheet built in previous version of My addin, for the UDF, it shows myUDF with path of xla. e.g "C:\Program Files\Installation folder\MyUDFs.xla!MyUDF", when I click Edit link and change source to "C:...\MyUDFs.xll" I got a pop up which says "Excel cannot update one or more links in this workbook. To update the links, open all the link source files(click Edit Links on the Data tab). To be sure all calculations are updated. press F9" I click OK, then the path of MyUDF changes from xla to xll, e.g. C:\Program Files\Installation folder\MyUDFs.xll!MyUDF For clients, this will break all their spreadsheets (could be 100+) built in previous version. I know I can write a VBA code to remove paths from all MyUDF. but it is not ideal since users have to open up spreadsheet and put the code in spreadsheet and run.

I wonder if there is a better/more convenient way for clients to solve the issue thanks

Immorality

Found a workaround. I keep old xla file which has VBA code to register MyUDF. in the new xll, I expose the same function with same sigature. When users open spreadsheet built from previous Addin (if the installation folder not changed), there's no issue. i.e. MyUDF shows as MyUDF, no path in front. I also tried this, I changed C# code to register MyUDF to return different value from MyUDF in xla, and helpID point to different page from xla, looks like the version in C# got precedence over xla. I don't know if there's any issue with the approach. – Immorality

See this question for an answer using a VBA-Reference. – Sheltonshelty

Answer 1

Internally, Excel stores different information for an .xla function and an .xll function. It's not so easy to around so that you can make an .xll that is compatible with functions that were entered into the sheet as functions in an .xla.

You can also see how Excel stores this information by poking around inside the .xmlx file a bit.

This Wilmott discussion might be relevant: http://www.wilmott.com/messageview.cfm?catid=10&threadid=79763 For your case the best I can suggest is adding a conversion macro to your .xll, and having the user press the 'Fix-up' button when they open spreadsheets that have not been converted yet.

Comprehension

thank you very much, Govert. How to add a conversion macro in my xll? Can you give a simple example? I just found there is one issue with workaround I found, the same MyUDF are registered twice in Excel – Immorality

This is the solution I use at last. I keep my xla addin, in xll, I register the same MyUDF with the same sigature(while, almost the same, optional parameters are not supported in xll, but are used in my xla) and set IsHidden = true. So there is only one MyUDF shows up in insert function list. When you type in =MyUDF and click function wizard, the version in xll shows up in function argument window. In Excel 2007, when I type =MyUDF, there is no function in drop down. In Excel 2010, when I type =MyUDF, there is a drop down. So the solution works better in Excel 2010. – Immorality

@Immorality hi, can you please explain your solution? How does this solve the problem of having existing worksheets linked to the old xla? – Oreilly

e.g. in my xla file, I have a UDF Public Function UDF1(param1, param2) As String ... return a value End Function – Immorality

Try this. Let's say I have a UDF1 in xla file as Public Function UDF1(param1, param2) As String ... return a value End Function In my xll, I add another UDF1 with the same signature but as hidden UDF i.e. [ExcelFunction(IsHidden = true)] public static object UDF1(param1, param2) { .... return a value} I keep old xla together with my xll, so when old worksheet will work. If you do not want to keep you xla you can add a button to remove paths from UDFs, of course you still need to add same UDFs in xll as those in xla. – Immorality

@Immorality - I suggest you post your solution as an answer. – Woodbridge

See this question for an answer using a VBA-Reference. – Sheltonshelty

Answer 2

This is the solution I use at last. I keep my xla addin, in xll, I register the same MyUDF with the same sigature(while, almost the same, optional parameters are not supported in xll, but are used in my xla) and set IsHidden = true. So there is only one MyUDF shows up in insert function list. When you type in =MyUDF and click function wizard, the version in xll shows up in function argument window. In Excel 2007, when I type =MyUDF, there is no function in drop down. In Excel 2010, when I type =MyUDF, there is a drop down. So the solution works better in Excel 2010.

Immorality