Topic: Fortran from Excel

Your blogs 4 and 5 describe how to call Fortran from Excel via Visual Basic. To me, a novice, there seem to be gaps in these. These are old blogs However, I’ve got to these routines to average to numbers, which compile ok.

MEAN.FOR as follows:

Module MEAN_MODULE
    USE ISO_C_BINDING
    IMPLICIT NONE
CONTAINS
   FUNCTION MEAN(A,B) BIND(C, name=“mean”) RESULT (M)
      REAL(C_DOUBLE), VALUE :: A, B
      REAL(C_DOUBLE) M
      M = (A + B)/2.
   END FUNCTION
END MODULE


MEAN.DEF as follows:

LIBRARY MEAN
EXPORTS
   MEAN=“MEAN@16”

This gets … Generating Makefile Okay
But then ….  ld returned 1 exit status
               Last command making (target.exe) returned in bad status.

Any clue as to what I am missing, please.

  • jeff
  • Administrator
  • Offline

Re: Fortran from Excel

I can see a few problems in your post.  First, your function that you want to have callable from a DLL is inside a Fortran module.  Using a module to encapsulate DLL-exported functions really won't work well since the compiler is not generating a simple name for the function.  You want to move the function outside the module so it is standalone:

   FUNCTION MEAN(A,B) BIND(C, name=“mean”) RESULT (M)
      REAL(C_DOUBLE), VALUE :: A, B
      REAL(C_DOUBLE) M
      M = (A + B)/2.
   END FUNCTION

Second, the blog's instructions were for generating a 32-bit DLL.  There's a good chance that, hopefully, you're using 64-bit Excel, which simplifies everything greatly.  Specifically, the export definition where it adds the "standard call" decoration of @16 is no longer necessary.  You probably don't need to create an export definition file at all if you're in 64-bit mode.

Third, when you build your library, you'll notice that the compiler is attempting to make an executable named "target.exe."  It's almost certainly failing because your code does not contain any program unit.  You'll need to ensure you're doing two things:

1. Use a Project - You need to create a new project (Shared Library (DLL)) by selecting "New Project" in the Project menu.  After you set up and select a reasonable location for your project, possibly the same directory where your Fortran source code is, you need to add your Fortran source code to the project (again, in the Project menu, select "Add File(s)") and save the project.

2. Configure the Project - In Project Options, make sure that the Architecture is selected to match your Excel installation's architecture (32- or 64-bit) and the project type is set to "Shared Library."  Save your project after you make any changes.

After you build a DLL, you should be able to access it via Excel's Visual Basic for Applications interface.  However, accessing a DLL from Excel isn't a simple process.  It is actually quite complicated.  You'll need to make sure the Excel VBA code has the correct path to the DLL and that the function's signature is correct.  The process gets harder once you start passing arrays to Fortran.  It is completely possible, but there is a learning curve here that is significant.  I just wanted to warn you.

Jeff Armstrong
Approximatrix, LLC

Re: Fortran from Excel

Many  thanks, Jeff
It helped bundle.
The learning curve was indeed significant, steep, and long.
Roger

Re: Fortran from Excel

Re: Fortran from Excel

Jeff, Indeed there was a great deal to learn to link these. I now have it all running sweetly.

But ... How can I WRITE or PRINT from my Fortran DLL called from Excel, as this causes a disconnect.
AI tells me I should obtain SimplyFortran's files libgfortran-5.dll, and libgcc_s_dw2-1.dll and libqualmath-0.dll
I cannot find these. Can you please somehow get me over this hurdle.. Thank you

  • jeff
  • Administrator
  • Offline

Re: Fortran from Excel

Roger,

You won't be able to use WRITE or PRINT to standard output because there is no "standard output" available.  You can write to a file if you first use an OPEN statement, of course. 

When your Fortran code is called by Excel as a DLL, it is running within the Excel process.  Excel, though, is a true Windows program; it is not running with access to "standard output," usually a terminal or command prompt window in Windows.  When Fortran tries to print to "standard output" while running inside Excel, it has nowhere to send its text.

You would really need to either print to a file after you open it with an OPEN call or somehow send strings to Excel instead.

AI was telling you to copy the Fortran runtime library DLLs, which, in Simply Fortran's default setup, aren't even used (the runtime is statically linked within the DLL or executable).  The AI answer is nonsensical and has no bearing on your particular situation.

Jeff Armstrong
Approximatrix, LLC

Re: Fortran from Excel

