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.
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 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.