Excel-DNA = .Net and Excel integration
In order to overcome limitations due to the lack of development of the VBA language, relaying on XLL seems a right option to manipulate Excel environment via more actual languages, like C++ or the .Net flavours.
From all the solutions one can opt to deal with this XLL thing, Excel-DNA is the way to go.
Excel-DNA (Excel Dot Net Assembly) lets integrate .Net and Excel, making possible to create native .xll add-ins for Excel (using .Net programming languages, via Visual Studio), which leverages high-performance in user-defined functions (UDFs) and Excel manipulation from .Net coded applications. It also allows to customize ribbon interfaces. The add-in can be packed into a single .xll file -requiring no installation or registration, only linking from the Excel COM tab manager-.
Other alternatives to this .Net-Excel integration would be, on the open source side: XWL project, KAS XLL -old JetXLL-, and on the commercial side: Add-in-Express or XLL+ from Planatech; although none of them seem to get as evolved and reliable. On the other hand they seem to be better at C++ than Excel-DNA.
Also, one could go and try Microsoft solutions on this field: Excel Development kit or even Visual Studio Tools for Office (VSTO).
Keeping us in Excel-DNA, the current version is 1.1 (released on 29 June 2020) and is expected to be the final version with support for older .NET (< 4.5) and Excel (< 2007) releases. There are some newer pre-release versions that target .NET 6.
The official wiki is not very straight forward for VBA coders, and best resource to start there is this one -in C#- and not visually appealing; and the Samples page have an index I could barelly understand the purpose they each were coded for (need more explanation), as they seem targeted to devs purposes. So I better recommend to follow this youtube introduction https://www.youtube.com/watch?v=z3cbAh7CzMo from the very recommendable VBA A2Z channel. Either way, download this file of mine -where I’ll put all my code findings- as there were some parts of the edited youtube video that hid critical steps.
Andy Sprague has some short trials on how he started that worth take a look for first steps.
If you want to go further, then next manual to follow will be this Patrick O’Beirne PDF, referenced on this post. If not 100% related, he has an application that should be revised with this article, describing a transpiler.
Another good material could be found on these series of 2010/02 posts on Simon Murphy‘s blog.
There is a official “forum” where you can ask for help in Google Groups (and also StackOverflow). This post was a continuation of this thread discussion, were I could not attach my Excel-DNA project to get more help, but now stays as a helper remainder for anyone interested on Excel-DNA.
Will post most related code here (in the project file to download) in the future, as more trials get done and gain some confidence with Excel-DNAOne of the first codes I should take a look is this one on StackOverflow, related to easing the manipulation of several cells once at a time.
Another set of posts I would enjoy are these related to Excel-DNA posts. The one that grabbed my attention is here detailing how to create a XLL for a very basic function, directly from script… easy-peasy, and it continues explaining the process for a more complex code (more than one function).
And last for today, these are some very promissing posts to overcome the DLL manage limitations on Excel, via a C++ wrapper. I’m thinking on OpenCV right now:
- https://uglyduckling.nl/blog/financial-engineering/levmar-excel-an-introduction/
- https://uglyduckling.nl/blog/financial-engineering/wrapping-c-dll-for-use-in-excel-vba-using-some-pretty-awesome-open-source-projects/
- https://uglyduckling.nl/blog/financial-engineering/compiling-levmarsharp-visual-studio-2010/
- https://uglyduckling.nl/blog/financial-engineering/compiling-levmar-using-nmake-visual-studio-2010/