|
forums.silverfrost.com Welcome to the Silverfrost forums
|
View previous topic :: View next topic |
Author |
Message |
cOwl_7
Joined: 01 Jun 2011 Posts: 4
|
Posted: Wed Jun 01, 2011 10:41 pm Post subject: Call to a FTN95 DLL from VBA (MS Excel) |
|
|
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 |
|
|
davidb
Joined: 17 Jul 2009 Posts: 560 Location: UK
|
Posted: Thu Jun 02, 2011 6:29 am Post subject: |
|
|
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 |
|
|
cOwl_7
Joined: 01 Jun 2011 Posts: 4
|
Posted: Thu Jun 02, 2011 3:10 pm Post subject: |
|
|
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 |
|
|
JohnCampbell
Joined: 16 Feb 2006 Posts: 2554 Location: Sydney
|
Posted: Fri Jun 10, 2011 3:22 am Post subject: |
|
|
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 |
|
|
davidb
Joined: 17 Jul 2009 Posts: 560 Location: UK
|
Posted: Mon Jun 13, 2011 11:13 am Post subject: |
|
|
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 |
|
|
cOwl_7
Joined: 01 Jun 2011 Posts: 4
|
Posted: Tue Jun 14, 2011 2:54 pm Post subject: |
|
|
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 |
|
|
|
|
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
|