Excel Function from Fortran .dll

[Andre_P_]

Hi,

I found several threads addressing this topic, but still need some help getting my Fortran .dll file to work.  I am using Windows 7, Excel 2010 and  Visual Fortran Composer XE 2013 SP1.  I dummed down the code from a 2002 post on this forum (titled Visual Fortran dll call from Excel/VBA)

My FORTRAN code is:

FUNCTION COMPUTEMULT ( ARG1, ARG2 )

!DEC$ ATTRIBUTES DLLEXPORT,ALIAS:'ComputeMult' :: COMPUTEMULT

REAL*4 ARG1, ARG2, COMPUTEMULT

COMPUTEMULT = ARG1 * ARG2

END FUNCTION COMPUTEMULT

My VB code is:

Public Declare Function ComputeMult Lib "c:\excel_test.dll" (A1 As Single, A2 As Single) As Single

Sub junk()

X = 4
Y = 5
MsgBox "X= " & X
MsgBox "Y= " & Y
Z = ComputeMult(X, Y)

MsgBox "X*Y= " & Z

End Sub

When i run the VB proceedure I get teh error "Bad .dll calling convention"

I also ran Dependency Walker on the excel_test.dll and recieved errors:

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.

There is a link to KERNEL32.dll, despite setting the FORTRAN>library>runtime  option to multithread.

Any help is appricated.

Andre

[IanH]

You need to set the calling convention of the Fortran procedure to stdcall.  Add

!DEC$ ATTRIBUTES STDCALL :: COMPUTEMULT

to the body of the Fortran procedure.

Dependency walker gets confused when resolving DLL's for a 32 bit DLL on a 64 bit system.  The error messages you quote are typical of that situation.

KERNEL32 is a basic system DLL.  If you DLL interacts with the system in any way, it needs to (perhaps indirectly) reference this DLL.

[Hitoshi]

I usually call variables in Fortran dll by reference; 

Public Declare Function ComputeMult Lib "c:\excel_test.dll" ( Byref A1 As Single, Byref A2  As Single) 

[DavidWhite]

Hitoshi,

I think in VBA, ByRef is assumed as default, and is not required.  In VB, however, it is (just to make life more interesting).

To allow for the different versions of Excel, I need to now use declarations like this one.  The VBA7 section is for Excel 2010 - it needs the addition of the PtrSafe keyword.

#If VBA7 Then
    Private Declare PtrSafe Sub WaterDensity_F Lib "AWAProps.dll" _
        (TempC As Double, Value As Double, ByVal Units As String)
#Else
    Private Declare Sub WaterDensity_F Lib "AWAProps.dll" _
        (TempC As Double, Value As Double, ByVal Units As String)
#End If

on the Fortran Side, I have:

Subroutine WaterDensity_F(TempC, Value, Units)

!DEC$ ATTRIBUTES DLLEXPORT, STDCALL, ALIAS:'WaterDensity_F' :: WaterDensity_F

!DEC$ ATTRIBUTES REFERENCE :: Units

 Hope this is helpful.  (note the apparent inconsistency in keywords for passing back strings - on the VB side ByVal vs using the Reference attribute on the Fortran side)

David

[Andre_P_]

Thanks to everyone for the help.

I added STDCALL per IanH's advice.  The VBA code ran, but returned a 0 for any input.  I added REFERENCE as shown below and the code works properly.

1  !DEC$ ATTRIBUTES STDCALL, REFERENCE :: COMPUTEMULT

Also, removing the REFERENCE command from the FORTRAN code and adding Byref to the VBA caused Excel to return a 0 for any input.

Refering to the FORTRAN compiler documentation  - REFERENCE specifies a dummy argument's memory location is to be passed instead of the argument's value.

That said, when should REFERENCE be used and omited?