Using a gFortran dll with Excel 365 / VBA 7

Question

For years I used a combination of dlls written in Fortran PowerStation and Excel 02/03, with VBA 6.

But progress marches on, and I got a new machine with Windows 64 bit, Excel 365 (64 bit), and no Fortran. So I downloaded gFortran as part of MinGW-w64-for 32 and 64 bit Windows from SourceForge. Now I have to get it working. Here is the Fortran source code, in a file called gTest.F90:

integer(2) function AddIt(iVal1,iVal2)
!MS$ATTRIBUTES dllexport, stdcall, alias:'AddIt' :: ADDIT
Integer(2) iVal1,iVal2
        AddIt=iVal1+iVal2
end function AddIt

(The second line specifies Microsoft attributes. More about this later.)

I compiled it as follows, from directory ., after adding the path to the MinGW bins added to %PATH%

gfortran -Wextra -Wall -pedantic -shared -fPIC -o .\Output\gTest.dll .\Source\gTest.F90

This produced no output, but did write the file gTest.dll.

And so onto Excel / VBA. I set up a little spread sheet, gTest.xlsm, which tried to invoke AddIt. It declared AddIt as follows:

Declare PtrSafe Function AddIt Lib "C:\A\Projects\gTest\Output\gTest.dll"(iVal1 As Integer, iVal2 As Integer) As Integer

No luck. So I entered the following VBA code and stepped through it:

Sub RunIt()
Dim Val1 As Integer, Val2 As Integer, Sum As Integer
    Val1 = 1
    Val2 = 10
    Sum = AddIt(Val1, Val2)
    Debug.Print Val1, Val2, Sum
End Sub

As expected, it blew up on Sum =, with one of MS's more useless error messages "Error in loading DLL (Error 48)".

Now, I suspect that the problem is that I am not telling the dll what within it must be exported - the function of the MS attribute statement above. I see that in the C++ environment, you can export from a dll with either the keyword __declspec(dllexport), or with a module definition (.def) file. But I cannot see how you could utilise either of these with gFortran (and I have tried). Or, possibly, did I not compile and link for 64 bits?

Can someone please help me? It would be gratefully appreciated.

Better to explicitly put the `ByRef` on the function arguments so it is clear the values are passed by reference and not value. Then check the calling convention used as a compiler option to see when arguments are expected as references and when as values. — John Alexiou, Oct 07 '21 at 17:22

Vladimir F Героям слава · Answer 1 · 2021-10-07T15:11:42.050

!MS$ATTRIBUTES is ignored by gfortran, try

!GCC$ ATTRIBUTES DLLEXPORT

See https://gcc.gnu.org/onlinedocs/gfortran/ATTRIBUTES-directive.html

STDCALL is only relevant for 32 bit.

The alias can also be set using bind(C):

integer(2) function AddIt(iVal1,iVal2) bind(C, name='AddIt')

Please check that the kind numbers in MS Powerstation actually correspond to those in gfortran. Chances are that you actually want integer(8) or an equivalent using some more portable syntax Fortran: integer*4 vs integer(4) vs integer(kind=4)

John Alexiou · Answer 2 · 2021-10-07T18:16:20.720

0

This varies by the compiler and it is shown below just for illustration. Some of the below info might be incorrect, please consult the specific compiler documentation for what calling convention to use.

Consider the following Fortran procedure

subroutine test(n,x,a)
!gcc$attributes dllexport :: test
  integer :: n
  real :: x
  real, dimension(n) :: a
end subroutine

And the corresponding VBA declaration

Calling Convention	Procedure Names(*)	Scalar Arg.	Array Arg.	VBA Spec
C, Default	_UPPERCASE	Reference	Reference	`(ByRef N As Long, ByRef X As Single, ByRef A As Single)`
C, REF	_lowercase	Value	Reference	`(ByVal N As Long, ByVal X As Single, ByRef A As Single)`
STD, CALL	UPPERCASE@n	Value	Reference	`(ByVal N As Long, ByVal X As Single, ByRef A As Single)`
STD, REF	UPPERCASE@n	Reference	Reference	`(ByRef N As Long, ByRef X As Single, ByRef A As Single)`

(*) Procedure names are only decorated on x86. @n denotes the number of bytes needed to be cleaned from the stack before the function returns.

Note that for array arguments, only pass the reference to the first element because VBA uses SafeArray structures that are not supported by Fortran.

Dim n as Long
Dim x as Single
Dim a() as Single

Call TEST(n, x, a(1))

Consider specifying the specific value types for scalar types overriding the default behavior for C,Default calling convention

subroutine test(n,x,a)
!gcc$attributes dllexport :: test
  integer, value :: n
  real, value :: x
  real, dimension(n) :: a
end subroutine

to be declared in VBA as

Declare PtrSafe Sub Test Lib "Fortran.dll" (ByVal N As Long, ByVal X As Single, ByRef A As Single)

Call Test(100&, x, a(1))

edited Oct 07 '21 at 18:16

answered Oct 07 '21 at 18:12

John Alexiou

28,472
11
77
133

`!dec$attributes` will also be ignored by gfortran – Vladimir F Героям слава Oct 07 '21 at 18:15
@VladimirF Yes, whatever the attribute declaration is for gfortran. – John Alexiou Oct 07 '21 at 18:16
Thanks Vladimir and John. Unfortunately, still no luck. I have tried as many combinations of your suggestions as I could, but I keep on getting the error message "Error in loading DLL (Error 48)". – Rogan Oct 14 '21 at 10:39
I even tried removing all arguments, to eliminate argument passing conventions. How can I cinfirm that gFortran is actually producing a 64-bit dll? – Rogan Oct 14 '21 at 10:50
I have used either the [dumpbin.exe](https://learn.microsoft.com/en-us/cpp/build/reference/dumpbin-reference?view=msvc-160) utility or [depends.exe](https://www.dependencywalker.com/) – John Alexiou Oct 14 '21 at 12:29
`dumpbin /headers Fortran.dll` will produce `14C machine (x86)` for 32-bit and `8664 machine (x64)` for 64-bit dll – John Alexiou Oct 14 '21 at 12:43
`dumpbin /exports Fortean.dll` will give you the name of the exported functions, their relative address (RVA) and if the function has @n on the end, the number of bytes occupied by the arguments in the stack. – John Alexiou Oct 14 '21 at 12:46

Using a gFortran dll with Excel 365 / VBA 7

2 Answers2