Sunday, April 27, 2008

How to use .Net Array.Sort Function in VBA

Using Vb.Net Function in VBA








How to use a VB.Net DLL/TLB in Excel VBA


Here is an example for using the customized .NET Function in Excel VBA. Unfortunately EXcel VBA doesn't have a Array.Sort function. To overcome the shortcomings, we create our own function here in .Net and use the same in Excel VBA

Here are the steps:


  1. Create a class library project in Visual Studio
  2. Add a COM Class item (DND_SortArray in this example)
  3. In the assembly information edit the title, company and provide a meaningful description. This would be seen in the References dialog in Excel
  4. Add the code shown below:


_
  1. Public Class DotNetDud_SortArray

    #Region "COM GUIDs"

    ' These GUIDs provide the COM identity for this class

    ' and its COM interfaces. If you change them, existing

    ' clients will no longer be able to access the class.

    Public Const ClassId As String = "93534c94-9fc1-4a54-b022-338fa7d454c1"

    Public Const InterfaceId As String = "03787ed3-bc65-41a1-9053-d37f390ff94b"

    Public Const EventsId As String = "34d12c14-8afd-44b7-a987-fc2f909724b6"

    #End Region

    ' 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 Sub SortArray(ByRef arTemp() As String)

    Array.Sort(arTemp)

    End Sub

    End Class

Compile the Project. You will get a DLL and a TLB.

Now open the Excel VBA Editor and add the TLB file to References.

The following code will now use the SortArray .NET Function created

Sub Use_DotNet_Sort()

Dim Cls1 As DotNetDud_SortArray.DotNetDud_SortArray

Set Cls1 = New DotNetDud_SortArray.DotNetDud_SortArray

Dim arTemp(0 To 2) As String

arTemp(0) = "Bottle"

arTemp(1) = "Apple"

arTemp(2) = "Aaron"

Cls1.SortArray arTemp

Set Cls1 = Nothing

End Sub


1 comment:

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.