Using fortran code with VBA
veda
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.
Wade Ward
"veda" <kedar.h...@gmail.com> wrote in message
news:1183165147.6...@o11g2000prd.googlegroups.com...
ability is greater than mine, then you might be able to dig that up with
author and keywords. Gary Scott (I believe) advised to instead use GINO to
make the front end, which next time I want an app with some bells and
whistles, I intend to do.
If you get desperate, he'll probably answer a polite, unsolicited e-mail
ep...@juno.com .
--
Wade Ward
--
e p chandler
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
e p chandler
See my other reply for general principles.
Here is my recipe for creating a DLL with g95 that contains a function
that takes the difference of two 32 bit integers and returns their
difference:
test.f is the code file, test.def handles exports and name mangling,
make.bat creates the DLL using g95. The various switches on the
command line are explained elsewhere in some of my previous posts.
test.f:
function isub2(i,j)
isub2=i-j
end
test.def:
EXPORTS isub2=isub2_
make.bat:
g95 -s -shared -mrtd -o test.dll test.def test.f
running make.bat leaves you with the file test.dll
put the DLL in a directory on the PATH or in one of
the directories that contains system DLLs
In Excel:
under tools / menu / visual basic editor
under insert / module
in module (general) (declarations) insert
Declare Function isub2 Lib "test.dll" (i As Long, j As Long) As Long
in a cell insert the formula
=isub2(a1,b1)
save the worksheet
re-open it and enable macros
type something into cells a1 and b1
The changes and steps you need to take with CVF are clearly documented
elsewhere. IIRC you need to add a comment line which contains
directives to the compiler. These specify the calling convention, what
functions to export and what name alias to use.
Craig Powers
>
> e. Error messages from VBA/VB are misleading. A "missing" DLL might in
> fact be malformed.
Most commonly in my experience, if VB claims that the dll is missing and
you're sure that it can actually find it, the problem is with a
dependency. Look up Dependency Walker, which is a great way to find out
what the dll needs and doesn't have. A common mistake would be to
distribute the dll without distributing the Fortran run-time library
(nor linking the full library into the dll using a static version of it).