Hello Anbu,
You can call .Net component from VBA.
Here is the summery of what I did to achieve this.
(1) Created C# class library project which has a class definition as
follows,
using System;
using System.Runtime.InteropServices;
using Microsoft.Win32;
namespace TestClassLibraryForExcel
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class MyFunctions
{
public MyFunctions()
{
}
// this is my user defined function
public double LuckyNumber()
{
return (7);
}
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type),false);
}
private static string GetSubKeyName(Type type)
{
string s = @"CLSID\{" + type.GUID.ToString().ToUpper() +
@"}\Programmable";
return s;
}
}
}
(2) Gave full trust to the assembly using the .Net framework wizards
(3) Registered the managed assembly for COM Interop in the registry by
using the assembly registration tool (Regasm.exe) with the /TLB and
/CODEBASE options to register the .NET Component for COM Interop and
creates a type library and a COM Callable Wrapper (CCW) Interop assembly.
To know more about the Regasm tool you may refer the following article
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cptools/htm
l/cpgrfassemblyregistrationtoolregasmexe.asp
Once a class is registered, any COM client can use it as though the class
were a COM class
You may install the assembly to the global assembly cache using the Global
assembly cache tool
(
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cptools/ht
ml/cpgrfGlobalAssemblyCacheUtilityGacutilexe.asp) if you do not want to use
the /CODEBASE switch to the RegAsm tool.
(5) Created a workbook and activated the Visual basic editor and added the
reference to the TestClassLibraryForExcel from Tools->References menu.
(6) Now, you may create object of TestClassLibraryForExcel same as a normal
COM object.
I hope this helps you!
Regards,
Uday Takbhate [MSFT]
Microsoft Developer Support
--------------------
