Section 5.3.Use .NET Components


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


Note: .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.

5.3.1. 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:

  1. From within the 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 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


  3. Click OK to close the Reference 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 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 "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 once you've learned more about the classes that come with the .NET Framework. For example, you can do some pretty useful things with even the basic .NET String and Array classes as shown here:

    ' .NET code    Public Class NetString            + COM GUIDS            ' A creatable COM class must have a Public Sub New(  )         ' with no parameters, otherwise, the class will not be         ' registered in the COM registry and cannot be created         ' via CreateObject.        Public Sub New(  )            MyBase.New(  )        End Sub            Public Function Split(ByVal arg As String, _          Optional ByVal sep As String = " ") As String(  )            If Len(sep) <> 1 Then _              Throw New Exception("Separator must be one character long")            Return arg.Split(CType(sep, Char))        End Function            Public Function Join(ByVal arg(  ) As String, _          Optional ByVal sep As String = " ") As String            If IsArray(arg) Then                If arg.Rank <> 1 Then _                  Throw New Exception("Array must have one dimension")            Else                Throw New Exception("First argument must be an array")            End If            Return String.Join(sep, arg)        End Function        Public Function Sort(ByVal arg As String, _          Optional ByVal ascending As Boolean = True) As String            ' Declare an array.            Dim ar(  ) As String            ' Break the string up and put it in the array.            ar = arg.Split(" "c)            ' Sort the array.            ar.Sort(ar)            ' Reverse the order if requested.            If Not ascending Then ar.Reverse(ar)            ' Convert the array back to a string and return it.            Return String.Join(" ", ar)        End Function    End Class

To use the preceding .NET class in code, compile the project and establish a reference to that project in the VBA Editor, then write code similar to the following:

    ' Excel code    Sub TestNetString(  )        Dim str As String, ar(  ) As String, i As Integer        Dim NetStr As New NetForExcel.NetString        str = "Some random text that you'd want to sort."        Debug.Print NetStr.Sort(str)        ar = NetStr.Split(str)        For i = 0 To UBound(ar)            Debug.Print ar(i)        Next    End Sub

5.3.2. How it works

The preceding code displays the sorted string in the Immediate window, then splits the string in to an array and displays it one word at a time. Since Visual Studio .NET generates a type library for the component and registers it with your system, you automatically get Intellisense and autocomplete features when you work with .NET objects in VBA as shown in Figure 5-5.

Figure 5-5. .NET objects registered for COM automatically get Intellisense and autocomplete in VBA




    Excel 2003 Programming. A Developer's Notebook
    Excel 2003 Programming: A Developers Notebook (Developers Notebook)
    ISBN: 0596007671
    EAN: 2147483647
    Year: 2004
    Pages: 133
    Authors: Jeff Webb

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net