0

I have built an Excel 2010 add-in for a customer, using C# and Visual Studio 2010. There's no UI requirement, just UDFs that are callable from the worksheet and from VBA. The same DLL contains an Automation add-in (for the worksheet functions) and a VSTO COM add-in (to provide VBA access to the add-in functions). It has 32-bit and 64-bit builds, and I also built a WiX installer (MSI) for it, also in 32-bit and 64-bit versions.

The sources for this were many and varied, and mostly on StackOverflow (see this for examples) but it has been working fine up to the last release I sent out, about 27-Sep-2014. Since then the only change I have made was to switch from using a pfx-based code signing file to a .snk-based strong name key (for reasons I won't go into because they are peripheral to the issue and anyway I have reverted back to using PFX code signing now). The add-in uses other class library DLLs that are part of the VS2010 solution and are shipped in the installer. The WiX project has not changed in any way.

Recently I built and sent out another release because of minor changes to one of the included DLLs. The customer (using Excel 2010 32-bit) reported that after installing this, Excel can no longer see the UDF names in the Insert Function Wizard, and if they are typed into a cell they just evaluate to #NAME?. If they revert to the previous installer I sent them, that still works.

I tried the new 64-bit installer on my own development machine (Excel 2010 64-bit) and see the same effect. However if I then rebuild the solution in VS2010 on this same machine, Excel can see the names again. (Whether Excel is run from within or outside Visual Studio). Uninstall/reinstall via the installer (or repair) - names gone again. I have tried doing images of the Registry before and after the VS2010 rebuild, but all that seems to change is the location of files from the installed directory to the build directory. The DLL files themselves (and all the GUIDs, TLB etc) are all exactly the same. The UDFs can still be called from VBA via the COM add-in.

I can't post the code as there is too much, so my next step is to build a minimal add-in and installer using the same principles but with only one UDF and no extra DLLs, to see if this issue is reproducible. If it also has the problem I'll post it here. In the meantime can anyone provide a clue as to what could be causing this? What mechanism does Excel use to gain access to the function names in a loaded automation add-in?

Community
  • 1
  • 1
Jeremy Tinkler
  • 75
  • 7
  • Shot in the dark; Resource file not embedded/compiled/delivered; Just not sure if .Net resources are used for a COM interop scenario. – Marvin Smit Oct 31 '14 at 22:14
  • Does not answer your question but I would recommend converting to use XLDNA which registers UDFs as XLLs rather than automation: it is more reliable and has better performance. – Charles Williams Nov 01 '14 at 08:36
  • how did you expose your classes? Via interfaces? It's worth checking that the interface attributes haven't been changed. You can view your .dll/.tlb in OLEView and compare the old and newer version. Also, check in VBE Object Browser that your libraries are visible to VBA –  Nov 03 '14 at 10:48
  • @vba4all: you might be on to something. I do use an interface and as I checked the code to paste in here, I found the following: – Jeremy Tinkler Nov 03 '14 at 21:50
  • `/// ` `/// DO NOT USE ComInterfaceType.InterfaceIsDispatch. For some reason` `/// this blocks some of the functions from appearing in the insert function helper.` `/// ` `[ComVisible(true)]` `[Guid("3744BAFD-6689-47FB-8C6B-FBF13CD658BB")]` `[InterfaceType(ComInterfaceType.InterfaceIsDual)]` `public partial interface IFISFunctions` `{` `}` If InterfaceIsDispatch has this effect maybe InterfaceIsDual can also? (it used to work though). What should I use here? – Jeremy Tinkler Nov 03 '14 at 21:56
  • @JeremyTinkler hm... try all three from [ComInterfaceType Enumeration](http://msdn.microsoft.com/en-us/library/vstudio/tz0x1301(v=vs.100).aspx)... Make sure the methods/functions you want to appear in Excel are defined in that interface and not just in a class definition –  Nov 04 '14 at 08:05

0 Answers0