Creating New Excel Formulas / Functions Using C #

Question

We want to be able to programmatically create an Excel workbook that invokes custom code inside a cell. The cells would look something like this:

=MyCode(A1:A10) 

My first thought was to use VBA, but since the algorithm is patented, the credentials that want it to be protected. I can put a password on it, but it is well documented (here on StackOverflow) about how to get around such passwords.

My second thought was to create an Excel 2013 workbook project in Visual Studio, but I did not find anything useful in how to expose a function in C # so that it can be called as I described.

Next, I thought about VBA calling C #, and found instructions at https://msdn.microsoft.com/en-us/library/bb608613.aspx . I followed these instructions for writing, but when I try to run VBA code, I get an error with the GetManagedClass function: the object library function is not supported.

Are there any good recommendations on how to do something like this?

Answer 1

You are looking for Excel-DNA . This open source library allows you to create managed Excel add-ins and supports the creation of custom functions, as well as macros, real-time RTD data sources, etc.

Creating Excel UDF in C # is as simple as:

 [ExcelFunction(Description = "My first .NET function")] public static string SayHello(string name) { return "Hello " + name; } 

and you can call from a cell like:

 =SayHello("Walter") 

To protect the code with .NET, you need to use an obfuscator - there are many free and paid ones.

Answer 2

I also tried this sample with the same error. I found a solution that worked for me.

In the ISheet1.cs file, replace the ISheet1 interface declaration with the following code. This code makes the ISheet1 interface publicly available, and it uses the http://msdn.microsoft.com/en-us/library/system.runtime.interopservices.comvisibleattribute.aspx attribute to make the interface visible to COM.

FROM#

 [System.Runtime.InteropServices.ComVisible(true)] public interface ISheet1 { void CreateVstoNamedRange(Microsoft.Office.Interop.Excel.Range range, string name); } 

Full article: http://www.nullskull.com/q/10059408/c-code-to-set-excel-workbook-macro-enabled-and-to-trust-vba-projects.aspx