4

One data provider wants to develop an Excel add-in, these are the requirements:

1) it can be installed on desktop. It adds one menu and buttons in Ribbon, and it also provides some specific functions (to get real-time data, like Bloomberg function).

2) the add-in should work in Excel 2007, 2010, 2013 and 2016.

The question is which technology should be used to create this add-in. Here are some of my thoughts:

1) JavaScript API for Excel does not fit, because it does not work for Excel 2007, 2010.

2) VSTO. Does anyone know if VSTO add-ins work for Excel 2013 and 2016?; Is it possible to build one VSTO add-in that works for all Excel 2007, 2010, 2013 and 2016?

3) C API for Excel. It seems that C API for Excel is used to build XLLs. Does anyone know if C API for Excel can build menu and buttons?

Alex
  • 4,885
  • 3
  • 19
  • 39
SoftTimur
  • 5,630
  • 38
  • 140
  • 292

1 Answers1

1

Here are some points :

  1. JavaScript API , they are web based addin, so your add-in won't work without internet but it seems that your solution is for a data provider so I assume your clients will have internet. As you said, it didn't work for pre 2013 versions. see here

  2. VSTO, probably the best option but you need to be aware of a major change related to MDI/SDI between Excel 2013 and previous version. Others small tweaks may appear (image idmso, some events..) but VSTO 2007 should be 2016 compatible. VSTO 2016 doesn't exist, the latest release is 2010 (Version 4.0 Runtime). You should test it regularly during development time to check the compatibility.

  3. It is possible to add a menu (a dropdown list) and a toolbar via the Excel C Api (using xlfAddMenu and xlfAddToolbar) but not ribbon controls that require the COM Interface IRibbonExtensibility. So the only way to add controls to the rubbon is via the COM technology (from wich VSTO is based on). The Excel C API didn't change since the 2007 version so it will be fully compatible for all versions. You can find a working example in the Excel C API 2010 SDK (not in the 2013 SDK -this part has been removed), in the file GENERIC.C , see the xlAutoOpen function. Also please note that it is very difficult to find information about it.

From my point of view, you have two possibilities: 1) implement everything via VSTO or 2) to implement your routines via the Excel C API and to interact with it via a VSTO addin that is just used for ribbon controls. I would suggest you the first solution (VSTO), you'll find more documentation about it.

EDIT :

Just realized your question is also about UDF. There are three ways to add UDFs to Excel : via VBA, via a COM automation and via the Excel C API. The best option for UDF is definitively the EXCEL C API but there exist an open source project that "wrap" this API in C#, see Excel DNA.

EDIT 2 :

As an alternative to VSTO, still in Net, there is the NetOffice project on Codeplex that it is worth trying it, they claim that they support all Excel versions. It won't help you regarding the UDFs part but for the GUI it seems to be a good candidate.

Graham
  • 7,431
  • 18
  • 59
  • 84
Malick
  • 6,252
  • 2
  • 46
  • 59
  • And If I go with Excel C API + VSTO (for the ribbon), I could use VSTO 2010 because there is no ribbon before Excel 2010? – SoftTimur Nov 08 '16 at 15:30
  • Thanks for your answer... So I go with purely VSTO, I should use VSTO 2007 (rather than 2010) to ensure the compatibility with all the versions? And VSTO 2007 allows me to create UDF that can be used in Excel 2007 as well? – SoftTimur Nov 08 '16 at 15:32
  • 2
    You should use VSTO 2010, it will work with 2007 - see https://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office but I still recommend you to test regularly your dev, there is a ribbon in excel 2007 so you'll be able to control it as in 2010-2016. Excel C API + VSTO seems to be the preferable option. Finally note that it won't work for Mac Osx because both the Excel C API + VSTO are not supported – Malick Nov 08 '16 at 15:40
  • 2
    + You can't create UDFs with VSTO – Malick Nov 08 '16 at 15:43
  • If I go with Excel C API + VSTO 2010, do I need to choose a version of XLL SDK? – SoftTimur Nov 08 '16 at 15:49
  • 2
    the XLL 2013 SDK is enough, it targets all version of excel. If you want to know more about XLL you should read the book of Steve Dalton about it (Financial Applications using Excel Add-in Development in C / C++). – Malick Nov 08 '16 at 15:54
  • Precisely in `Excel C API + VSTO 2010`, I can use `VSTO 2010` to make menu, bottons and ribbon for all the Excel versions, and use `Excel C API` only for UDFs, right? – SoftTimur Nov 08 '16 at 16:57