Creating an Excel Add-In in D(blog.jonharrington.org)
blog.jonharrington.org
Creating an Excel Add-In in D
https://blog.jonharrington.org/d_excel_addin/
27 comments
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
[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
> it seems like an odd way to respond to it
A better way to clarify it would be:
There are three D compilers available. Two of them have always been fully open source. Parts of one were originally source available rather than open source, but that one is now 100% open source as well. The website failed to communicate this information properly at the time the author first tried D.
The D website had, shall we say, uneven quality of communication back in the old days.
A better way to clarify it would be:
There are three D compilers available. Two of them have always been fully open source. Parts of one were originally source available rather than open source, but that one is now 100% open source as well. The website failed to communicate this information properly at the time the author first tried D.
The D website had, shall we say, uneven quality of communication back in the old days.
I've leaned on simply "yeah, but long since solved" when lunchtime chats were still a thing. It was the fastest way to shut down that branch in my experience.
However, I do like your full and complete answer. It acknowledges the old situation and completely nullifies it. I'll steal your second paragraph for when coffee time returns to normal ;)
However, I do like your full and complete answer. It acknowledges the old situation and completely nullifies it. I'll steal your second paragraph for when coffee time returns to normal ;)
> this was never really true [...] the code has always been available
The term the author used ("open source") has a specific meaning. Merely being able to see the source code is not sufficient.
The term the author used ("open source") has a specific meaning. Merely being able to see the source code is not sufficient.
That's is true, but as I said the fully open source compilers are good, so even if the code was not legally open source de jure - you could still audit it, is what I meant.
[deleted]
Picking a compiler backend is confusing as a new user. I remembering having questions like: is dmd going to produce a slower binary? Are gdc and ldc up to date with dmd?
Is it confusing? Presumably most C++ programmers have an intuition at very least between gcc and clang?
Given my role at the foundation I need more data on this so I'm not being facetious
Given my role at the foundation I need more data on this so I'm not being facetious
Maybe I'm the wrong target audience then. I'm a Java developer and I only dabble in writing some C with GCC or MSVC depending on my OS.
The compiler page has a lot more details than I remember [0]. It says: For beginners you should use dmd, but the next sentence says that LDC/GDC produce substantially faster binaries. So why would I ever choose dmd? What are the trade offs? Compile time? What is substantial? And then when you scroll down it doesn't say what version of the D language spec is supported by each compiler, imagine getting started with LDC and then it only supports D 2.02, when dmd is up to D 2.12 or something.
I guess for me there were a lot of questions to getting started.
[0] https://wiki.dlang.org/Compilers
The compiler page has a lot more details than I remember [0]. It says: For beginners you should use dmd, but the next sentence says that LDC/GDC produce substantially faster binaries. So why would I ever choose dmd? What are the trade offs? Compile time? What is substantial? And then when you scroll down it doesn't say what version of the D language spec is supported by each compiler, imagine getting started with LDC and then it only supports D 2.02, when dmd is up to D 2.12 or something.
I guess for me there were a lot of questions to getting started.
[0] https://wiki.dlang.org/Compilers
When I discovered D (pre 1.0) the compiler front end was open source, but the back end source was closed. I was surprised years later when I found it now more open.
There was a compiler called DLI that even used the front end code and wrote a new backend that used nasm. I remember porting that to BeOS because none of the other opensource back ends were easily portable at the time.
There was a compiler called DLI that even used the front end code and wrote a new backend that used nasm. I remember porting that to BeOS because none of the other opensource back ends were easily portable at the time.
It is wholly open-source now.
I’m a bit confused by the suite of API’s around Office ATM.
So my understanding is there’s a COM APIfor automating Office applications that seems to launch a headless version of the application, some sort of newer JavaScript API for interacting with Office365 applications, and if I recall correctly, some sort of COM API for actually extending the application which I assume is what the D-wrapper in this article is using.
So my understanding is there’s a COM APIfor automating Office applications that seems to launch a headless version of the application, some sort of newer JavaScript API for interacting with Office365 applications, and if I recall correctly, some sort of COM API for actually extending the application which I assume is what the D-wrapper in this article is using.
I think this is about creating an Excel XLL: "The primary reason for writing Microsoft Excel XLLs and using the C API is to create high-performance worksheet functions.[0]" If I'm not mistaken, this API predates COM Automation and COM add-ins.
[0] https://docs.microsoft.com/en-us/office/client-developer/exc...
[0] https://docs.microsoft.com/en-us/office/client-developer/exc...
Yeah, it's practically archaeology now! It is very fast for doing some things, though.
If anyone's interested in it from a Dot Net perspective, ExcelDNA [0] was excellent when I used it a few years ago.
[0] https://excel-dna.net/
If anyone's interested in it from a Dot Net perspective, ExcelDNA [0] was excellent when I used it a few years ago.
[0] https://excel-dna.net/
COM isn't for headless, it is for interacting with the application programatically. VBA communicates with Office via COM. You can interact from within an excel session (for instance through VBA or an Excel-DNA addin) or from outside of an excel session (by launching an excel session from your script). It's not headless in the sense that it will open a normal excel sessions with the UI (and potential for modal dialog boxes blocking your code).
COM however does not allow you to create your own user defined functions in excel, so for that you need to create an xll (for instance via Excel-DNA).
I am less familiar with the javascript API but I understand it is newer, and meant to create components for office 365, it does not offer the full access to the office API.
COM however does not allow you to create your own user defined functions in excel, so for that you need to create an xll (for instance via Excel-DNA).
I am less familiar with the javascript API but I understand it is newer, and meant to create components for office 365, it does not offer the full access to the office API.
I can try to give you an overview, but caveat emptor that I'm no expert and could very well be butchering some of the finer points below.
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...
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...
Welcome to my world: https://github.com/xlladdins/xll
One benefit of the C API is that it has the FP data type - a two dimensional array of doubles. All other API's copy data around while this hands you a pointer to the array.
Need to call a singular value decomposition routine from a LAPACK Fortran library? No problem: https://github.com/keithalewis/xlllapack/blob/master/syev.cp...
That assumes you're in a position to copy around DLLs and install stuff.
...true for a programmer. Office worker using primarily Excel who the IT team is trying to protect against threats...not so much.
I can't even change the wallpaper...
...true for a programmer. Office worker using primarily Excel who the IT team is trying to protect against threats...not so much.
I can't even change the wallpaper...
Believe it or not, even custom wallpapers can cause issues now and again, such as this recent example of one that bricked several Android devices:
https://9to5google.com/2020/06/10/android-phone-wallpaper-so...
Not saying it’s likely, just saying you never really know where a technical or security issue may come from.
https://9to5google.com/2020/06/10/android-phone-wallpaper-so...
Not saying it’s likely, just saying you never really know where a technical or security issue may come from.
For the record, this was never really true. The backend of dmd (the "main" D compiler) was unfortunately not under the same licence as the rest of the compiler, but the code has always been available. The GNU and LLVM backends are both fully open source and are faster anyway.