×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Fortran to VBA
4

Fortran to VBA

Fortran to VBA

(OP)
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
www.espcomposites.com

RE: Fortran to VBA

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.

RE: Fortran to VBA

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/
 

RE: Fortran to VBA

(OP)
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
www.espcomposites.com

RE: Fortran to VBA

Quote:


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/
 

RE: Fortran to VBA

(OP)
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
www.espcomposites.com

RE: Fortran to VBA

Quote:

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.


Quote:

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/
 

RE: Fortran to VBA

(OP)
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
www.espcomposites.com

RE: Fortran to VBA

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/
 

RE: Fortran to VBA

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: Eng-Tips.com Forum Policies

RE: Fortran to VBA

(OP)
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
www.espcomposites.com

RE: Fortran to VBA

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: Eng-Tips.com Forum Policies

RE: Fortran to VBA

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/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close