Skip to main content

· One min read

I've updated and improved the Excel-DNA NuGet package. (NuGet is the Visual Studio package manager that makes it easy to download and install external libraries into your projects.)

To turn your Class Library project into an Excel add-in, just open Tools -> Library Package Manager -> Package Manager Console, and enter

PM> Install-Package Excel-DNA

The Excel-DNA package now has an install script that creates the required .dna file, and a post-build step to copy the .xll and run the packing tool, and even configures debugging. The package should work for C#, Visual Basic and F# Class Library projects on:

  • Visual Studio 2010 Professional and higher
  • Visual Studio 2012 Professional and higher
  • Visual Studio 2012 Express for Windows Desktop

Please post any feedback - bugs, good or bad comments - to the Excel-DNA Google group.

· 3 min read

Excel-DNA Version 0.30 has now been released on CodePlex. The download is available from

This version implements support for RTD-based asynchronous worksheet functions based on a thread-safe RTD base class - ExcelRtdServer. The asynchronous support is designed to (optionally) integrate with the .NET 4.0 Task-based operations, as well as the Reactive Extensions library, allowing IObservables to be exposed as "live" worksheet UDFs

  • (thus "RxExcel"). The language-specific support for asynchronous functions found in C# 5, Visual Basic 11 and F# 2.0 can be easily integrated with the Excel-DNA asynchronous interfaces. Some examples experimenting with the new async features are available in the download.

Various bug fixes have also accumulated over the last 18 months, and are collected in this release.

As always, I greatly appreciate any feedback on this version, and on Excel-DNA in general. Any comments or questions are welcome on the Google group or by contacting me directly.

Changelist - Version 0.30 (12 December 2012)

  • Fixed LoadComAddIn error when using a direct ExcelComAddIn-derived class.
  • Fixed (Ribbon Helper) display in ribbon tooltips.
  • Fixed RTD / array formula activation bug.
  • Fixed IsMacroType=true reference argument sheet error (ExcelReference pointed to active sheet instead of current sheet).
  • Fixed array marshaling pointer manipulation concern under 64-bit Excel.
  • Fixed check for derived attributes too - for backward compatibility with v. 0.25.
  • Fixed assembly multiple-loading problem for packed assemblies.
  • Fixed persistent COM registration (Regsv32.exe / ComServer.DllRegisterServer) to allow HKCR registration whenever possible (for UAC elevation issue).
  • Fixed Excel version check when COM / RTD Server loads before add-in is loaded - ribbon would not load.
  • Fixed IntPtr StackOverflowException in high-memory 32-bit processes.
  • Fixed custom task panes UserControl activation - do HKCR registration whenever possible (for UAC elevation issue).
  • Fixed double[0,1] array marshaling memory allocation error with potential access violation.
  • Allow abstract base classes in ExcelRibbon class hierarchy. Now loads the first concrete descendent of ExcelRibbon as the ribbon handler.
  • Remove Obsolete class ExcelDna.Integration.Excel. (Use ExcelDnaUtil instead.) Allows smooth XlCall usage.
  • Allow external SourceItem packing.
  • Add ExcelAsyncUtil for async macro calls.
  • Add thread-safe RTD server base class ExcelRtdServer.
  • Add async function helper as ExcelAsyncUtil.Run.
  • Add support for Reactive Extensions via RTD via ExcelAsyncUtil.Observe and related interfaces.
  • Change ExcelRibbon and ComAddIn loading to use declared ProgId and Guid if both attributes are present. Fixed Ribbon QAT issue.
  • Revisit caching of Application object.
  • Rename ExcelDna.Integration.Integration to ExcelDna.Integration.ExcelIntegration.
  • Implement macro shortcuts (from ExcelCommand attributes).
  • Changed re-open via File->Open to do full AppDomain unload and add-in reload.

· 2 min read

Excel-DNA is a great library to help ease the path from Excel VBA to VB.NET. Last year another part of the puzzle fell in place: I discovered NetOffice, a version-independent set of Office interop assemblies put together by Sebastian Lange. By referencing the NetOffice assemblies instead of the official Primary Interop Assemblies (PIA) for Office, an Excel-DNA add-in can target various Excel versions with a single add-in, and also ease distribution of the required interop assemblies, even packing them into the .xll add-in itself.

