Our great sponsors
-
ExcelDna
Excel-DNA - Free and easy .NET for Excel. This repository contains the core Excel-DNA library.
-
-
InfluxDB
Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.
-
I can try to give you an overview, but caveat emptor that I'm no expert.
The two historical programmatic interfaces to Office (that I'm familiar with):
VBA[1] - They have a pretty extensive VBA object model for the various Office programs, and is used for macros (either by auto-generated code when you "Record Macro" or manually developed code). The code lives within the Office file and follows it around, allowing it to be fairly portable since it doesn't require anything external to the file (unless you're calling out externally or relying on things like ODBC drivers).
You can also package up VBA into add-ins as well.
COM - There's an interopp assembly[2] that exposes a COM-based object model. This is the object model being used here (via Excel-DNA[3]), and the one hooked into by Visual Studio Tools for Office[4].
The biggest issue with both of these is cross-platform support – historically add-ins have been Windows only. COM isn't supported anywhere else, and VBA is only supported on Mac (not mobile). And the Mac VBA model isn't nearly as robust as the Windows version and has several caveats[5].
Enter Javascript, which is a relatively new option and what they're pushing hard get people onto. They've started developing an object model to expose the various Office apps via Javascript[6]. When you develop an add-in using this[7], the add-in is actually a webapp running in a sandboxed browser, with transparently handled interop between the add-in sandbox and the Office app. So all of the actual add-in development is HTML, CSS, and Javascript based.
The primary benefit of the Javascript option is that they're portable between the various platforms ([7] mentions Windows, Mac, Web-based, and the iPad. Not sure about Android/iPhone). But you get a lot of secondary benefits as well, such as the ability to have your add-in resources loaded from a remote website, ensuring things like the user always loading the latest version.
They've also slowly been making inroads in expanding the usage of Javascript - Office Scripts[8] is a preview feature they have right now, that effectively allows you to record macros in Excel for the web, which generate Javascript-based code rather than VBA code. You can also create user defined functions[9] in Javascript that can be mixed with normal functions within cells.
I (unfortunately) have to use Excel for many end-user deliverables, and have tinkered around with the Javascript options to make a few quality-of-life utilities for myself and others on my team. It's super handy for plugging into online services, and for being portable. I have one or two utilities for external teams that they access via Excel Online but I don't give them the file, and for others on the team we have several in circulation the Javascript-based stuff all "just works" in these contexts.
A major caveat of the Javascript stuff is that it all runs in sandboxed contexts and can primarily only interface between the Office host application and external services. COM and VBA-based add-ins don't have this constraint, and interact with other local resources beyond the host application itself.
On the plus side for the JavaScript option – in addition to being cross-platform, they're also not nearly as locked down in corporate environments because of their reduced security threat. But that limitation also means that it's unusable for solutions like this D in Excel add-in, which couldn't execute in that browser context and require the COM interop capabilities.
[1] https://docs.microsoft.com/en-us/office/vba/library-referenc...
[2] https://docs.microsoft.com/en-us/visualstudio/vsto/office-pr...
[3] https://excel-dna.net/
[4] https://docs.microsoft.com/en-us/visualstudio/vsto/create-vs...
[5] https://docs.microsoft.com/en-us/office/vba/api/overview/off...
[6] https://docs.microsoft.com/en-us/javascript/api/overview?vie...
[7] https://docs.microsoft.com/en-us/office/dev/add-ins/excel/ex...
[8] https://docs.microsoft.com/en-us/office/dev/scripts/resource...
[8] https://docs.microsoft.com/en-us/office/dev/add-ins/excel/ex...
[9] https://docs.microsoft.com/en-us/sharepoint/dev/general-deve...
As someone who has had this conversation when trying to sell people on D a few times¹ it seems like an odd way to respond to it. When the licence text contained "the Software is copyrighted and comes with a single user license, and may not be redistributed"² then it hardly meets the expectations people have of Open Source. This must end up in the weeds on source available vs Open Source more often than it shuts it down?
[And yes, I realise this comment is a perfect example of those weeds.]
¹ Although, I appreciate far less than either you or especially WalterBright have.
² https://github.com/dlang/dmd/pull/6680
Welcome to my world: https://github.com/xlladdins/xll
Need to call a singular value decomposition routine from a LAPACK Fortran library? No problem: https://github.com/keithalewis/xlllapack/blob/master/syev.cp...