Thank you for your advice.
As you recommend I added these statements to a smoothly working Excel/Fortran DLL.

        OPEN(UNIT=11,FILE='PAT4.DOC',STATUS='NEW')     
        WRITE(11,6601)
   6601 FORMAT(' ***** PRINTING FROM KAKA.DLL')
        CLOSE(11)

This gave me "Can't find DLL entry point FABEXCEL in ...KAKA.DLL"

Another level of complexity that I cannot fathom! Any further advice please!

  • jeff
  • Administrator
  • Offline

Re: Fortran from Excel

The error means that Excel can't find your Fortran procedure FABEXCEL in your DLL KAKA.DLL any longer.  I don't think adding the file open code causes this problem.  If you remove the code, I'm guessing the DLL still won't work.

Are you sure nothing else has changed?  Are you loading the proper DLL?

Also, I believe the compiler will convert all Fortran symbols (procedures and variables) to lowercase.  I would try to access fabexcel instead of the uppercase version.  Fortran is a case-insensitive language, but almost no other modern language shares this trait.

Jeff Armstrong
Approximatrix, LLC
  • adem
  • New member
  • Offline

Re: Fortran from Excel

First, sorry for my poor English. To put it short, I successfully  use Simply Fortran dlls with Excel 32 bits. The way is done is explained in blogs 4 and 5. It was not ease for me to make it work. It is needed to install the Microsoft Community Visual Studio. Since my PC is 64b I needed to find the folder HostX64, inside that folder there are two subfolders. I use the x86 subfolder since my Excel is 32b. The blog says that the command °lib /machine:i386 /def:fortran-library.def° will produce the file fortran-library.lib. What I did was to make a file called °AAALibreria.bat° to execute the command. The procedure is to compile the fortran dll, copy the resulting ° fortran-library.def° file
Inside folder x86, run the bat program and copy both produced files °fortran-library.lib° and °fortran-library.exp” in the folder containing the ° fortran-library.dll°. That is all.

Now my problem is that I updated the MS Office to 64 bits and so far I have not been able to produce the .lib file. Any idea?

  • jeff
  • Administrator
  • Offline

Re: Fortran from Excel

adem wrote:

Now my problem is that I updated the MS Office to 64 bits and so far I have not been able to produce the .lib file. Any idea?

Building DLLs becomes drastically simpler if everything is 64-bit on Windows.  But first, let's clear up some confusion about the need for some files:

The .lib and .def files are specifically needed for interaction with Microsoft's compilers.  The .lib file is an import library, used (but not "required") by Microsoft's linker to understand the functions available in the the DLL that was built with Simply Fortran.  The .def file is used to define "aliases" for Fortran procedures from the DLL in C code.  The aliases, though, are a convenience because Microsoft assumes DLLs use a specific calling convention (stdcall) for 32-bit DLLs that are accompanied by particularly ugly procedure names.

The blog posts go over some of the nastier details of 32-bit stdcall name mangling (the addition of the @ symbol to function names followed by the number of bytes passed into the procedure), but 64-bit Windows doesn't suffer from this insanity.

So for your use case, calling 64-bit Fortran from 64-bit Excel, we can simplify things:

First, because you're not interacting with any of Microsoft's compilers in this case, we don't need to create .lib or .def files at all.  Those steps were only needed to interact with Microsoft's compilers and to build 32-bit aliases for our function names that didn't look nutty.  You can skip all those steps.  Building your DLL in Simply Fortran is all you should need to do.

Second, 64-bit Windows DLLs conform to a single calling convention that doesn't rely on ugly name-mangling DLLs, and the calling convention is the same whether you're calling a Fortran function, a C function, a Pascal function, etc.  We can skip right ahead to writing the VBA definitions.  Once you build your DLL containing, for example:

function mean(x, n)
use iso_c_binding, only: C_PTR, C_DOUBLE, C_INT, c_f_pointer
implicit none

type(C_PTR), value::x
integer(kind=C_INT), value::n
real(kind=C_DOUBLE)::mean

real(kind=C_DOUBLE), dimension(:), pointer::fx

    ! Convert the C pointer of length n to an appropriate
    ! Fortran pointer
    call c_f_pointer(x, fx, [n])
    
    mean = sum(fx)/real(n, C_DOUBLE)
    
end function mean

You can immediately write your corresponding VBA definition:

Private Declare PtrSafe Function mean Lib "C:\Fortran\ExcelDll\fortran-library.dll" _
    (ByRef x As Double, ByVal n As Integer) As Double

And it should work as expected.

There shouldn't really be anything else involved.

Jeff Armstrong
Approximatrix, LLC