To explore how Excel-DNA and NetOffice can combine to convert a VBA add-in to VB.NET, I picked a small add-in made by Robert del Vicario that does a risk analysis simulation inspired by the Pallisade @RISK add-in. I took Robert's original RiskGen VBA add-in, and created a new Excel-DNA add-in in VB.NET (I used Visual Studio, but the free SharpDevelop IDE should work fine too). I documented the steps along the way of creating the VB.NET project, making an add-in based on Excel-DNA and using NetOffice to help port the VBA code to VB.NET. The resulting document (RiskGen Port Log.docx) outlining exactly how I ported the add-in, with the new VB.NET-based RiskGen.NET is also on Robert's site.

I'm also looking for some more examples of free/open source VBA add-ins to port to Excel-DNA. The best add-ins will contain a mix of user-defined functions and macros which use the Excel object model. Please post to the Google group or mail me directly if you have any suggestions.

And as always, if you need any support porting your Excel VBA add-ins to .NET using Excel-DNA, I'm happy to help on the Excel-DNA Google group.

· 2 min read

I have posted a release candidate of Excel-DNA version 0.29 to the CodePlex site. The download is available at I will wait a week or two for some confirmation that this version works correctly before setting this release to "recommended" status and updating the NuGet package. Any results from your testing with this version would be very helpful.

Excel-DNA version 0.29 adds support for a number of specialized Excel features. The 64-bit version of Excel 2010 is fully supported, registration-free Custom Task Panes can be created under Excel 2007 and later, direct COM server integration can improve integration with legacy VBA code, and macros with parameters are registered. In addition, there are some features to improve the development and debugging workflow, and a few minor bugfixes. The complete change list is included below.

More information about the new features will be posted on the Excel-DNA website in the coming weeks. Any comments or questions are welcome on the Google group - - or by contacting me directly.

As always, I greatly appreciate any feedback on this version, and on Excel-DNA in general.


Complete change list

  • BREAKING CHANGE! Changed SheetId in the ExcelReference type to an IntPtr for 64-bit compatibility.
  • Changed initialization - only create sandboxed AppDomain under .NET 4 (or if explicitly requested with CreateSandboxedAppDomain="true" attribute on DnaLibrary tag in .dna file).
  • Fixed memory leak when getting SheetId for ExcelReference parameters.
  • Fixed Ribbon RunTagMacro when no Workbook open.
  • Added support for the 64-bit version of Excel 2010 with the .Net 4 runtime.
  • Added Cluster-safe function support for Excel 2010 HPC Cluster Connector - mark functions as IsClusterSafe=true.
  • Added CustomTaskPane support and sample.
  • Added COM server support for RTD servers and other ComVisible classes. Mark ExternalLibraries and Projects as ComServer="true" in the .dna file. Supports Regsvr32 registration or by calling ComServer.DllRegisterServer. Allows direct RTD and VBA object instantiation. Includes TypeLib registration and packing support.
  • Added support for macros with parameters.
  • Added ArrayResizer sample.
  • Added C# 4 dynamic type sample.
  • Added Path attribute to SourceItem tag to allow external source.
  • Added LoadFromBytes attribute to ExternalLibrary tag to prevent locking of .dll.
  • Added /O output path option to ExcelDnaPack.
  • Added "before" option to CommandBars xml.
  • Added Int64 support for parameters and return values.

· One min read

I recently noticed a very nice add-in developed by Bryan McKelvey called FinAnSu. The whole add-in is generously available under the MIT open source license, and is a fantastic example of what can be built with Excel-DNA.

FinAnSu uses a ribbon interface to make the various functions and macros easy to find. The RTD server support is used to implement asynchronous data update functions, providing a live quote feed from Bloomberg, Google or Yahoo! And then there is a bunch of useful-looking financial functions. Here's a little preview:

FinAnSu Quote Animated

Find the project on Google code:, with detailed documentation on the wiki:

