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:

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

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

VBA Tips & Tricks