ExcelDNA from VBA

Aug 16, 2010 at 10:30 AM

ExcelDNA looks quite good, better to say exceptional, for the job of creating Excel UDF's. However, I think that the only way till now suggested for using ExcelDNA from VBA, i.e., using Application.Run, is inadequate (error prone, slow, etc.). By myself I am (as I guess most of the "Excel programmers") mainly a VBA programmer, and what I need is a smooth migration where I can move part of the code base to C# or VB Net in ExcelDNA and still make use of it from VBA.

So the question: is there a more elegant and efficient way to use an ExcelDNA function from VBA?



Aug 20, 2010 at 9:43 AM


Migrating from VBA to .NET is one of the main use-cases for Excel-Dna. VBA code should be very easy to port to VBA.NET with Excel-Dna, and I'd love to help with plans that would make for a smoother migration.

Application.Run to an Excel-Dna function or macro is likely to be slower than a pure VBA call, and you might want to consider not having such calls to a migrated function inside nested loops. Maybe you need to consider the granularity of your migrated chunks to not have the performance be a big problem. Still, the performance hit of Application.Run shouldn't be terrible - have you timed this? Remember the first call might be much slower than subsequent calls, as the .Net code is loaded and JIT-compiled.

For making an easier interface for the Exposed functions, I suggest two approaches:

1. Create some VBA wrappers for the migrated functions that hide the Application.Run calls, and will give you intellisense at the call site.

2. Construct your .Net code as a part that has the core computations, and a wrapper exposing functions and UI to Excel. The core library might then be referenced directly from your VBA project (after COM registration etc.), and would also be referenced by your .xll add-in which exposes the high-performance UDFs to Excel.

I appreciate your thoughts on this.