How to reference DLL created with DllExport into Excel VBA

[supertrip86]

. . .

The question is related to:

• DllExport - latest:

---

Hi, my goal is to create a windows unmanaged DLL to be loaded into Excel VBA, without the need to select "Register for COM Interop" in Visual Studio, and without having to use regasm to register the DLL. This because the DLL I intend to create needs to be deployed on a machine where I cannot install Visual Studio, and where I probably won't have administrator's privileges.

I have a single project in VB.NET, where I have installed the DllExport NuGet package, and set "x86" anywhere in my solution rather than "AnyCPU".

Here is the code:

`

  Imports System.Runtime.InteropServices
  Imports net.r_eg.DllExport
  Public Class Matrix

  <DllExport>
  Public Shared Function MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC(ByRef DATA_RNG As Double(,))

      Dim i As Long
      Dim j As Long

      Dim NROWS As Long
      Dim NCOLUMNS As Long

      Dim TEMP_SUM As Double
      Dim DATA_MATRIX As Double(,)

      On Error GoTo ERROR_LABEL

      DATA_MATRIX = DATA_RNG

      NROWS = UBound(DATA_MATRIX, 1)
      NCOLUMNS = UBound(DATA_MATRIX, 2)

      TEMP_SUM = 0
      For j = 1 To NCOLUMNS
          For i = 1 To NROWS
              TEMP_SUM = TEMP_SUM + DATA_MATRIX(i, j)
          Next i
      Next j

      MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC = TEMP_SUM

      Exit Function
  ERROR_LABEL:
      MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC = Err.Number
  End Function
End Class

` I followed the indications in the first 1:34 minutes of this video: https://www.youtube.com/watch?v=sBWt-KdQtoc

And then built the solution. No errors till this point.

Eventually, I added this delcaration on top of a VBA module:

Public Declare Function MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC Lib "C:\path\to\file.dll" (ByRef DATA_RNG As Variant)

I am trying to test this function, but I can't seem to be able to make it work. Unfortunately I have no experience with VBA, can anyone tell me what am I doing wrong?

[3F]

Hello,

I am trying to test this function, but I can't seem to be able to make it work.

What error?

Unfortunately I have no experience with VBA, can anyone tell me what am I doing wrong?

I only have LibreOffice to test something but I'm not sure about compatibility or similarity to MS products regarding VBA.

Anyway, did you try something from other issues about VBA https://github.com/3F/DllExport/issues?q=is%3Aissue+vba

[supertrip86]

Hi, thank you for your reply. The error I am getting is the following, along with the code I am using to call the method from the VB.NET dll:

Why can't VBA find the entry point? Am I missing anything? I read the other threads you linked me, but none reported the same error I am having. Plus, they are about C#, not VB.NET.

Would you mind providing some assistance? I would be very grateful

[3F]

Plus, they are about C#, not VB.NET.

C# and VB.NET both are CLR based thus it does not matter because it just frontend.

Can't find DLL entry point

What result for the following command ?

DllExport -pe-exp-list MonteCarlo\bin\x86\Debug\MonteCarlo.dll

[supertrip86]

C# and VB.NET both are CLR based thus it does not matter because it just frontend.

My bad, I am still a Junior :)

Can't find DLL entry point

What result for the following command ?

DllExport -pe-exp-list MonteCarlo\bin\x86\Debug\MonteCarlo.dll

Here is the result:

[3F]

@supertrip86 I'm a little confused with your code. Here you're trying to call MonteCarlo() declared function that looks like it must return some specified object because you're calling later MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC on its state.

But your initial code just declares a simple static function as

<DllExport>
Public Shared Function MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC(ByRef DATA_RNG As Double(,))

DllExport -pe-exp-list confirms export

So, I'm not really sure what are you trying here. Can you attach any complete src or project example?

Isn't this is what you need ?

Declare Function MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC Lib ...

Also,

About ByRef. Do not pass arrays like here. Either configure marshaling or use pointer to allocated region; maybe manually or using something like Conari etc. Please read this https://github.com/3F/DllExport/wiki/Quick-start#about-data-types

My bad, I am still a Junior :)

I suggest you MSDN or any Dev Q&A such as stackoverflow

[supertrip86]

