Linking FORTRAN subroutine to Excel VBA... and failing

[mggm]

Hi:

I'm trying to call AS62 algorithm from VBA.  I have done everything I can think of, but I still fail.

This is the FORTRAN CODE:

SUBROUTINE UDIST(M, N, FRQNCY, LFR, WORK, LWRK, IFAULT)
      !dec$ attributes stdcall, alias:'UDIST', dllexport :: UDIST
      !dec$ attributes reference :: M, N, FRQNCY, LFR, WORK, LWRK, IFAULT
      INTEGER M, N, LFR, LWRK, IFAULT
      REAL FRQNCY(*), WORK(*)
      INTEGER MINMN, MN1, MAXMN, N1, I, IN, L, K, J
      REAL ZERO, ONE, SUM
      DATA ZERO /0.0/, ONE /1.0/
      IFAULT = 1
      MINMN = MIN(M, N)
      IF (MINMN .LT. 1) RETURN
      IFAULT = 2
      MN1 = M * N + 1
      IF (LFR .LT. MN1) RETURN
      MAXMN = MAX(M, N)
      N1 = MAXMN + 1
      DO 1 I = 1, N1
    1 FRQNCY(I) = ONE
      IF (MINMN .EQ. 1) GO TO 4
      IFAULT = 3
      IF (LWRK .LT. (MN1 + 1) / 2 + MINMN) RETURN
      N1 = N1 + 1
      DO 2 I = N1, MN1
    2 FRQNCY(I) = ZERO
      WORK(1) = ZERO
      IN = MAXMN
      DO 3 I = 2, MINMN
        WORK(I) = ZERO
        IN = IN + MAXMN
        N1 = IN + 2
        L = 1 + IN / 2
        K = I
        DO 3 J = 1, L
          K = K + 1
          N1 = N1 - 1
          SUM = FRQNCY(J) + WORK(J)
          FRQNCY(J) = SUM
          WORK(K) = SUM - FRQNCY(N1)
          FRQNCY(N1) = SUM
    3 CONTINUE
      SUM = ZERO
      DO 10 I = 1, MN1
        SUM = SUM + FRQNCY(I)
        FRQNCY(I) = SUM
   10 END DO
    4 IFAULT = 0
      DO 20 I = 1, MN1
   20 FRQNCY(I) = FRQNCY(I) / SUM
      RETURN
 
      END

It compiled without ny errors/warnings.

This is the VBA code:

Option Base 1
Option Explicit
Public Declare Sub UDIST Lib "E:\\Prueba Fortran\\Compilado\\AS62.dll" _
(ByRef m As Integer, ByRef n As Integer, ByRef frqncy As Long, ByRef lfr As Integer, ByRef work As Long, ByRef lwrk As Integer, ByRef ifault As Integer)

Public Function UPROB(m As Integer, n As Integer, U As Single) As Double
Dim lfr As Integer, minmn As Integer, lwrk As Integer, work() As Long, frqncy() As Long, ifault As Integer
lfr = m * n + 1
minmn = Application.WorksheetFunction.Min(m, n)
lwrk = 1 + minmn + (lfr + 1) / 2
ReDim work(lwrk)
ReDim frqncy(lfr)

Call UDIST(m, n, frqncy(1), lfr, work(1), lwrk, ifault)

' Reads the frequency for given U --> gives error, disabled for the moment
'UPROB = frqncy(U + 1)

'Reads IFAULT value to check if the call is correct --> still gives error
UPROB = ifault

End Function


I really don't know what I'm doing wrong

Thanks in advance,
Marta

[Steven_L_Intel1]

You have not said what the error is.  However, I note that your VBA declaration of UDIST specifies "long" as the datatype for frqncy and work, but these are declared as REAL in the Fortran code, so these don't match.  You want "single" in the VBA declaration here (also in the declaration for the local variables.)

[mggm]

