Excel-DNA

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.


Useful Links

link - docs.excel-dna.net/
link - nuget.org/packages/Excel-Dna
link - nuget.org/packages/ExcelDna.Integration/
link - github.com/Excel-DNA/ExcelDna
link - groups.google.com/g/exceldna


Supported Features

64-bit versions of Excel
Multi-threaded recalculation (Excel 2007 and later)
Custom Ribbon Interfaces (Excel 2007 and later)
RTD Servers (registration free)
Custom Task Panes (Excel 2007 and later)
Offloading UDF computations to a Windows HPC cluster (Excel 2010 and later)


Supports Two Types of async functions:


Type 1 - RTD Based Async

Uses ExcelAsyncUtil
Can interact with Excel while the function executes
This uses an internal RTD server internally.


Type 2 - Native Async

Only works with Excel 2010 or later
Cannot interact with Excel while the function executes
Can continue with other parts of the calculation tree while the function executes.



Function Declarations

You are able to define the function wizard category as well as the function description and argument descriptions.
You can set the function wizard category as well as the function and argument descriptions.
You can even provide a tooltip
These customisations can be made by adding .NET attributes to the function declarations.
You need to reference the ExcelDna.Integration.dll assembly.
You do not need to redistribute the ExcelDna.Integration file - a copy is embedded as a resource.

[ExcelDna.Integration.ExcelFunction(Description="HelloWorld", Name="myFunction")] 
public static string MyFunction([ExcelArgument(Description=@"Text Value")] string sText)
{
   return sText + sText;
}

AfterCalculate Event

Add a Reference to the Excel Interop.
Assemblies, Extensions: "Microsoft.Office.Interop.Excel" (version 15.0.0.0)
Register an event handler for the AfterCalculate event.
The "AppEvents_AfterCalculateEventHandler" will not appear in the intellisense.

using Excel = Microsoft.Office.Interop.Excel; 

