Tuesday, January 31, 2012

Run (C#) .NET Assembly inside of Excel Spreadsheet

(1) make the .NET Assembly COM Compatible (check the template at the bottom of this post)
(1.1) make it a Class Library (project's properties -- Application)
(1.1) make it build for interop (project's properties -- Build)
(2) use regasm to register the assembly
(3) use regasm to create the TLB file,

regasm test1.dll /tlb:test1.tlb /codebase

(4) from Excel's VBA editor, go to Tools->Reference. Add the TLB file by browsing
(5) you can now use your .NET Assembly.

example VBA code

Dim myObject AS NEW MyDotNetClass
myObject.MyDotNetMethod()

Note:
- generate your own Guid (on python: uuid.uuid4(); on Visual Studio 2005 Tools->Create Guid)
- change the ProgId

 ----- begin code -----


using System;
using System.Runtime.InteropServices;
using System.Windows.Forms;

namespace Project
{
    [Guid("363576cf-42cc-415f-bed8-70b5fa524da3"),
    InterfaceType(ComInterfaceType.InterfaceIsDual),
    ComVisible(true)]
    public interface ITest3
    {
        string Hello();
    }

    [Guid("b9201a23-5474-4d22-b82a-7c6b0ff53bd8"),
     ClassInterface(ClassInterfaceType.None),
     ComVisible(true),
     ProgId("Project.Test3"),
     ComDefaultInterface(typeof(ITest3))]
    public class Test3 : ITest3
    {
        public Test3()
        {
        }

        public string Hello()
        {
            return "yooo hello";
        }


    }


}


----- end code -----