Adding a C# method as an Excel function

[urig]

Hi,

This is a newbie question - be warned

I need to make a method that I've written in C# available to Excel
users as a function. I have: "public string encryptSomething(string
something)" and I'd like to make it visible to Excel so users can put
"=encryptSomething(A1)" in a cell and, well, encrypt something.

I know I can do this by writing a hybrid between C# and C++ to create
an xll file. I know that's how ExcelDna (http://exceldna.typepad.com/)
does its magic. Can someone point me to a simpler example of this
technique?

And shouldn't there be a more elgant way to do this? Something that
wouldn't involve C++?

Will VSTO give me this functionality? If so, will VSTO require my end
users to install something apart from whatever DLL/XLL I will be
shipping to them?


Thanks!
urig.

 

[NickHK]

"Something simpler" for me would be to use VBA and call the the Windows APIs
that can deal with encryption, those called Crypt*.
Here's a couple of examples:
http://simplesamples.info/VB/EncryptDecrypt.php
http://www.ex-designz.net/apidetail.asp?api_id=394
You can wrap the code in a class and make to an add-in also, but all code
would be VBA.

If you need to use one of the .Net languages, then AFAIK you need something
compiled; xll, dll (ActiveX or Standard).

NickHK

 

[govert]

Hi Urig,

Using 'ExcelDna' (http://exceldna.typepad.com) does not mean you hav
to create or compile any C++ code. Your user-defined functions, writte
in C# or VB.NET, can be either directly in a text-based script file, o
can be in a compiled library. You have to write nothing more than th
implementation of your functions. The ExcelDna library automaticall
exposes these functions and macros to Excel.

VSTO does not help with creating user-defined functions for Excel.

An alternative approach which does not require any third-party library
is to make a COM Automation Add-In. You can find some details o
'CodeProject' (http://www.codeproject.com/dotnet/excelnetauto.asp).

Regards,
Gover

 

[urig]

Thanks for your replies NickHK and govert.

Special thanks go to you govert, for having written the wonderful
ExcelDna. I've had a look at your source code and the work you've done
is robust and impressive by any standard!

Currently, ExcelDna is my fallback. Its only drawbacks are that it
exposes my code to tampering by end clients and that it requires me to
distribute a few additional dlls. I would still rather have a my
deliverable consist of a single installable XLL file.

I was surprised to see how much work you had to put into ExcelDna to
register a new C# user-defined function into Excel. How come VSTO
doesn't supply this functionality? Maybe it does so only for Excel
2007? How come it's so hard to hook up .net code into MS Excel?


Regards,
urig

 

[govert]

Hi Uhrig,

Currently, ExcelDna is my fallback. Its only drawbacks are that it
exposes my code to tampering by end clients and that it requires me to
distribute a few additional dlls. I would still rather have a my
deliverable consist of a single installable XLL file.

Not sure what you mean with the tampering issue. Easy decompilation is
a property of .NET assemblies, and you should look at obfuscation
tools to address this. But this issue is independent of how you
integrate into Excel.
Indeed for ExcelDna you currently have to ship an extra .dll file and
a text file, and address the .Net version loading issue -- I would
like to simplify this in future. If you have particular requirements,
please let me know.

How come VSTO doesn't supply this functionality?
Maybe it does so only for Excel 2007?

I know of no plans to support the creation of UDFs in VSTO add-ins,
also not for Excel 2007. But the .XLL interface (that ExcelDna uses)
got a significant upgrade in Excel 2007, so it should be a great way
to integrate into Excel for the foreseeable future.

How come it's so hard to hook up .net code into MS Excel?

It's not hard at all, and there are many ways to do it. You can:
1. Reference a .Net library in VBA code, and create functions VBA
functions that call your library.
2. Create an Automation Add-In and integrate into Excel using COM
interop.
3. Create a .XLL add-in using C/C++ and reference your .NET code
(using the It-Just-Works C++/CLR binding).
4. Use ExcelDna which is easy, open-source and free (including for
commercial use).
5. Use the commercial ManagedXll library, which is expensive but has a
very broad range of features.
For the last two, you need to write no additional code to expose your
functions to Excel, you just add some attributes in your code. Seems
pretty easy to me.

Regards,
Govert