Fortran DLL & MS Excel

Fortran DLL & MS Excel

Although I am a fairly experienced Fortran programmer, I have little or no idea about how to create a Fortran DLL that can be subsequently used in MS Excel. Browsing the DVF and Excel documentation also doesn't seem to get me anywhere, so I hope that one of you will be able to point me in the right direction.

For the sake of discussion, suppose I want to create a library with my own Fortran functions and make it available within Excel. In fact, let's assume the library will contain only two simple functions, say:

Real Function MySub1 (a, b, c)
Implicit None
Real, Intent (IN) :: a, b, c
Real :: MySub2
MySub1 = a + MySub2 (b, c)
Return
End Function MySub1

Real Function MySub2 (b, c)
Implicit None
Real, Intent (IN) :: b, c
MySub2 = b * c
Return
End Function MySub2

My questions are as follows:

1) How do I go about creating the DLL? Is it as simple as creating a new DLL project in DVF and then compiling and linking? Or do I need to take any special measures.

2) Once the DLL has been successfully created, how can I make it available for use in MS Excel, so that, for example, I can use formulas like "=MySub1(A1,A2,A3)" or "=MySub2(B1,B2)" in a spreadsheet cell?

Thanks in advance for your feedback!

6 posts / 0 new
Last post
For more complete information about compiler optimizations, see our Optimization Notice.

This subject has been covered by lots of postings, so you could
do a search of this forum using 'Excel' and find more help than
you can shake a stick at. You could start with this
link.

Just create a DLL project and compile the code. The DLL must then be copied to a directory in the Excel search path - best to put it into the /system/ directory, IMHO, this saves putting it into every folder with each and every Excel file that needs to access it.

See the link for how to use VB in Excel to access the Fortran DLL routines via wrappers. It may not be the only way, but I found that it works. If you want to handle character strings or, god forbid, arrays, then ask for more help.

HTH

Message Edited by intel.software.network.support on 12-09-2005 01:54 PM

The page linked to the word "this" appears no longer to exist.

You could try the following, developed using Compaq Visual Fortran 6.6c

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 EXCEL 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, b2 and c2},
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 by Excel when it needs to load it.

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.

**NOTE: If you want to use REAL*8 (i.e. double precision) in
the FORTRAN, just define the VisBasic functions and their
arguments as Double rather than Single.

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

An Excel example is provided in Intel Visual Fortran 10.

Steve - Intel Developer Support

Some other things to make this work easier ...

If the DLL is on the machine's PATH, then you only need to declare the DLL file name in the VBA code, not the full path.

You can save the VBA caode from Excel as an Excel Add-In (.xla). Rather than put this into the Excel Add-Ins folder, put it into the XLSTART folder. If you do not do this, the hidden addresses stored in the Excel workbook point to the user's Add-In folder. If the workbook is then shared with other users, then they cannot access the function values without updating the location of the XLA file.

For maintaining portability in my Fortran code, I also maintain the original fortran functions without the DLLExport statements. I then add Wrapper routines which contain the DLLEXport statements and then simply call the Fortran routines. In this way, I can have a static library project, for example, which is usable by any fortran apps and a DLL project using the same fundamental codes that are callable from Excel/VBA.

David

Leave a Comment

Please sign in to add a comment. Not a member? Join today