calling fortran dll from VBA

Question

I am using gfortran compiler (under Simply Fortran) for Windows 64 and when creating a basic fortran dll for testing I cannot run it under VBA and got then runtime error 48 : the dll cannot be found.

here is my fortran subroutine code :

subroutine multiply(x, y, z)

!DEC$ ATTRIBUTES DLLEXPORT :: multiply
!DEC$ ATTRIBUTES ALIAS : "multiply" :: multiply

real, intent(in):: x, y
real, intent(out):: z

z = x * y

end subroutine multiply

I create the library typing : gfortran -shared -omultiply.dll multiply.f90 this library is located in "C:\Users\Olivier\Documents\Fortran\"

and my VBA code (I am using VBA 7.0) :

Declare Sub multiply Lib "C:\Users\Olivier\Documents\Fortran\multiply.dll" (x As Single, y As Single, ByRef z As Single)

Sub test()

Dim x As Single
Dim y As Single
Dim z As Single

x = 2
y = 3

Call multiply(x, y, z)

Cells(1, 1) = z

End Sub

When running this VBA code, it says it cannot find multiply.dll whereas this file is in the correct file mentioned in the declaration, if anyone could help please !

Thank you in advance

Have you seeen this link: http://stackoverflow.com/questions/8606740/vba-cannot-find-my-dll-despite-hardcoding-location? — varocarbas, Aug 05 '13 at 20:13
Also just to reduce sources of error, I would put the dll in the same directory than the spreadsheet and reduce the name to "multiply.dll" — varocarbas, Aug 05 '13 at 20:15
I have tried to put the dll in the same directory than the excel file but it didnt change anything unfortunately. — Olivier, Aug 05 '13 at 20:23
I have just read the link and downloaded Dependency Walker, so the solution of my problem would be to remove all dependencies of my dll ? — Olivier, Aug 05 '13 at 20:25
If not removing, putting them such that your dll can access them. Call your dll from other program and replicate the exact conditions with Excel to make sure that you are missing anything. — varocarbas, Aug 05 '13 at 20:27
sorry you lost me, how could I call my dll from other programs ? From now I used a lot to call Fortran dll under R and it worked fine I didnt have anything else to do. — Olivier, Aug 05 '13 at 20:30
(?!) when you call this dll from R you are doing exactly the same than from Excel? (the dll is located in the same folder and call with the same path). In principle, any calling program should have problems with dependencies, if R does not have these problems under the same conditions, perhaps this is not the problem. — varocarbas, Aug 05 '13 at 20:32
yes you are right that's why I was very surprised it didnt work the same under VBA...when opening this dll with dependency walker here is what I get, maybe it could help : Error: At least one module has an unresolved import due to a missing export function in an implicitly dependent module. Error: Modules with different CPU types were found. — Olivier, Aug 05 '13 at 20:35
I haven't written anything in FORTRAN since 3 years ago (and haven't ever used "Dependency Walker") so I am not the best one to help on this front :) — varocarbas, Aug 05 '13 at 20:39
Ignore the DependencyWalker issues for now, they're probably not the problem. First, make sure your DLL is built for 64-bit, not 32-bit. Second, set the VB project platform to "x64", not "Any CPU". This is done in Properties > Compile. You'll probably want to link your Fortran DLL against static libraries (I don't know the gfortran way of doing that.) — Steve Lionel, Aug 05 '13 at 21:01
My dll is built for 64-bit, which software are you taking about when you say : set the VB project platform to "x64", not "Any CPU" ? Is it Visual Studio ? Cause I am not using it, I am using VBA for Excel and Simply Fortran — Olivier, Aug 05 '13 at 21:41
Are you using 64 bit Excel or 32 bit Excel? Compiler directives for GCC start with !GCC$, not with !DEC$. — IanH, Aug 05 '13 at 22:59
Ah, VBA for Excel. You want to build the DLL for 32-bits then. IanH also has a good point regarding the directives. Since VBA wants a 32-bit DLL, you also need to specify STDCALL and REFERENCE in the directives. — Steve Lionel, Aug 06 '13 at 01:40
You're missing the alias. Use depends (dependency walker - just drop the dll in it) to find out what the external names are, put that name in quotes and alias what you wish to call it. — cup, Aug 06 '13 at 06:06
Oh VBA needs 32-bits DLL really...I missed that point thanks a lot for noticing me that, also need to change the compiler directives then, many thanks guys I will try that tonight. — Olivier, Aug 06 '13 at 07:00

score 0 · Answer 1 · answered Aug 06 '13 at 10:20

0

Have you tried with the LoadLibrary-API?

Private Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" ( _
    ByVal lpLibFileName As String) As Long

Declare Sub multiply Lib "multiply.dll" ( _
    x As Single, _
    y As Single, _
    ByRef z As Single)