Hi, thank your your patience. What I need is to be able to call several functions from the MonteCarlo DLL within VBA (MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC is only one of those functions). Nothing more than that.

So far, I only added MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC as a static method, just to to see if I can manage to call it from within VBA. If I succeed, I will add the rest. The ultimate goal is to be able to call each of these functions from within Excel ( pass a range of cells to MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC as arguments and return the result, for example).

Now, I followed your advice and tried the code below, but if I run it all Excel crashes... No errors are provided, the entire Excel freezes and I have to manually terminate it.

Declare Function MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC Lib "C:\path\to\MonteCarlo.dll" (ByRef DATA_RNG As Variant) As Double

Sub RunTest()

      Dim result As Double
      Dim DATA_RNG(2, 2) As Variant

      DATA_RNG(1, 1) = 1
      DATA_RNG(1, 2) = 2
      DATA_RNG(2, 1) = 1
      DATA_RNG(2, 2) = 2

      result = MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC(DATA_RNG)

End Sub

Is it because I am using ByRef? I understand you adviced me not to use it. Is this the reason?

[3F]

Is it because I am using ByRef? I understand you adviced me not to use it. Is this the reason?

Quite possible

if I run it all Excel crashes... No errors are provided, the entire Excel freezes and I have to manually terminate it.

So what exactly, crashes or freezes ? Can you also copy everything from DllExport window Data tab just to exclude some problems like this. But I think it may freeze due to an incorrect values from memory according UBound use.

In any case just marshal it correctly IF it cannot be marshaled automatically by CLR.

MarshalAs attribute, or custom implementation around Marshal.Alloc and so on

[supertrip86]

As you can see from the screenshot below, MS Excel and the VBA editor gets stuck (not responding), and I have to terminate them.

Here is the log from the Data tab:

Installed: True; 1.7.4+c1cc52f; invoked: 1.7.4
Project type: Vb
Storage: ProjectFiles
Compiler.Platform: x86
Compiler.ordinalsBase: 1
Compiler.rSysObj: False
Compiler.ourILAsm: False
Compiler.customILAsm: 
Compiler.genExpLib: False
Compiler.peCheck: PeIl
Compiler.patches: None
PreProc.Type: None
PreProc.Cmd: 
PostProc.Type: None
PostProc.ProcEnv: $(SolutionPath);$(MSBuildThisFileFullPath)
PostProc.Cmd: 
SignAssembly: 
Identifier: 9959C632-8E6F-4981-8736-E6BCAED4778F
Instance: C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\MSBuild\Current\Bin\amd64\MSBuild.exe
Project path: C:\Users\giogi\path\to\MonteCarlo\MonteCarlo.vbproj
Action: Configure
PlatformTarget: x86
TargetFramework: 
TargetFrameworks: 
TargetFrameworkVersion: v4.7.2
RootNamespace: MonteCarlo
AssemblyName: MonteCarlo
MgrArgs: 
MetaLib: tools\raw\lib\net20\DllExport.dll
MetaCor: tools\raw\lib\netstd\DllExport.dll
Proxy: 
StoragePath: .net.dllexport.targets
ddNS: net.r_eg.DllExport
ddNS max buffer: 500
UseCecil: True
intermediateFiles: False
timeout: 30000
Options: None
RootPath: C:\Users\giogi\path\to\MonteCarlo\
PkgPath: C:\Users\giogi\path\to\MonteCarlo\packages\\DllExport.1.7.4\
SlnFile: 
SlnDir: C:\Users\giogi\path\to\MonteCarlo\
DxpTarget: tools\net.r_eg.DllExport.targets
MsgGuiLevel: -1
LockIfError: 

If you confirm there is nothing else that can be wrong, I will start studying about marshaling using the link you provided me.

Many thanks!

[3F]

I don't see the problems for your configuration and used environment.

Did you try configure marshaling at least by using MarshalAs attribute as said above?

For better understanding try define ranges manually without For/UBound, etc. Try to keep everything as simple as possible in order to debug something.

I am not guru in VBA (last time I used this seems in ~2006) and I don't have MS Excel on my machines to debug your script at runtime.

But definitely For+UBound are not safe in your code as I can see here. This can lead to a moving out of bounds of the allocated memory region due to incorrect marshaling (affects entirely incorrect values and addresses) etc. Which may produce exactly some freezes, I think.

