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