Programming Excel with VBA and .NET

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:

1. From within the Excel VBA Editor, select References from the Tools menu. VBA displays the References dialog box.

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

3. Click OK to close the References dialog box.

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