I simply get a "#VALOR!" (Spanish Excel, I suppose the translation is #VALUE!) when I try to use the funtion in a cell:

=UPROB(4,4,5)

I changed Long to Single for those variables declared as real in FORTRAN , but I still get the same error.

or the moment I don't try to get the FRNCYvalue, just the IFAULT error handling, in order to see if I am calling the subroutine correctly.

I also used Dependency Walker to check that the DLL had in fact the UDIST subroutine.

Thanks a lot,
Marta

[Steven_L_Intel1]

Are you aware that Fortran arrays start indexng at element 1?  I think VBA uses zero. You should debug the VBA code and see what gets returned from the call to the Fortran routine.

[mggm]

That's why I added Option Base 1 to my VBA code, to start from element 1.

As I said, nothing gets returned from the call, not even the IFAULT (error) codes.

Thanks,
Marta

[Steven_L_Intel1]

I'm not a VBA programmer, but comparing to our VB-calls-Fortran sample, I wonder if the arrays should be declared like this:

ByVal frqncy() as Single

and pass just frqncy and not frqncy(1).  The (1) was once thought to be the right way, but not anymore.

[IanH]

I recall seeing an (Intel?) knowledge base article or similar about this, but it looks like I've not changed things in my own code yet (possibly because the Excel version I target is 2003 and not one of these new fangled ribbony I-don't-know-where-anything-is variants, or possibly because I was feeling lazy).

"ByVal array() as xyz" in a "Declare" declaration gives a VBA error here.  Maybe 'VB' /= 'VBA' ?  Anyway, you've made me rather nervous - so I'll dig some more.

[DavidWhite]

This works for me ... hope it helps:

VBA side

Private Declare Sub SeawaterDensity_F Lib "AWAProps.dll" _
    (TempC As Double, Saltgkg As Double, Value As Double, ByVal Units As String)

Fortran Wrapper - note use of arrays of size (1) to interface the arguments and the Reference attribute for the string variable.

Subroutine SeawaterDensity_F(TempC, Saltgkg, Value, Units)

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

!DEC$ ATTRIBUTES REFERENCE :: Units

IMPLICIT NONE

Real(KIND=8), INTENT(IN) :: TempC(1), Saltgkg(1)

Real(KIND=8), INTENT(OUT) :: Value(1)

Character(LEN=20), INTENT(INOUT) :: Units

CALL SeawaterDensity(TempC(1), Saltgkg(1), Value(1), Units)

END Subroutine SeaWaterDensity_F

Actual Fortran function (can be called from other Fortran routines as well)

Subroutine SeaWaterDensity(TempC, Saltgkg, Value, Units)

IMPLICIT NONE

Real(KIND=8), INTENT(IN) :: TempC, Saltgkg

Real(KIND=8), INTENT(OUT) :: Value

Character(LEN=20), INTENT(OUT) :: Units

[mggm]

Hi Ian

I tried it, but still doesn't work, same #VALUE! error.

Thanks,
Marta

[mggm]

Hi Steve

ByVal frqncy() as single gave an error, freely translated from Spanish as "ByRef should be used"

Eliminating the (1) resulted agin in #VALUE!

Frustrated, but many thanks,
Marta

[mggm]

Hi Steve:

You wee right! I set an eror trap and surprisingly, the error code says that the dLL can't be found. But it is there, and exactly at the location I indicate (E:\Prueba Fortran\Compilado\AS62.dll).

Now I know what I have to fix, although I don't know how (still9.

Thanks,
Marta

[mggm]

Hi again

Using Dependency Walker I have located some missing DLLs (wer, IEShims, bcrypt... long list), and now the error trap says "Bad DLL calling convention".

I'm looking on that, but for themoment I only found this: http://support.microsoft.com/kb/85108/en-us?fr=1

Marta

[Steven_L_Intel1]

Are you running this on a computer where Intel Fortran is not installed?  If so, you need to install the redistributables package - see here.  I wouldn't worry about wer, IEShims, etc., though maybe some others if your code refers to them.