Excel-DNA

link - docs.excel-dna.net
This is an open source project that lets you create native (.xll) add-ins for Excel using C#, VB.Net or F#.
The Excel C API is not directly accessible from .NET code but the Excel-DNA runtime allows managed assemblies to integrate with this low-level Windows API.
This lets you create high performance user-defined functions and requires no installation or registration.
Allowing a user to install and run on a machine without admin access can be extremely useful.
The acronym RTD stands for Real Time Data.


Excel 2013, Excel 2016, Excel 2019, Excel 365

In Excel 2013, programmers can run multiple user-defined functions asynchronously at the same time.
This frees the Excel calculation thread to run other calculations while the user-defined functions wait for external resources.
The RTD approach for Excel 2013 (and later) is to use the System.Threading.Tasks.Task class which runs the code using the Task Parallel Library (TPL).
Using the Task class is much more efficient that using the Thread class.
The TPL was added in .NET 4.0 and brings many new features:
The corresponding ExcelDNA method is ExcelAsyncUtil.Observe


Excel 2003, Excel 2007

In Excel 2007, programmers could run multiple user-defined functions at the same time by using threads.
Increasing the number of threads used in multiple-thread recalculations increased the performance.
This method has drawbacks primarily because the number of threads is a setting scoped to an application.
The number of threads cannot be controlled at the level of a single function or an add-in.
Some user-defined functions must wait for external resources and while they wait, the Excel calculation thread is blocked.
The RTD approach for Excel 2007 was to use the System.Threading.Thread class and runs the code using a ThreadPool thread.
The corresponding ExcelDNA method is ExcelAsyncUtil.Run


Interfaces

The Excel C API was first introduced in Excel '95 and has continuously evolved with new versions of Excel.
Excel-DNA uses the C API to integrate with Excel, though
Excel-DNA can also use the COM Automation interfaces where needed.


Registry Keys

Need to add a registry key
HKCU\Software\Microsoft\Office\12.0\Excel\Options\ OPEN1 - /R "MyXLL.dll"
If the add-in is not in the default add-ins folder then the full path must be provided
The Excel-DNA runtime contains a small loader (the .XLL) that loads the .NET runtime, then checks the configuration (.dna) file and accordingly loads the managed assembly.
The managed assemblies are then inspected using the .NET Reflection API and the appropriate methods are registered.


Links

link - nuget.org/packages/Excel-Dna
link - nuget.org/packages/ExcelDna.Integration/
link - github.com/Excel-DNA/Samples
link - github.com/Excel-DNA/ExcelDna
link - groups.google.com/g/exceldna
link - youtube.com/watch?v=UEIO2Q8HyD8
link - youtube.com/watch?v=0-Rb0StiJgM
link - bitbucket.org/andysprague44/excel-dna-examples/src/master/


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext