Question Calling Dot Net code directly from a custom Excel "=" function

[Keith Howard]

Hello,

For some years, I have been trying to convert my Excel VBA functions to Dot Net. The way I currently do this is by calling a VBA wrapper function, which then calls Dot Net code containing the real logic that I am trying to implement in the function.

Maintaining VBA wrapper functions, however, is cumbersome. In addition, this approach has the disadvantage that the Excel containing the wrapper function needs to be converted to a .xlsm file. I have a utility that enables me to search and replace over .xlsx files, but this utility does not work over .xlsm files. Therefore, I have been waiting for MS to provide a way to eliminate the VBA wrapper functions and to call Dot Net code directly.

I just came across the following write-up of Excel DNA, which seems to achieve this goal:
http://www.sysmod.com/vba-to-vb.net-xll-add-in-with-excel-dna.pdf

I was wondering if anyone had experience with this product. The idea behind this product sounds very appealing, but the documentation on Excel-DNA: Introducing ExcelDna - DotNet for Applications seems quite out of date, and I am using Office 2013 (with Excel version 15) and Dot Net 4.5.

Also, has MS put out any best-practice guidance for writing Excel functions that access Dot Net code?

Thanks in advance for your feedback.

Kind regards,
Keith

 

[govert]

Hi Keith,

I'm the developer of the Excel-DNA library. It is indeed a popular approach to making Excel add-ins with .NET, especially for making high-performance worksheet functions. Excel-DNA is actively supported and developed, and fully supports Excel 2013 and .NET 4.5.

Patrick O'Beirne's guide to migrating from VBA to VB.NET with Excel-DNA, which you link to in your post, is indeed a good start. The covering blog post is here: Migrating an Excel VBA Add-in to a VB.Net XLL with Excel-Dna: update | Patrick O'Beirne @ sysmod

In general, the best resource for Excel-DNA information is the CodePlex site: https://exceldna.codeplex.com. You'll find a variety of links to different posts and projects there. For questions about using the library, getting started with VB.NET or anything related, you can post to the Google forum at https://groups.google.com/forum/#!forum/exceldna. The TypePad post that you point to is quite old.

I hope this encourages you to have a closer look at Excel-DNA for your .NET in Excel needs.

Regards,
Govert

 

[JohnH]

Have a read through this article, code samples in C# though, it also mentions Excel DNA: Using C# .NET User Defined Functions (UDF) in Excel ? Adam Tibi .NET

 

[Keith Howard]

Hi Govert,

Thanks for your reply.

Can I ask whether you need to use .xlsm files to access your own custom Dot Net code via Excel-DNA, or can you use .xlsx files? (I have a strong preference to use .xlsx rather than .xlsm files, because you can't do search and replace operations over a .xlsm file...i.e., from outside the file, rather than from within Excel. I think that that is because .xlsm files are binary or encrypted in a way that .xlsx files are not....not quite sure...)

Kind regards,

Keith

 

[Keith Howard]

Hi Govert,

In addition to my above question regarding using Excel-DNA with .xlsx files, I also wanted to ask you whether the Excel-DNA product can be used with Excel 2013? I downloaded Excel-DNA and tried to install it, but as far as I could tell, it does not work with Excel 2013 - can you please confirm any compatibility restrictions?

Many thanks,

Keith