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"
' 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()
Public Sub SortArray(ByRef arTemp() As String)
Now open the Excel VBA Editor and add the TLB file to References.
The following code will now use the SortArray .NET Function created
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"
Set Cls1 = Nothing