How to call .Net library from Excel

This method has been verified using Visual Studio 2010 and MS Office 2010. It works!!

(Reference: http://richnewman.wordpress.com/2007/04/15/a-beginner%E2%80%99s-guide-to-calling-a-net-library-from-excel/ )

(1) Create a public .Net class and have a public method therein. ClassInterface will allow ‘intelligence’ feature on VBA, so it is useful to have. For example:

    Imports System
    Imports System.Collections.Generic
    Imports System.Text
    Imports System.Runtime.InteropServices

    ClassInterface(ClassInterfaceType.AutoDual)_
    Public Class DotNetClass
      Public Sub DotNetMethod(ByRef input As String, ByRef output As String)
        output = "Hello" + input
      End Sub
    End Class

    Let’s assume the filename is Test.dll

(2) On the project Assembly Information, give a check to “Make assembly COM-Visible”

(3) Build library

(4) On Excel , create VBA code for testing. For example,           

   Sub Button1_Click()
   Dim testClass As New DotNetClass
   Dim inp As String
   Dim out As String
   inp = "World"
   Call testClass.DotNetMethod(inp, out)
   MsgBox (out)
   End Sub

(5) For deployment to others who doesn’t have a programming environment, the deliverables are Test.dll only.

(6) To register all public classes contained in Test.dll, and generates and registers the type library Test.tlb, which contains definitions of all the public types defined in Test.dll. Open Command Window, Use following command for registration:

  C:\windows\microsoft.net\Framework\v4.0.30319\regasm.exe /codebase Test.dll /tlb: Test.tlb

An important advice is that you must use 32bit version of regasm.exe in .Net 4.0 even if your computer is 64 bit. It is because .NET library is compiled for 32 bit (in VS2010).