Creating new Excel formulas/functions with C#

Question

We are looking to be able to programmatically create an Excel workbook which would call custom code from within a cell. Cells would look something like:

=MyCode(A1:A10)

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

My second thought was to create an Excel 2013 Workbook project in Visual Studio, but I haven't found anything useful on how to expose a function in C# so it can be called like I described.

Next I thought about having VBA call the C#, and found instructions at https://msdn.microsoft.com/en-us/library/bb608613.aspx. I followed those instructions to the letter, but when I try to run the VBA code I get an error with the GetManagedClass function: Object Library Feature not Supported.

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

The most secure thing is to create a Win32 dll, like with C, C++ or Fortran and call it from VBA. — John Alexiou
– John Alexiou, Commented Mar 18, 2015 at 20:37
@pnuts: Why would this question be considered off-topic? This is total programming. — Walter Williams
– Walter Williams, Commented Mar 18, 2015 at 20:38
I think he's taking issue with: Are there any good references on how to do something like this? If you provide a code sample that generates the error, with an indication of where the error is, you'll A) get on pnuts good side, and B) be more likely to get specific help. Referencing the MS docs and saying you followed them doesn't help us help you find a typo. — FreeMan
– FreeMan, Commented Mar 18, 2015 at 20:56
The code out of context from the article would be useless to repeat it here, and if I could attach a zip I would. The MSDN sample doesn't work and I have not been able to find any online information via MSDN or elsewhere on how to set up this kind of thing correctly. — Walter Williams
– Walter Williams, Commented Mar 18, 2015 at 21:07

Govert · Accepted Answer · 2015-03-18 21:45:47Z

6

You're looking for Excel-DNA. This open-source library allows you to create managed Excel add-ins, and supports making user-defined functions, but also macros, real-time RTD data sources etc.

Creating an Excel UDF in C# is then 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 as:

=SayHello("Walter")

For code protection with .NET, you'd need to use an obfuscator - there are a variety of free and paid-for ones available.

answered Mar 18, 2015 at 21:45

Govert

17k4 gold badges63 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1683456 Over a year ago

When I tried this example but it doesn't appear to work. Excel shows "#Name?" in the cell.

Govert Over a year ago

It sounds like something unexpected went wrong. The best place to ask for Excel-DNA is the Google group at groups.google.com/g/exceldna Post a bit more details about your project, which version of Excel-DNA you're using etc. I can help you dig into the problem from there.

Torben Nygaard · Accepted Answer · 2015-03-21 06:49:23Z

0

I have 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 public, and it applies the http://msdn.microsoft.com/en-us/library/system.runtime.interopservices.comvisibleattribute.aspx attribute to make the interface visible to COM.

C#

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

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

answered Mar 21, 2015 at 6:49

Torben Nygaard

1011 silver badge3 bronze badges

Collectives™ on Stack Overflow

Creating new Excel formulas/functions with C#

2 Answers 2

2 Comments

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

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

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

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related