Visual Fortran dll call from Excel/VBA

[srj]

Hi !

Need some help with how to call Visual Fortran dll from Excel/VBA. Can any one give me an example.

What is difference between Calling Fortran dll from Excel/vba & Calling Fortran dll from VB ?

Thanks !

[isn-removed200637]

I went through this.
On the principle of learning to walk before you try running,
I coded the simplest code I could think of to add and/or
multiply the contents of two cells and get the correct answer returned.

I have been able to get correct values returned to an EXCEL
cell using the formula reference =DoMult(b2,c2) for example
{to multiply the contents of the two indicated cells},
so long as I include the REFERENCE attribute in the compiler
directive included in the FORTRAN code for function DOMULT.
If I omit REFERENCE and use STDCALL alone, the number returned
is wrong.
If I include both attributes, as shown below, I get the correct value
returned to EXCEL.

Here is the FORTRAN code used to create the dynamic-link
library DLL2_TEST.DLL, which was copied to the /SYSTEM/ folder,
which is in most search paths, so that it will be found automatically

	FUNCTION COMPUTEMULT ( ARG1, ARG2 )
!DEC$ ATTRIBUTES DLLEXPORT,STDCALL,REFERENCE,ALIAS:'ComputeMult' :: COMPUTEMULT
	REAL*4 ARG1, ARG2, COMPUTEMULT

	COMPUTEMULT =  ARG1  *  ARG2 

	END FUNCTION COMPUTEMULT

	FUNCTION COMPUTEADD( ARG1, ARG2 )
!DEC$ ATTRIBUTES DLLEXPORT,STDCALL,REFERENCE,ALIAS:'ComputeAdd' :: COMPUTEADD
	REAL*4 ARG1, ARG2, COMPUTEADD

	COMPUTEADD =  ARG1  +  ARG2 

	END FUNCTION COMPUTEADD

	SUBROUTINE COMPUTEBOTH ( ARG1, ARG2, ARG3 )
!DEC$ ATTRIBUTES DLLEXPORT,STDCALL,REFERENCE,ALIAS:'ComputeBoth' :: COMPUTEBOTH
	REAL*4 ARG1, ARG2, ARG3(2)

	ARG3(1) =  ARG1  *  ARG2 
	ARG3(2) =  ARG1  +  ARG2 

	END SUBROUTINE COMPUTEBOTH

Here is the VisualBasic code used in the EXCEL worksheet
(I have spread the longer PUBLIC statements over two lines for ease of
visibility in this restricted space)
essentially, I use 'wrappers' such as DoAdd( arg1, arg2) etc. for the calls to
the FORTRAN functions , such as ComputeAdd(X,Y). The message boxes were just for show.

Public Declare Function ComputeMult Lib "C:Winntsystem32dll2_test.dll"
 (A1 As Single, A2 As Single) As Single
Public Declare Function ComputeAdd Lib "C:Winntsystem32dll2_test.dll"
 (A1 As Single, A2 As Single) As Single
Public Declare Sub ComputeBoth Lib "C:Winntsystem32dll2_test.dll"
 (A1 As Single, A2 As Single, A3 As Single)
Public Function DoMult(X As Single, Y As Single) As Single
MsgBox "X= " & X
MsgBox "Y= " & Y
Z = ComputeMult(X, Y)
MsgBox "X*Y= " & Z
DoMult = Z
End Function

Public Function DoAdd(X As Single, Y As Single) As Single
MsgBox "X= " & X
MsgBox "Y= " & Y
Z = ComputeAdd(X, Y)
MsgBox "X+Y= " & Z
DoAdd = Z
End Function

Public Function DoBoth(X As Single, Y As Single,
 ISWITCH As Integer) As Single
Static Z(1 To 2) As Single
MsgBox "X= " & X
MsgBox "Y= " & Y
MsgBox "Iswitch= " & ISWITCH
Rem Iswitch=0 calls routine to get both
Rem values and returns the first
If ISWITCH = 0 Then
Call ComputeBoth(X, Y, Z(1))
MsgBox "First value = Product = " & Z(1)
DoBoth = Z(1)
Else
Rem Iswitch not =0 calls routine to get both
Rem values and returns the second  
Call ComputeBoth(X, Y, Z(1))
MsgBox "2nd value = Sum =  " & Z(2)
DoBoth = Z(2)
End If
End Function

[srj]

Thanks!

[Steven_L_Intel1]

Tony,

In your example, you can omit both STDCALL and REFERENCE and it should work. STDCALL by itself changes the default argument passing to VALUE, which is why you needed to add REFERENCE. But the default is to use the STDCALL mechanism (not the same as explicitly saying STDCALL) and to pass by reference.

Steve

[tingchuan_wang]

This example is very helpful. However I went into a problem: I couldn't get ARG3(2) returned to VBA using the COMPUTEBOTH subroutine. I got VBA error: subscript out of range. Below is my VBA code to call COMPUTEBOTH.

Sub Test_ComputeBoth()
Dim X As Single, Y As Single, Z(1 To 2) As Single
X = 10
Y = 5
Call ComputeBoth(X, Y, Z)
MsgBox X, Y, Z(1), Z(2)
End Sub

In fact, I had troubles using arrays. I modified the function COMPUTEADD to a subroutine that adds two arrays. I couldn't make it work. Someone has to help me!

The fortran code is:

SUBROUTINE COMPUTEADDM( N, ARG1, ARG2, ARG3 )
!DEC$ ATTRIBUTES DLLEXPORT,STDCALL,REFERENCE,ALIAS:'ComputeAddM' :: COMPUTEADDM
INTEGER N
REAL*4 ARG1(N), ARG2(N), ARG3(N)
DO I = 1,N
ARG3(I) = ARG1(I) + ARG2(I)
END DO
END SUBROUTINE COMPUTEADDM

and VBA code is:

Public Declare Sub ComputeAddM Lib _
"F:A_INTERFACEsVB2FortranFCALLDebugFCALL.dll" _
(N As Long, X() As Single, Y() As Single, Z() As Single)

Sub Test_ComputeAddM()
Dim X(1 To 2) As Single, Y(1 To 2) As Single, Z(1 To 2) As Single
Dim N As Long, i As Single
N = 2
For i = 1 To N
X(i) = i
Y(i) = i ^ 2
Next i
Call ComputeAddM(N, X, Y, Z)
MsgBox X(1) & Y(1) & Z(1) & vbLf & X(2) & Y(2) & Z(2)
End Sub

[tingchuan_wang]

It seems that I solved my problem.
I found the answer at
http://www.compaq.com/fortran/docs/vf-html/pg/pgwvbusr.htm#arrayex

VB arrays can be send to Fortran by giving the first element and the length of array in VB call.