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
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
Thanks that's a great idea.
ReplyDeleteHi , I did similar code but getting error saying ActiveX cant create object. Can you fix that ?
ReplyDelete