You can browse through the source code online, but to download a copy of the whole project you'll need a Mercurial client. I just installed the one called Mercurial 1.8.2 MSI installer and ran hg clone finansu from a command prompt.

· 6 min read

Update (21 June 2017): The most up-to-date version of the ArrayResizer utility can be found here:

Update: To work correctly under Excel 2000/2002/2003, this sample requires at least version of Excel-DNA.

A common question on the Excel-DNA group is how to automatically resize the results of an array formula. The most well-know appearance of this trick is in the Bloomberg add-in.

WARNING! This is a bad idea. Excel does not allow you to modify the sheet from within a user-defined function. Doing this breaks Excel's calculation model.

Anyway, here is my attempt at an Excel-DNA add-in that implements this trick. My approach is to run a macro on a separate thread that will check and if required will expand the formula to an array formula of the right size. This way nothing ugly gets done if the array size is already correct - future recalculations will not run the formula array resizer if the size is still correct.

The code below will register a function call Resize. You can either call Resize from within your function, or enter something like =Resize(MyFunction(…)) as the cell formula. The code also registers two sample functions, MakeArray and MakeArrayAndResize to play with, each take the number of rows and columns for the size of the returned array.

To test this:

  1. Get started with Excel-DNA.
  2. Copy the code and xml wrapper into a text file called Resizer.dna (the xml wrapper is at the end of this post).
  3. Copy the ExcelDna.xll in the Excel-DNA distribution to Resizer.xll (next to the Resizer.dna).
  4. File->Open the Resizer.xll in Excel and enter something like =MakeArrayAndResize(5,3) into a cell. See how it grows.

In the current version, the formula expansion is destructive, so anything in the way will be erased. One case I don't know how to deal with is when there is an array that would be partially overwritten by the expended function result. In the current version Excel will display an error that says "You cannot change part of an array.", and I replace the formula with a text version of it. I'd love to know how you think we should handle this case.

Any questions or comments (can if anyone can get it to work, or not?) can be directed to the [Excel-DNA Google group][excel-dna-group] or to me directly via e-mail. I'm pretty sure there are a few cases where it will break - please let me know if you run into any problems.

I'll try to gather the comments and suggestions for an improved implementation that might go into the next version of Excel-DNA.

Also, if you have any questions about how the implementation works, I'd be happy to write a follow up post that explains a bit more of what I'm doing. But first, let's try to get it working.

Here's the Resizer add-in code:

using System;
using System.Collections.Generic;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Threading;
using ExcelDna.Integration;

public static class ResizeTest
public static object MakeArray(int rows, int columns)
object[,] result = new string[rows, columns];
for (int i = 0; i < rows; i++)
for (int j = 0; j < columns; j++)
result[i,j] = string.Format("({0},{1})", i, j);

return result;

public static object MakeArrayAndResize(int rows, int columns)
object result = MakeArray(rows, columns);
// Call Resize via Excel - so if the Resize add-in is not part of this code, it should still work.
return XlCall.Excel(XlCall.xlUDF, "Resize", result);

public class Resizer
static Queue<ExcelReference> ResizeJobs = new Queue<ExcelReference>();

// This function will run in the UDF context.
// Needs extra protection to allow multithreaded use.
public static object Resize(object[,] array)
ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
if (caller == null)
return array;

int rows = array.GetLength(0);
int columns = array.GetLength(1);

if ((caller.RowLast - caller.RowFirst + 1 != rows) ||
(caller.ColumnLast - caller.ColumnFirst + 1 != columns))
// Size problem: enqueue job, call async update and return #N/A
// TODO: Add guard for ever-changing result?
EnqueueResize(caller, rows, columns);
return ExcelError.ExcelErrorNA;

// Size is already OK - just return result
return array;

static void EnqueueResize(ExcelReference caller, int rows, int columns)
ExcelReference target = new ExcelReference(caller.RowFirst, caller.RowFirst + rows - 1, caller.ColumnFirst, caller.ColumnFirst + columns - 1, caller.SheetId);

public static void DoResizing()
while (ResizeJobs.Count > 0)

