Re: Using fortran code with VBA

On Jun 29, 8:59 pm, veda <kedar.hardi...@xxxxxxxxx> wrote:
Hi,

I have done limited programming with both VBA and FORTRAN (CVF) i.e.
separately. I would like to know how to use VBA for front end while
you use your (existing) fortran code in the background.

If someone can tell me steps to follow for creating simplest project
that illustrates the process, I would appreciate that. I am not
familiar with creating DLLs etc. So, if that is required, I would need
pointers/help for that.

The line of help I am seeking is something like this -

1. Lets say you have a cell in excel where user enters a number
2. Create a "button" for the user to hit that invokes VBA code
"mycode"
3. "mycode" is a VBA code that reads the "input" from the cell and
sends it to a fortran code say "ComputeSquare" e.g. compute the square
of the number (of course, this doesn't need fortran code interface
etc..) and somehow returns to VBA code "mycode"
4. "mycode" then puts that value (squared) in another cell in excel
for user as output.

What I do not know is -

1. What do I need to do after creating "ComputeSquare" in CVF the
usual way so that it can be accessed from VBA? Can "ComputeSquare" be
just a fortran subroutine or is it a full fortran program with its
"main" etc? If it is a DLL, please clarify how do I go about creating
and using it.

2. How do you call "ComputeSquare" (either a full program or a
subroutine) from within VBA and send in the input/output across?

Any help will be greatly appreciated.

Veda.

1. You need not go to so much trouble to access an external function
written in Fortran from VBA. If you include the proper global
declaration, you can use it in formulas just like built in functions.

2. You do need to create a DLL which contains your function. The
recipe varies from compiler to compiler, but the CVF does document how
to do this in its manuals.

3. Here are the main issues:

a. for VB/VBA to use functions (or subroutines) in a DLL, they must
use the STDCALL calling convention.

b. Arguments must correspond between VB/VBA and Fortran, both in type
and in how they are passed. For example, integers must be the same
size and they must be passed by reference, not by value.

c. Names must be exported (made visible) from the routines in the DLL.
Some compilers "decorate" or "mangle" routine names in some fashion.
Fortran is not case sensitive, so some compilers convert exported
symbols to upper case. Others add underscores in various places.
Others add a suffix indicating the number of bytes of arguments passed
on the stack.

d. Name "mangling" can also be taken care on the VB side using ALIAS.

e. Error messages from VBA/VB are misleading. A "missing" DLL might in
fact be malformed.

f. It's not always obvious where to put the DLL. Sometimes the current
directory works.

g. VBA has some differences from VB. VB dot net also differs from the
others in some ways.

h. You need to explicitly declare the type of arguments on the VB
side. Note that the DIM statement is tricky. You should only use one
variable for each DIM statement.

I may have an example function which works with Excel in my files,
which I can post, but my recipe for creating the DLL is for g95 not
CVF.

-- elliot
-- e-mail: epc8 at juno dot com



.

Quantcast