forums.silverfrost.com Forum Index forums.silverfrost.com
Welcome to the Silverfrost forums
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Call to a FTN95 DLL from VBA (MS Excel)

 
Post new topic   Reply to topic    forums.silverfrost.com Forum Index -> Support
View previous topic :: View next topic  
Author Message
cOwl_7



Joined: 01 Jun 2011
Posts: 4

PostPosted: Wed Jun 01, 2011 10:41 pm    Post subject: Call to a FTN95 DLL from VBA (MS Excel) Reply with quote

Hello, I have been working on creating a .dll 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:

Code:
F_STDCALL FUNCTION F(X)
INTEGER F, X

F=X

END


I generated a .dll file using the slink in command line after calling the function:

Code:

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 :

Code:

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)
Back to top
View user's profile Send private message
davidb



Joined: 17 Jul 2009
Posts: 557
Location: UK

PostPosted: Thu Jun 02, 2011 6:29 am    Post subject: Reply with quote

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).

Code:

Private Declare Function F Lib "c:\WINDOWS\f95.dll" (ByVal i As Long) As Long
Back to top
View user's profile Send private message
cOwl_7



Joined: 01 Jun 2011
Posts: 4

PostPosted: Thu Jun 02, 2011 3:10 pm    Post subject: Reply with quote

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):
Code:

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:
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
Back to top
View user's profile Send private message
JohnCampbell



Joined: 16 Feb 2006
Posts: 2227
Location: Sydney

PostPosted: Fri Jun 10, 2011 3:22 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
davidb



Joined: 17 Jul 2009
Posts: 557
Location: UK

PostPosted: Mon Jun 13, 2011 11:13 am    Post subject: Reply with quote

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.

Code:


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:

Code:

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
Back to top
View user's profile Send private message
cOwl_7



Joined: 01 Jun 2011
Posts: 4

PostPosted: Tue Jun 14, 2011 2:54 pm    Post subject: Reply with quote

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.

Code:

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:

Code:

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    forums.silverfrost.com Forum Index -> Support All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group