Create DLL file in VB.NET and use in Excel VBA

Question

I have created a DLL file in VB.NET and I want use it in Excel VBA. When I use it like a function it is working perfect but when I use sub with a ByRef variable it does not work and Excel restarts with an error.

The code in VB.NET is:

Public Function distinctArr(ByVal arr As String()) As String()
        Return arr.ToList.Distinct.ToArray
End Function

Public Sub sortArr(ByVal arr As String(), ByRef a As String())
    Dim tolist As List(Of String) = arr.ToList
    tolist.Sort()
    a = tolist.ToArray
End Sub

This is the code in VBA:

Dim objMda As Excelcode.mda
Set objMda = New Excelcode.mda
Dim distinc_Item() As String
Dim all_Items() As String
all_Items = rng_to_string(rng_rizmetre)
distinc_Item = objMda.distinctArr(all_Items) '''This line is working perfect
Dim Sorted_Item() As String
objMda.sortArr distinc_Item, Sorted_Item

What is wrong with the code?

What is the error? "does not work" doesn't mean anything. Did you reference the type library in VBA? Where and how is objMda declared and assigned? Does the VB.NET code have the required type and member attributes? Do you see your VB.NET API in VBA's object browser? Have you tried passing the arrays As Object and ByRef? Is your VB.NET code catching any exception? Have you tried debugging the VB.NET code by attaching to the EXCEL.EXE process? Do you know that you could use System.Collections.ArrayList directly in VBA for this? — Mathieu Guindon
– Mathieu Guindon, Commented May 31, 2017 at 16:11
the error is "Microsoft Excel stop working"...when i use fuction widout any byref variable in the library the library is working perfect but when i use byref variable in library Excel stop working and restart excel. — MDA
– MDA, Commented May 31, 2017 at 16:42
"Microsoft Excel stopped working" is because your DLL is throwing an exception without catching it. You need to attach the VS debugger to the running EXCEL.EXE process and see what the exception is. Consider exposing a Function instead of returning your result with a ByRef parameter. — Mathieu Guindon
– Mathieu Guindon, Commented May 31, 2017 at 16:47
BTW, no need to convert it to list. just Return arr.Distinct.ToArray and a = arr.ToArray : Array.Sort(a) — Slai
– Slai, Commented May 31, 2017 at 17:07
Yes, we got that part. You need to debug your VB.NET dll when it is used through VBA. To do that you need to attach the VB.NET / Visual Studio debugger to the running EXCEL.EXE process, place a breakpoint on the first executable statement, execute the failing VBA code (which calls your dll), and then you can step through the VB.NET code and inspect what VBA has been giving you. The VB.NET code is throwing an exception, and without knowing what that exception is, you're working blindly. — Mathieu Guindon
– Mathieu Guindon, Commented May 31, 2017 at 17:52

MDA · Accepted Answer · 2017-06-08 10:25:26Z

1

Finally i can find my answer. code in vb.net

Public Class MainClass
    Sub sortArr(ByVal arr As String(), ByRef sortedarr As String())
        sortedarr = arr
        Array.Sort(sortedarr)

    End Sub
End Class

and code in excel vba:

Sub aaa()
Dim Mycode As excelcode.MainClass
Set Mycode = New excelcode.MainClass
Dim arr(2) As String
arr(0) = "m"
arr(1) = "a"
arr(2) = "d"
Dim sortedArr() As String
ReDim sortedArr(0)
  Mycode.sortArr arr, sortedArr
End Sub

by this code i can pass array byval to vb.net dll then vb.net pass sorted array.

edited Jun 8, 2017 at 10:25

answered Jun 8, 2017 at 10:00

MDA

1132 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create DLL file in VB.NET and use in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related