|
5.3. Use .NET ComponentsVisual 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 itOnce you compile a .NET component with Register for COM Interop enabled, using that component from Excel is simply a matter of following these steps:
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 worksThe 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 |
|