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.