If you search the Internet for information on how to link Excel VBA to C# you will find thousands, if not millions, of links with details of how to connect to Excel from C#, but very few about going the other way, which is what I want to do. The link below gives detailed, step by step, instructions for doing that, allowing functions in a C# library to be called from a VBA routine with the minimum of hassle. The link gives a very simple “hello world” example, and I will be giving some more useful examples, linking to the ALGLIB library, in future posts.
A Beginner’s Guide to calling a .NET Library from Excel
Introduction
It’s actually very easy to call a .NET library directly from Excel, particularly if you are using Visual Studio 2005. You don’t need Visual Studio Tools for Office. However there doesn’t seem to be an easy guide on the internet anywhere. MSDN help is quite good on the subject, but can be a little confusing. This article is an attempt to redress the situation.
This article was updated 24th August 2007 to cover Excel 2007 and to clarify the issues with intellisense.
… More at link
Reading the comments at the link, it seems there is a problem linking with the 64 bit version of Office 2010, so those wanting to do that have a look at the latest comments first. There is no problem with 64 bit Windows 7, which is what I am using (with 32 bit Office 2010).
The second link is to the MSDN Library, giving details of how to link different data types to objects in C#. I needed this for one of the ALGLIB routines I will be looking at. It’s probably very basic stuff, but for those not familiar with C#, it’s not that easy to find.
Boxing and Unboxing (C# Programming Guide)
Boxing and unboxing enable value types to be treated as objects. Boxing a value type packages it inside an instance of the Object reference type. This allows the value type to be stored on the garbage collected heap. Unboxing extracts the value type from the object. In this example, the integer variable i is boxed and assigned to object o.
… see link for example code.
Newton Excel Bach, not (just) an Excel Blog 
If you can get yourself out of the VBA mindset, there are a lot of great tools out there to help you move into the modern world. People seem to love the tools they know. Meanwhile, the world moves on to new things.
Excel-DNA is far better than the VSTO tools if you want to hook up .Net code with Excel.
If you know C++, http://xll.codeplex.com lets you the highest performance possible out of Excel.
LikeLike
Hi kalx, I’m sure these things have their place, but what I am interested in is getting all the heavy computational work done in whatever compiled code does the job best, and transfering the data from and to Excel with the minimum of effort. If I can do this without VSTO or Excel-DNA, or even xll.codeplex, then that’s a good thing as far as I’m concerned. If these tools offer substantial advantages (and I’m sure they do to some people), then they are worth the time and effort of learning, but for my way of working I don’t see what the advantage would be.
LikeLike
How the human brain works is far more fascinating than how computers work. Even seemingly rational people make decisions first, then find justifications later.
I like VBA and use that when appropriate. It solved a problem for a client that xll’s and VSTO could not using only a few lines of code. (BTW, it involved Auto_Open. Took me a while to find out Workbook_Open does not work as advertised.)
The computation heavy hitters seem to still be using Fortran. Not Fortran 77, but 90 or 95. If you need to get numbers back and forth from Excel, the FP datatype wins hands down and that is only available via the C SDK.
My reason for learning C++? A coder that I looked up to making fun of VBA by asking me what the B in BASIC stood for. I didn’t want to be that.
LikeLike
Hi Keith/kalx – I’m not sure what you’re point is. Using the technique described I get (for example) an Excel UDF that will solve large systems of simultaneous equations about 40 times faster than using the built-in MInverse and MMult functions, or about 120 times faster than a pure VBA solution. That takes 4 lines of code in VBA and 4 lines of code in C#, and importantly for me, I can copy the C# code from provided examples, so the learning curve is minimal.
What will I gain by using a different approach?
LikeLike
The C SDK provides higher performance, I believe. But computers are fast and RAM is cheap these day.
Interesting read about people that need to solve big problems: http://queue.acm.org/detail.cfm?id=1820518
LikeLike
Interesting link thanks Keith.
The message I’d take from that is that even in the context where computing performance is everything, performance of the compiled code is not everything.
LikeLike
And having people think carefully about the code they are writing is more important than the language they use.
You might get a kick out of a gadget I wrote for doing Monte Carlo simulation in Excel: http://tukhi.com.
LikeLike
“You might get a kick out of a gadget I wrote for doing Monte Carlo simulation in Excel: http://tukhi.com.”
Looks good – I’ll have a good play with it when I have time.
LikeLike
Hi Doug – I’d like to two more links that might be relevant to this post.
This is a Ed Parcell’s take on how to hook up AlgLib to Excel via Excel-DNA: http://blog.edparcell.com/tutorial-numerical-analysis-in-excel-using-c.
And if you’re interested in moving some VBA UDF code to VB.NET (mainly for improved calculation speed, but sometimes for .NET integration to use other .NET libraries, Patrick O’Beirne has written a great guide to get started, and a helper tool to help prepare your VBA: http://sysmod.wordpress.com/2012/11/06/migrating-an-excel-vba-add-in-to-a-vb-net-xll-with-excel-dna-update/. With some care, a lot of your VBA code can be written to run as VBA or VB.NET without changes, the latter quite a bit faster and no more difficult to deploy (a single .xll file).
Cheers – Govert
LikeLike
Thanks for the links Govert. I’ve seen the Ed Parcell one before, and I’ve been meaning to follow it up, but the VB.Net I hadn’t seen, and it should be very useful.
LikeLike
Pingback: Compiled Matrix Functions for Excel | Newton Excel Bach, not (just) an Excel Blog