static void DoResize(ExcelReference target)
// Get the current state for reset later

XlCall.Excel(XlCall.xlcEcho, false);

// Get the formula in the first cell of the target
string formula = (string)XlCall.Excel(XlCall.xlfGetCell, 41, target);
ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);

bool isFormulaArray = (bool)XlCall.Excel(XlCall.xlfGetCell, 49, target);
if (isFormulaArray)
object oldSelectionOnActiveSheet = XlCall.Excel(XlCall.xlfSelection);
object oldActiveCell = XlCall.Excel(XlCall.xlfActiveCell);

// Remember old selection and select the first cell of the target
string firstCellSheet = (string)XlCall.Excel(XlCall.xlSheetNm, firstCell);
XlCall.Excel(XlCall.xlcWorkbookSelect, new object[] {firstCellSheet});
object oldSelectionOnArraySheet = XlCall.Excel(XlCall.xlfSelection);
XlCall.Excel(XlCall.xlcFormulaGoto, firstCell);

// Extend the selection to the whole array and clear
XlCall.Excel(XlCall.xlcSelectSpecial, 6);
ExcelReference oldArray = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);

XlCall.Excel(XlCall.xlcSelect, oldSelectionOnArraySheet);
XlCall.Excel(XlCall.xlcFormulaGoto, oldSelectionOnActiveSheet);
// Get the formula and convert to R1C1 mode
bool isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4);
string formulaR1C1 = formula;
if (!isR1C1Mode)
// Set the formula into the whole target
formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);
// Must be R1C1-style references
object ignoredResult;
XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, formulaR1C1, target);
if (retval != XlCall.XlReturn.XlReturnSuccess)
// TODO: Consider what to do now!?
// Might have failed due to array in the way.
firstCell.SetValue("'" + formula);
XlCall.Excel(XlCall.xlcEcho, true);

// Most of this from the newsgroup:
private static readonly TimeSpan BackoffTime = TimeSpan.FromSeconds(1);
static void AsyncRunMacro(string macroName)
// Do this on a new thread....
Thread newThread = new Thread( delegate ()
catch(COMException cex)
// TODO: Handle unexpected error
catch(Exception ex)
// TODO: Handle unexpected error

static void RunMacro(string macroName)
object xlApp;
object xlApp = ExcelDnaUtil.Application;
xlApp.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, xlApp, new object[] {macroName});
catch (TargetInvocationException tie)
throw tie.InnerException;

const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
const uint VBA_E_IGNORE = 0x800AC472;
static bool IsRetry(COMException e)
uint errorCode = (uint)e.ErrorCode;
return true;
return false;

You can easily make a test add-in for this by wrapping the code into a .dna file with this around:

<DnaLibrary Language="CS">

<!--// Paste all of the above code here //-->


· 2 min read

Excel 2010 introduced support for offloading UDF computations to a compute cluster. The Excel blog talks about it, and there are some nice pictures on this TechNet article:

Excel-DNA now supports marking functions as cluster-safe, and I have updated the loader to allow add-ins to work under the XllContainer on the HPC nodes. There are some issues to be aware of:

  • The add-in does not create its own AppDomain when running on the compute node. One consequence is that no custom .xll.config file is used; configuration entries need to be set in the XllContainer configuration setup.
  • There are some limitations on the size of array data that can be passed to and from UDF calls - this limit is probably configurable in the WCF service.
  • Only the 32-bit host is currently supported.

To test this you will need an Windows HPC Server 2008 R2 cluster with the HPC Services for Excel installed. On the clients you need Excel 2010 with the HPC cluster connector installed. The latest check-in for Excel-DNA with this support is on GitHub:

In the Microsoft HPC SDK there is a sample called ClusterUDF.xll with a few test functions. I have recreated these in C# in the samples file Distribution\Samples\ClusterSample.dna Basically functions just need to be marked as IsClusterSafe=true to be pushed to the cluster for computation. For example

public static int DnaCountPrimesC(int nFrom, int nTo)
// ...

As usual, any feedback on this feature - questions or reports on whether you use it - will be most appreciated.