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


2 comments:

  1. Thanks that's a great idea.

    ReplyDelete
  2. Hi , I did similar code but getting error saying ActiveX cant create object. Can you fix that ?

    ReplyDelete

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.