'Call LoadLibrary ONCE!!!! before calling 'multiply', e.g. on opening the workbook
LoadLibrary "C:\Users\Olivier\Documents\Fortran\multiply.dll"

Regards,

AKDA

answered Aug 06 '13 at 10:20

AKDADEVIL

206
1
7

It looks like a good idea but I have no idea how to create this kind of LoadLibrary function :( – Olivier Aug 06 '13 at 19:48
ok LoadLibrary is a function included in the kernel32 library, sorry I didn't realize that as I never used it, I have done as you said but I still get the same error 53, it cannot find multiply.dll :( – Olivier Aug 06 '13 at 21:45
Did you change the declaration of the multiply method like I did in the example, removing the path and just put **Lib "multiply.dll"** as source? You should also check the return value of LoadLibrary (should be <> 0) and Err.LastDLLError (should be 0), maybe your DLL can't be loaded in the first place. I use this method to call functions from C++-DLLs created in Visual Studio in MS Access and it works 100% **if** the DLL can be loaded correctly. – AKDADEVIL Aug 07 '13 at 03:28
I have done exactly as you said but I get : runtime error 453 can't find dll entry point – Olivier Aug 07 '13 at 22:21
I think it comes from my GCC$ ATTRIBUTES which are wrong, I have : !GCC$ ATTRIBUTES DLLEXPORT:: multiply – Olivier Aug 07 '13 at 22:29
also adding !GCC$ ATTRIBUTES STDCALL:: multiply leads to the same runtime error 453 – Olivier Aug 07 '13 at 22:30

score 0 · Answer 2 · edited Sep 16 '14 at 20:02

My Excel 2010 is indeed the 32-bits version so I have built the 32-bits DLL multiply with STDCALL :

subroutine multiply(x, y, z)

    !GCC$ ATTRIBUTES STDCALL :: multiply

    real , intent (in) :: x, y  
    real , intent (inout) :: z  

    z = x * y

end subroutine multiply

when I open this DLL with Dependency Walker it says it cannot find LIBGCC_S_SJLJ-1.DLL, I have no idea how to solve this problem using the ALIAS and when running under VBA I get the runtime error 53 now, please if anyone could help, this is far beyond my abilities, it was so easy to link fortran dll under R, I would have never imagined it was so complicated to do the same under VBA :(

*LIBGCC_S_SJLJ-1.DLL* is in the *bin* directory of MinGW, juste add this directory to the [system path](http://windowsitpro.com/systems-management/how-can-i-add-new-folder-my-system-path) and restart Excel. — , Jul 02 '14 at 05:58

score 0 · Answer 3 · answered Sep 16 '14 at 18:41

I have had the same problem and it bothered me until I found a solution. If you look at the dependency walker there is missing referenced dll, most likely from the debug environment. Mine was DFORTD.DLL.

The solution is to

Either compile as release and make sure all dependent dlls are available
Or compile with static libraries /libs:static parameter or for me specifying in settings Fortran/Library/Debug Single-threaded.

In the end, make sure there are no missing dependencies in your dll

Depends

Matti Wens · Answer 4 · 2016-11-15T11:55:02.317

I have successfully populated a 2D array of Longs from a toy DLL function, calling from Excel VBA using the following approach.

Fortran Code:

    Subroutine FortranDLL(Array1, dim1, dim2)
    Implicit None
    Integer :: dim1, dim2
    Integer :: Array1(1:dim1, 1:dim2)
    Integer :: i, j
    do i=1,dim1
        do j=1,dim2
            Array1(i,j)=(10*i) + j
        end do
    end do
    End Subroutine FortranDLL

Compiled using Code::Blocks with:

    mingw32-gfortran.exe -Jobj\Release\ -mrtd -fno-underscoring  -Wall -DBUILD_DLL -O2     -c D:\Test2\main.f95 -o obj\Release\main.o
    mingw32-gfortran.exe -shared -Wl,--output-def=bin\Release\libTest2.def -Wl,--out-implib=bin\Release\libTest2.a -Wl,--dll  obj\Release\main.o  -o bin\Release\Test2.dll -s

Note particularly the use of -mrtd and -fno-underscoring when compiling.

The Excel VBA is then:

    Option Explicit
    Option Base 1

    Declare Sub fortrandll Lib "D:\Test2\bin\Release\Test2.dll" _
        (ByRef Array1 As Long, ByRef rows As Long, ByRef cols As Long)

    Sub TestDLL()

        Const rows As Long = 7
        Const cols As Long = 5

        Dim Arr(1 To rows, 1 To cols) As Long

        Call fortrandll(Arr(1, 1), rows, cols)

        ActiveSheet.Range("A1").Resize(rows, cols).Value = Arr

    End Sub

Note that the first element of the array is passed into the DLL ByRef as an ordinary Long.

I am indebted to the author here for my initial pointers to a working solution. A very similar solution also appears in this comp.lang.fortran thread.

calling fortran dll from VBA

4 Answers4