In 2018 I started working with the Excel-DNA library. As the Excel-DNA homepage mentions:
Excel-DNA is an independent project that integrates .NET into Microsoft Excel to extend its native capabilities. Using C#, Visual Basic.NET or F#, it is possible to create a standalone add-in file (.xll) with high-performance user-defined functions (UDFs), custom ribbon interfaces, and much more!
DNA is in effect a wink😉 to VBA; where VBA stands for Visual Basic for Applications (an interpreted language), DNA stands for dot-NET for Applications, with Excel being the main target application.
The add-ins can be programmed using C#, VB.Net or F#. The code is compiled into a dynamic link library (dll) with *.xll being used as file extension, to distinguish it from ‘ordinary’ dll’s. The entire add-in can be packed into a single .xll file requiring no further installation or registration.
This proved to be a very powerful library to create functions that one wishes Excel would have had built-in. It comes with all the bells and whistles to make your UDFs appear as a native Excel function. Overall, it is a powerful and actively maintained library written by Govert van Drimmelen.
GeoLib
The first Excel extension, that I wrote using Excel-DNA, was the second iteration of “GeoLib“; a library of geophysical routines, that covers a broad range of topics, such as AVO, Fourier transforms and convolution, coordinate conversion, pore fluid functions, rock property functions, as well as tapers and wavelet functions. It is available at the download page here.
ExcelDna-XlDialogBox
The most robust way to make input dialogs with Excel-DNA (still) appeared to be the DIALOG.BOX macro that is part of the XLM macro’s that predate Visual Basic for Applications (VBA) and were introduced in Excel 4.0. Documentation of these macro functions is hard to come by, but a comprehensive function reference document can be found on cloudfront.net.
You may wonder, why spend time and effort on working on such ancient technology, but the point is that there are no good edit controls available (yet) for Winforms or WPF-forms. This is discussed in-depth here.
To help developing Excel-DNA Addins with some user interface elements, I created the ExcelDna-XlDialogBox project, that is available on GitHub here. With a single XlDialogBox.cs – source file containing all dialog classes, you can easily generate dialogs like the one shown below:
TopoLib
Early 2022, I completed the “TopoLib” library. This is a coordinate transform library maintained on GitHub, that uses the PROJ library to do all the hard work.
PROJ is a generic coordinate transformation software library that transforms geospatial coordinates from one coordinate reference system (CRS) to another. This includes cartographic projections as well as geodetic transformations.
It is written in C++, and it has a C++ API to make its functionality accessible to 3rd party software. Because Excel-DNA is based on C# and uses managed code, an extra layer is needed in between PROJ and TopoLib, to cement the two together.
This C# interface is supplied by SharpProj, written and actively maintained by Bert Huijben.
At present, the TopoLib library has been upgraded to use Proj 9.0.1, accessed via SharpProj 9.1.183. Several spreadsheets have been provided in the Samples folder on GitHub (source code).
The binaries in my download area have also been updated.