Also, what about VBA's multidimensional ByRef along with LongPtr to a single-dimensional array def in a CLR side, like

Declare PtrSafe Function MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC Lib "C:\path\to\MonteCarlo.dll" 
(ByRef DATA_RNG As LongPtr) As Double

Public Shared Function MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC(
    <MarshalAs(UnmanagedType.LPArray, ArraySubType:=UnmanagedType.R8, SizeConst:=4)> ByVal r As Double()) As Double

    Dim DATA_RNG As Double(,) = { {r(0), r(1)}, {r(2), r(3)}}

    ' DATA_RNG(0, 1)
    ' DATA_RNG(1, 0)

I will start studying about marshaling using the link you provided me.

Definitely a huge recommendation in any case since mechanism will be similar even between other languages.

[supertrip86]

Hi, thank you again for your patience. I read about marshaling and why it needs to be used, however it seems quite complicated to use...

These are the modifications I made to my code. I also removed UBound, to see if there could be improvements. Here is VB.NET:

Imports System.Runtime.InteropServices
Imports net.r_eg.DllExport

Public Class Matrix
    <DllExport>
    Public Shared Function MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC(<MarshalAs(UnmanagedType.LPArray, ArraySubType:=UnmanagedType.R8, SizeConst:=4)> ByVal r As Double()) As Double

        Dim i As Long
        Dim j As Long

        Dim NROWS As Long
        Dim NCOLUMNS As Long

        Dim DATA_RNG As Double(,) = {{r(0), r(1)}, {r(2), r(3)}}

        Dim TEMP_SUM As Double
        Dim DATA_MATRIX As Double(,)

        On Error GoTo ERROR_LABEL

        DATA_MATRIX = DATA_RNG

        'NROWS = UBound(DATA_MATRIX, 1)
        'NCOLUMNS = UBound(DATA_MATRIX, 2)

        NROWS = 2
        NCOLUMNS = 2

        TEMP_SUM = 0
        For j = 1 To NCOLUMNS
            For i = 1 To NROWS
                TEMP_SUM = TEMP_SUM + DATA_MATRIX(i, j)
            Next i
        Next j

        MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC = TEMP_SUM

        Exit Function
ERROR_LABEL:
        MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC = Err.Number
    End Function

End Class

And here is VBA:

Declare PtrSafe Function MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC Lib "C:\Users\path\to\MonteCarlo.dll" (ByRef DATA_RNG As LongPtr) As Double

Option Explicit     'Requires that all variables to be declared explicitly.
Option Base 1       'The "Option Base" statement allows to specify 0 or 1 as the
                    'default first index of arrays.

Sub RunTest()
  Dim result As Double
  Dim DATA_RNG(2, 2) As LongPtr

  DATA_RNG(1, 1) = 1

  DATA_RNG(1, 2) = 2
  DATA_RNG(2, 1) = 1
  DATA_RNG(2, 2) = 2

  result = MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC(DATA_RNG)

End Sub

Not sure why, even though I declared DATA_RNG as LongPtr, it gives me "data mismatch"...

[3F]

Try

Dim DATA_RNG(2, 2) As Double
' ...
result = MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC(DATA_RNG)

continue to keep ByRef LongPtr + Marshalable ByVal r As Double declaration

Declare PtrSafe ... (ByRef DATA_RNG As LongPtr) As Double

Public Shared Function MATRIX_ELEMENTS_CUMULATIVE_SUM_FUNC(
    <MarshalAs(UnmanagedType.LPArray,...> ByVal r As Double()) As Double

I'm not sure about VBA but without COM you need obviously pass it as address to its allocated memory in the heap.

But after, CLR will marshal ByVal Double according some information, for example, configured through MarshalAs attribute. There, however, you can also marshal it independently through some other technic but I think for VBA this will be hard.

Alternatively you can also try to get address to allocated DATA_RNG manually to work only with LongPtr/IntPtr but this is another level for ascetic VBA.

[Zeki-Gursoy]

@supertrip86, You have two options:

• Net functions' input parameter type must ByVal Object. This is safe. Or,
• VBA array data type and Net function's input type must same with ByRef.