Fortran to VBA 4

[ESPcomposites]

I am looking at converting some public domain fortran codes in Excel VBA. One option would be to recode the entire thing, but that would be very time consuming.

There appears to be a way to do it via a DLL as well, though I am not familiar with this process. Does the user still need to have fortran installed for that or is it self contained in the DLL? How difficult is a task like that?

Any other options to consider? Excel as a front end is a must.

Brian

http://www.espcomposites.com

 

[xwb]

The answer depends on which Fortran compiler you are using. On some compilers, it is easy to create DLLs, on others, it is darn near impossible.

 

[IDS]

Brian - this link will get you started:

http://newtonexcelbach.wordpress.com/2008/12/01/linking-excel-to-fortran/

Search the blog for Fortran for follow up articles.

For use on other machines you will need to copy the dll and install the run-time package. See:

http://newtonexcelbach.wordpress.com/2009/12/28/distributing-silverfrost-fortran-applications/

I have used Silverfrost fortan and found it reasonably easy to get working. As well as avoiding the need to translate the code the performance of compiled Fortran code will normally be much better than VBA.

If you do decide to translate to VBA (sometimes it makes it more convenient for use with other routines) I have found Fortran reasonably straightforward to translate because its structure is more similar to VBA than many other languages.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[ESPcomposites]

Doug,

Thanks for the help. I am still trying to digest it, but I would like to distribute the VBA final product (free for educational purposes and small price for commercial use).

So the user must then install fortran and I suppose it will not be free if it is used in a commercial environment? Both of these would be major problems for the end user and maybe I will need to rewrite the code in VBA (or more likely contract it out).

Brian

http://www.espcomposites.com

 

[IDS]

So the user must then install fortran and I suppose it will not be free if it is used in a commercial environment? Both of these would be major problems for the end user and maybe I will need to rewrite the code in VBA (or more likely contract it out).

If you go with the Silverfrost compiler, the end user doesn't need to intall the whole system, just one dll (in addition to the dll for the application). My understanding is that if you buy a copy of the compiler then there is no restriction on what you do with applications you have written, but if you use the free version then it is restricted to evaluation/educational use. But best check with the suppliers.

Alternatively there are free Fortran compilers:

http://www.megasolutions.net/fortran/g95-versus-gfortran-50009.aspx

I went with Silverforost because at the moment I am not using it for commercial purposes, and I found it a struggle getting g95 to work with Excel. If I wanted to do something commercial in Fortran I'd probably get a licence from Silverfrost, but the free alternatives would be worth exploring if you have the time to find and absorb the documentation.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[ESPcomposites]

Oh, I *think* I get it. I don't mind paying for the it as long as I can freely distribute it.

It would be much better to pay for it than to mess around with something that is free, but would be a hassle. It's within the budget, though somewhat pricey.

That being said, what do you mean by *install* a DLL? Can the user just copy the DLL to the directory they are running in?

I am thinking I need:

.xls with VBA code to interface to fortran
.dll that is the bridge between VBA and fortan
.dll that runs the fortran

All of them would be in the same directory. Does this make sense? Overall, how difficult is it to interface the fortran to VBA? There are a lot of pretty good codes done under air force contract that are in the public domain. I think it would be useful to have an Excel interface to them.



Brian

http://www.espcomposites.com

 

[IDS]

That being said, what do you mean by *install* a DLL? Can the user just copy the DLL to the directory they are running in? I am thinking I need:.xls with VBA code to interface to fortran.dll that is the bridge between VBA and fortan.dll that runs the fortran
All of them would be in the same directory. Does this make sense?

Either the same directory, or better put the two dlls in the System32 directory (or another on the search path) so they can be called from anywhere.

Overall, how difficult is it to interface the fortran to VBA? There are a lot of pretty good codes done under air force contract that are in the public domain. I think it would be useful to have an Excel interface to them.

Not very difficult, once you have got the compiler settings right, which the blog post should cover. Really all you have to do is:

- Convert the Excel data into a form acceptable to Fortran, then pass it to the dll.
- Add a declare statement at the top of the module, or in a separate module, to tell VBA where the dll function is
- Add some code to the Fortran to receive data and pass it back in a suitable form. Typically all you need to do is write any output to an array passed by reference, then the VBA can access that data and write it to the spreadsheet, or whatever you want to do with it.

The main thing with the VBA code is to make sure that everything that is passed to the Fortran routine and back is the right data type, and that arrays are exactly the right size. If you pass an array to the Fortran and the dll writes outside the bounds of the array, you won't get a polite message saying you can't do that, it will just crash, or worse it will act like everything is OK and then crash when you are half way through a demonstration with a potential client.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[ESPcomposites]

Well sounds promising

Most of the codes I am looking into were written in the 80's so I am assuming the fortran was done in fortran77. Do you think I will have any issues with this?

Brian

http://www.espcomposites.com

 

[ESPcomposites]

Here is a sample, posted by another member. It is for BJSFM (Bolted Joint Stress Field Model). Care to have a look at the feasibility? This would be my first project.

http://files.engineering.com/getfile.aspx?folder=2cb79806-7bfc-4809-a490-c4a1e029f8c5&file=BJSFM.FOR

Brian

http://www.espcomposites.com

 

[IDS]

Brian - Fortan 77 shouldn't be a problem, probably makes life easier if anything.

I'll have a look at your posted code later, but I'd better go and do some paid work now

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

Generally, the DLL needs to be installed, so that the OS knows where to find the DLL, and where all the entry points are. The whole point of a Dynamically Linked Library is that the entry points are not known until the DLL is actually called for.

TTFN

FAQ731-376

 

[ESPcomposites]

What would be the advantage of a DLL versus having Excel write an input file to fortran and read the output back into Excel. According to a friend of mine, he thought may actually be easier to implement. Something to do with arguments and other stuff I don't understand.

Brian

http://www.espcomposites.com

 

[IRstuff]

A standalone DOS command-line program does not have to worry about the type or format of the data being exchanged, as it is clearly defined and written in the transfer file. A DLL is passing data through the operating system interface, which raises questions about big and small endian, etc., To that extent, a standalone program is easier to write and to deal with.

TTFN

FAQ731-376

 

[IDS]

Some advantages of the dll route are:

You can write the Excel routine as a UDF, which will behave like a VBA UDF, or a built in function.

You don't have to worry about keeping track of separate input and output data files.

You don't have to worry about formatting of the Fortran output, and reading the text file into Excel.

Using text files will be slower. Probably not a huge issue, but if you need to do a lot of iterations it could be.

It's probably quicker getting started going the text file route, especially if the Fortran program is already set up to read input from a file and write to a text file, but in the long run I think it is easier to use a dll, and this gives much more satisfactory results.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/