public class MyAddin : ExcelDna.Integration.IExcelAddIn
{
   public void AutoOpen()
   {
      Excel.Application xlApp;
      xlApp = (Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;

      xlApp.AfterCalculate += new Excel.AppEvents_AfterCalculateEventHandler(Method_RunAfterCalculate)
   }

   // only called when the user unticks the add-in and not when Excel closes
   public void AutoClose()
   {
   }

   [ExcelDna.Integration.ExcelFunction(Description="HelloWorld", Name="myFunction")]
   public static string MyFunction([ExcelArgument(Description=@"Text Value")] string sText)
   {
      Method_PreProcessing((Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application);

      return sText + sText;
   }

   // check settings, initialise start time, clear cache
   private static void Method_PreProcessing(Excel.Application xlApp)
   {
      int workerThread;
      int ioThread;
      int availableWorkerThread;
      int availableIOThread;

      if (!_bStartRefresh)
      {
         // get diagnostics info
         System.Threading.ThreadPool.GetMinThreads(out workerThread, out ioThread)
         System.Threading.GetAvailableThreads(out availableWorkerThread, out availableIOThread)

         ExcelDna.Integration.ExcelAsyncUtil.QueueAsMacro(() => logSomething());

         lock (_oSingletonLock)
         {
            xlApp.AfterCalculate -= _afterCalcHandler;
            xlApp.AfterCalculate += _afterCalcHandler;
         }
      }
   }

   private static void Method_RunAfterCalculate()
   {
   }
}


Caching Data


namespace MyNewXLL 
{
public class MyAddin : ExcelDna.Integration.IExcelAddIn
{
   private static bool _bStartRefresh;
   private static int _iRefreshCount;
   private static DateTime _dtRefreshStartTime;
   private static readonly object _oSingletonLock = new Object();
   private static Excel.AppEvents_AfterCalculateEventHandler _afterCalcHandler =
      new Excel.AppEvents_AfterCalculateEventHandler(Method_AutoSizeColumns)

   public void AutoOpen()
   {
      Excel.Application xlApp;
      xlApp = (Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;

      xlApp.CalculationInterruptKey = Excel.CalculationInterruptKey.xlNoKey;

      xlApp.AfterCalculate += _afterCalcHandler;

      Method_ClearCache();

      ExcelDna.Integration.ExcelIntegration.RegisterUnhandledExceptionHandler(ex => "EXCEPTION: " + ex.ToString());
   }

   // only called when the user unticks the add-in and not when Excel closes
   public void AutoClose()
   {
      Method_ClearCache();
   }

   [ExcelDna.Integration.ExcelFunction(Description="HelloWorld", Name="myFunction")]
   public static string MyFunction([ExcelArgument(Description=@"Text Value")] string sText)
   {
      Method_PreProcessing((Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application);

      return sText + sText;
   }

   private static void Method_ClearCache()
   {
   }

   // check settings, initialise start time, clear cache
   private static void Method_PreProcessing(Excel.Application xlApp)
   {
      int workerThread;
      int ioThread;
      int availableWorkerThread;
      int availableIOThread;

      if (!_bStartRefresh)
      {
         // get diagnostics info
         System.Threading.ThreadPool.GetMinThreads(out workerThread, out ioThread)
         System.Threading.GetAvailableThreads(out availableWorkerThread, out availableIOThread)

         ExcelDna.Integration.ExcelAsyncUtil.QueueAsMacro(() => logSomething());

         lock (_oSingletonLock)
         {
            AppDomain domain = getDefaultDomain();
            domain.SetData("REFRESH KEY", true);

            _bStartRefresh = true;
            _iRefreshCount = 1;
            _dtRefreshStartTime = System.DateTime.Now();

            xlApp.AfterCalculate -= _afterCalcHandler;
            xlApp.AfterCalculate += _afterCalcHandler;

            Method_ClearCache();

         }
      }
   }

   private static void Method_AutoSizeColumns()
   {
   }
}




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.



Reference


ExcelDna.Integration.IExcelAddIn 

ExcelDna.Integration.DnaLibrary 
ExcelDna.Integration.ExcelArgument
ExcelDna.Integration.ExcelArgumentAttribute
ExcelDna.Integration.ExcelAsyncHandle

ExcelDna.Integration.ExcelAsyncUtil 

QueueAsMacro
Run


ExcelDna.Integration.ExcelComAddIn 
ExcelDna.Integration.ExcelComAddInHelper
ExcelDna.Integration.ExcelCommandAttribute

ExcelDna.Integration.ExcelDnaUtil 

Application - the correct Application root object
ExcelLimits
ExcelVersion
MainManagedThreadId
SupportsDynamicArrays
WindowHandle
XllPath
XllPathInfo


ExcelDna.Integration.ExcelEmpty 

ExcelDna.Integration.ExcelError 

ExcelErrorNA


ExcelDna.Integration.ExcelErrorUtil 

ExcelDna.Integration.ExcelFunctionAttribute 

Description = ""
Name = ""
IsMacroType = false


ExcelDna.Integration.ExcelIntegration 

RegisterUnhandledExceptionHandler


ExcelDna.Integration.ExcelLimits 
ExcelDna.Integration.ExcelMissing
ExcelDna.Integration.ExcelReference
ExcelDna.Integration.ExcelSynchronizationContext
ExcelDna.Integration.ExternalLibrary
ExcelDna.Integration.IExcelObservable
ExcelDna.Integration.IExcelObserver
ExcelDna.Integration.Image
ExcelDna.Integration.Integration
ExcelDna.Integration.Project
ExcelDna.Integration.Reference
ExcelDna.Integration.SourceItem

ExcelDna.Integration.XlCall 

Excel
xlfActiveCell
xlfCaller
xlfGetWorkspace
xlfSelection
xlcEcho
xlcFormatNumber
xlcFormulaGoto
xlcSelect
xlcWorkbookSelect
xlSheetNm


ExcelDna.Integration.XlCallException 
ExcelDna.Integration.XlLibrary



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