Use .NET Components in Excel
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 Excel 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 25-4, and click OK to add a reference to that component.
Figure 25-4. Use the .NET component’s type library to create a reference to the component in Excel VBA
Click OK to close the References 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 Visual Basic .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 "I worked!" End Sub
Now, if you run the preceding code, Excel uses the type library to start the .NET
assembly and invoke the Test
method with a string argument. The .NET component, in turn, displays a message box saying “I worked!”
Though that demonstration isn’t very impressive, what you can do with .NET components becomes exciting ...
Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.