Use .NET Components
Visual Studio .NET projects that contain COM classes can be used from Excel VBA, or any other COM-based programming language even though they are .NET executables.
.NET components that contain COM classes show up in the References dialog box in VBA. You can add a reference and use those components much as you would any other component.
How to do it
Once you compile a .NET component with Register for COM Interop enabled, using that component from Excel is simply a matter of following these steps:
From within the VBA editor, select References from the Tools menu. VBA displays the References dialog box.
Click Browse and navigate to the /bin folder for the .NET component you wish to use. Select the type library (.tlb) for the component as shown in Figure 5-4, and click OK to add a reference to that component.
Figure 5-4. Use the .NET component’s type library to create a reference to the component in VBA
Click OK to close the Reference dialog box.
Declare an object variable for the .NET class using the New keyword, then call the members of the class.
The components you create using VB.NET are named using their project name (.NET calls that the namespace of the component), so you would use the following code to call the NetForExcel project’s NetObject created in the preceding section:
' Excel code Sub TestNetObj( ) Dim x As New NetForExcel.NetObject x.Test ...