Call to a FTN95 DLL from VBA (MS Excel)

Hello, I have been working on creating a [color=orange:2a61284d80].dll[/color:2a61284d80] file, and calling it from Visual Basic Application (VB 6.5) in Microsoft Excel. Currently it results in a crash of MS Excel when the function is called.

Currently I have the following **.f95 ** (f95.f95)file:

F_STDCALL FUNCTION F(X)
INTEGER F, X

F=X

END

I generated a [color=orange:2a61284d80].dll[/color:2a61284d80] file using the slink in command line after calling the function:

C:\\...\\dll_File_Test>ftn95 f95.f95
[FTN95/Win32 Ver. 6.10.0 Copyright (c) Silverfrost Ltd 1993-2011]
    NO ERRORS  [<F> FTN95/Win32 v6.10.0]

C:\\...\\dll_File_Test>slink
[SLINK/Win32 v1.44 Copyright (c) Silverfrost Ltd. 1995-2010]
* dll
* lo f95.obj
* exportall
* file c:\\WINDOWS\\f95.dll

I then created a VB module in **MS Excel **:

Private Declare Function F Lib 'c:\\WINDOWS\\f95.dll' (ByVal i As Integer) As Integer

When I call the function in a cell in Excel, the application crashes. I assume that it is possible due to an incorrect variable pass, or something along those lines; however, am not certain. I have tried this by a couple methods - creating a .dll project as well, and have the same results.

I currently have Windows 5.10, with ftn95 6.10.0, Plato 4.4.0, VB-6.5, and MS Excel 2007

Thank you very much in advance. (I tried to look through most of the help files and previous posts, some had similar issues, but let me know if I have missed any)

From memory VBA's Integer is 2 bytes (16 bits) and Fortran's default integer is 4 bytes (32 bits).

Try changing the declaration to use VBA Long (which is 4 bytes).

Private Declare Function F Lib 'c:\\WINDOWS\\f95.dll' (ByVal i As Long) As Long 

Dear davidb, Thanks a lot for the reply. That helped me narrow down the problem. Yes, it seems like a very good idea to explicitly name the data type when declaring variables.

I changed the ftn95 code to (explicit declaration of KIND=3):

F_STDCALL FUNCTION F(X)
INTEGER(KIND=3) :: F,X

F=X

END FUNCTION F

Also, I removed the ByVal qualifier in the VBA code:

Private Declare Function F Lib 'c:\\WINDOWS\\f95.dll' (i As Long) As Long

It is now working, and I have checked it with a couple other examples as well. I will make some more considerations for qualifiers / variable declaration. I appreciate the help a lot! Thank you

cOwl_7,

Your approach shows a lot of potential as an easy approach to running Fortran programs from within excel. I would be interested to know how effective your approach has been in linking FTN95 code into your Excel VBA application. I'd be especially keen to know how you manage transferring large amounts of information between the two. I have applications where I would like to: Structure data input in an excel sheet. (400 rows x 2 to 10 columns) Run a VBA module which transfers the data to the Fortran program, runs the Fortran based application, interrogates the Fortran application for the results, then take these results and produce charts in a smarter way (this is the new bit!)

My application currently produces multiple files of output where each file would consist of 3,000 lines of 10 tab delimited fields; the first is a 'character string' and the rest are typically real numbers.

It would be good to control this in a more direct way.

John

John,

I do this sometimes (when a client wants this). You need to write some interface subroutines for your fortran code. You need to build a DLL application which exports THREE subroutines (each with F_STDCALL appended to the front). The subroutines will do the following:

• allows you to pass all the excel data to the subroutine (where it needs to be stored in common (or module global data)
• does the processing
• allows you to get the results.

So you need 3 subroutines and some short and simple VBA code to make the calls. There is not much overhead in making the setup and results calls, even if large amounts of data are transferred.

After that you need to produce the charts etc you want in Excel.

The fortran code will look like this. In any real application there will be array arguments, not scalars as I have shown here.

F_STDCALL SUBROUTINE SETUP(A,B,C, ....)
   REAL, INTENT(IN) :: A, B, C ! Change to whatever your variables are

   ! Store A,B,C in common

END SUBROUTINE SETUP

F_STDCALL SUBROUTINE PROCESS()

   ! Process data in common
   ! Store results in common

END SUBROUTINE PROCESS

F_STDCALL SUBROUTINE RESULTS(X,Y,Z, ....)
   REAL, INTENT(OUT) :: X, Y, Z ! Change to whatever your variables are

   ! Get results in common and put them in arguments

END SUBROUTINE RESULTS

In VBA you need something like:

Private Declare Sub Setup Lib 'filenameDLL' (ByVal A, ByVal B, ByVal C)
Private Declare Sub Process Lib 'filenameDLL' ()
Private Declare Sub Results Lib 'filenameDLL' (ByRef X, ByRef Y, ByRef Z)

sub Analysis

   ' Get values from worksheet
    X = worksheets('Sheet1').Range('A1') ' etc
   ' other lines

   call Setup(A,B,C)
   call Process
   call Results(X,Y,Z)

end sub

In response to your post as well John.

My first thought was to produce a .csv file from the fortran code, but it appears that is what you are doing currently.

There is a way to pass arrays from excel to fortran, so that might be a possibility, I believe this would require a little more research.

Also, to add on what davidb wrote as well - you can have internal functions in .dll files as well (e.g functions that you do not export, but are present within the .dll file). You can do this by ommitting the F_STDCALL header for the subroutine.

F_STDCALL SUBROUTINE SETUP(A,B,C, ....) 
   REAL, INTENT(IN) :: A, B, C ! Change to whatever your variables are 

   ! Store A,B,C in common 

   call Process()

END SUBROUTINE SETUP 

SUBROUTINE PROCESS() 

   ! Process data in common 
   ! Store results in common 

END SUBROUTINE PROCESS 

F_STDCALL SUBROUTINE RESULTS(X,Y,Z, ....) 
   REAL, INTENT(OUT) :: X, Y, Z ! Change to whatever your variables are 

   ! Get results in common and put them in arguments 

END SUBROUTINE RESULTS 

In command line you prevent the subroutine from being exported using the exportx command, eg:

C:\\...\\Documents>slink
[SLINK/Win32 v1.44 Copyright (c) Silverfrost Ltd. 1995-2010]
* dll
* exportx process
* lo filename.obj
* exportall
* file

This lets subroutines call eachother within the .dll file. - There may also be a more effective way of doing this. I'm not certain if anyone has any suggestions regarding this.

I hope this helps. Also, on another note, I am not certain if either of you are familiar with .dll registering through command line.

Currently, I am having trouble registering the .dll files created through slink for debugging. When I use Regsvr32 filename.dll, I receive the following error:

'filename.dll was loaded, but the DllRegisterServer entry point was not found.

This file can not be registered'

I'm not certain if anyone has any suggestions regarding that.