Calling A FORTRAN DLL From Excel & VBA

Using: Windows 8.1 © 2013 64 bit OS, x64-based processor MS Office 2013 x64

Download the example from https://myengineeringworld.net/2014/03/fortran-dll-excel-vba.html

Copy the “Calling A FORTRAN DLL From Excel & VBA.xlsm” to the folder ..\MySample\Release\x64

Open the .xlsm Click on the “Enable Content” button

In VBA window, add “PtrSafe” to both Public Declare… Save and Close

As seen in https://www.silverfrost.com/32/ftn95/ftn95_personal_edition.aspx just downloaded the compiler FTN95 Personal Edition (FTN95PE) version 8.61 from http://www.ftn95.co.uk/ftn95/ftn95-8.61/ftn95_personal.exe

Run 'C:\Program Files (x86)\Silverfrost\FTN95\plato.exe'

File > New > Project: Project Type: Fortran DLL Name: MySample

OK

In “Project Explorer”, right click on “Source Files”, click on “Add New Item”

Type: Free format Fortran file Name: MyCode

And paste the content of the downloaded MyCode.f90 in a new .f95 source file

Configuration: Release Platform: x64

Build > Build

So far so good: “Compiling file: MyCode.f95 C:\Users\BarRErA\Documents\MySample\MyCode.F95(17) : warning 197 - Variable C has been declared but not used C:\Users\BarRErA\Documents\MySample\MyCode.F95(17) : warning 197 - Variable D has been declared but not used C:\Users\BarRErA\Documents\MySample\MyCode.F95(51) : warning 197 - Variable I has been declared but not used Compilation completed with no errors. Linking... Creating dynamic link library C:\Users\BarRErA\Documents\MySample\Release\x64\MySample.dl”

When opening the .xlsm, Excel crashes or a exception is thrown.

“FATAL ERROR > Silverfrost exception handler has failed”

Commenting the FRICTIONFACTOR on the .f95 code, compiling again, then going to Excel > tab Sub > clicking RUN (i.e. running the VBA Sub CallDoubleArray()), it works. The problem is with the function call.

I have no clue about what to do. It is my first contact with FORTRAN, I’m an engineer that wants to translate some thermodynamic calculations from VBA to Fortran.

opening via Plato (32 bit) and compiling it again, I got this log:

'Silverfrost 64-bit exception report on C:\Program Files\Microsoft Office 15\Root\Office15\EXCEL.EXE Wed Apr 29 15:08:57 2020

Unknown exception (c004f012) at address 7ffd8cec908c

Within file KERNELBASE.dll In RaiseException at address 6C In RpcRaiseException at address 51 Within file RPCRT4.dll In RpcRaiseException at address 14 In NdrClientCall3 at address F6A In NdrClientCall3 at address FE Within file sppc.dll In SLUnregisterPlugin at address 993 In SLGetPolicyInformation at address 63 In Ordinal7162 at address 12B8 Within file mso.dll In Ordinal7162 at address 11B4 In Ordinal7162 at address 14C0

RAX = 000000391254e420 RBX = 00000000c004f012 RCX = 00007ffd8ecec750 RDX = 0000000000000000 RBP = 0000000000000003 RSI = 0000000000000000 RDI = 000000391254eaf0 RSP = 000000391254e670 R8 = 000000391254e420 R9 = 00007ffd66dc1438 R10 = 00007ffd8ecfe844 R11 = 000000391254e420 R12 = 0000000000000000 R13 = 0000000000000001 R14 = 000000391254ef28 R15 = 000000391254f228

7ffd8cec908c) mov_q RCX,[c0+RSP]'

I have also similar problem in calling 64-Bit Fortran dlls in 64-Bit Excel.

I used this simple Fortran code:

DOUBLE PRECISION FUNCTION test_code1(myarg1, myarg2)
   DOUBLE PRECISION myarg1, myarg2
   test_code1 = myarg1 + myarg2
END

I removed all the ftn95 options from config and compiled the code just by using /64 option [color=red:32b8446ee3](ftn95 test_code1.f90 /64)[/color:32b8446ee3] and linked the dll with slink64. The created dll was then transferred into the C:\Windows\System32 directory of another computer, in which 64-bit Excel is installed.

Next, in Excel(64-Bit) I created this VBA code:

Private Declare PtrSafe Function TEST_CODE1 Lib 'test_code1.dll' (ByRef arg1 As Double, ByRef arg2 As Double) As Double

Public Function test_sum1(arg1 As Double, arg2 As Double) As Double
   test_sum1 = TEST_CODE1(arg1, arg2)
End Function

and save the file with *.xlam extension. I opened Excel again and added the *.xlam file as an Add-in. Now everything is prepared to call test_sum1 function which interacts with Fortran dll. The Function works and the results are correct, but: - When you try to insert a diagram, excel crashes. Or - When you save your test file and open it again, after a while you get an access violation error message.

I am using Windows 10 Enterprise LTSC - Version:1809, Microsoft Excel 365 Version 2103, FTN95 Version 8.71.0

I am writing this post, to get some help regarding calling 64-Bit Fortran subroutines/functions in Excel.

In the previous post, I explained the process that I used and the corresponding error.

I think it is a compiler bug, but I am not sure about it. Therefore, if you have any experience in calling 64-Bit DLLs in 64-Bit Excel, I would be happy to hear about it.

Why do you think that your problem is caused by a compiler bug? Here is evidence to the contrary. The DLL that you built using FTN95 on the Fortran source has a well-defined API. Here is a C program that calls the function in the DLL using that API, and runs with no errors and prints the correct result.

#include <stdio.h>
extern double TEST_CODE1(double *a, double *b);
int main(){
double x = 10.2, y = 9.8;
double z = TEST_CODE1(&x, &y);
printf('Z = %e\n',z);
}

I did the following:

1. I built the DLL using your source code, and the commands

   ftn95 /64 test_code1.f90 slink64 /dll test_code1.obj /exportall

2. I built an import library for Visual C using the following DEF file:

   LIBRARY TEST_CODE1 EXPORTS TEST_CODE1

using the command

lib /def:test_code1.def /machine:x64

3. I compiled and linked the C program using the command

   cl tcode.c test_code1.lib

4. Running the resulting EXE produced the expected result.

I am not an Excel user and so I do not know if the version of Excel that you use does follow the same API used in my example and as declared in your VBA code.

Hi mecej4,

thank you so much for your reply.

As I mentioned in my first post, Excel can also call Fortran functions and the results are correct, but Excel crashes later on, as you try to insert a diagram, or when you save the project file, close it and open the project again.

There is a subtle difference between your C program and my Excel case. You start your program, call the fortran function, present the result and end it afterwards. However in my case, Excel should be able to keep working after calling the Fortran function without crashing. In other words, if I close Excel after getting the results (like your C program), nobody would notice that something is wrong.

[color=red:196dd822f1]'Why do you think that your problem is caused by a compiler bug?'[/color:196dd822f1] I also managed to call Fortran functions from a C++ program and later on in a C++ DLL, similar to what you did. But then as I imported the C++ DLL into MATLAB software, MATLAB crashed after a while with a silverfrost access violation error message. Here is the link to that topic.

Since these two 64-Bit cases are compeletly independent and both end up with access violation error, I thought that maybe it is a compiler bug. I should also mention that with 32-Bit Fortran DLLs and 32-Bit Excel, everything works fine.

The request to call a DLL developed with FTN95 from Excel 64 bit becomes more and more urgent for my company. Some of our customers roll out Office 64 bit and we cannot support this version.

We cannot say it's a problem with FTN95; we can also not state it's an MS Office problem. Instead it seems to be a problem with the interface or interaction between both components.

Description: The DLL calls from Excel work initially, i.e. we get the correct results of the functions. After some time, however, Excel behaves strange and stops working properly. The Excel response is not constantly the same, sometimes it don't give any response and one has to cancel the process with the Windows task manager. Sometimes an error message appears as described below:

Access violation (c0000005) at address aa6c1
Within file SALFLIBC64.DLL
in prepare_register_details(<ptr>char) at address 7a
in details_box(void) at address 3b
Within file CLEARWIN64.DLL
In _set_mg_return_value at address 8C3F
...

Who can possibly share his knowledge how to call a FTN95 based DLL from Excel 64 bit? Who has sucessfully resolved this issue?

Thomas

prepare_register_details and details_box are functions within salflibc64.dll. They were called when processing an exception that was raised within the ClearWin+ function _set_mg_return_value.

_set_mg_return_value is the internal name for set_mg_return_value@ which in theory was called from your Fortran code.

If you (or someone else) can provide code for a very simple project that demonstrates the failure then (given time) we might be able to investigate further.

Dear forum users, dear Silverfrost team,

The demand for support regarding FTN95 integration in MS Excel is really not new. Already in October 2012, i.e. 9 years ago, a sent the following request to the 'General' forum:

Quoted from Thomas Searching for advice, how to integrate FTN95 DLLs into 64 bit applications, I became aware of the forum's discussion about the interst in a 64 bit version of FTN95.

I can agree with many of the other members: Yes, there is a demand for a 64 bit version.

My background/characteristics:

• Using Salford/Silverfrost products for a very long time (FTN77, DBOS).
• Large amount of existing Fortran source code.
• Extensive use of ClearWin.
• Integration of DLLs, developed with FTN95, into other applications like MS Office, Delphi, VB.

Recently I had appreciated the development of DBOS to overcome the limitations of a 16 bit operation system and to make more memory available for development and application. I saw - and I am still seeing - Salford/Silverfrost as a front runner to support fortran programmers with application tools which are not directly related to a specific environment reagarding hardware and software and which are designed to fulfill the programmer's needs.

Today we are faced to the problem that the 64 bit development is progressing. More and more of our customers may use 64 bit versions of software products. Although 32 bit seems to be the default for e.g. MS office, 64 bit versions are available and some companies may decide to make use of them.

Therefore a request for 64 bit versions of DLLs will arise. I would prefer to have a solution based on Silverfrost FTN95 or FTN20xx. If there is a prospect of availability within reasonable time, I would appreciate. Otherwise I have to look for alternatives.

I'm still wondering, if my company, Steinmüller Engineering with Moji as co-author, is alone in the FTN95 community to be faced with this problem.

Thomas

Thank you for this enquiry.

I have carried out a brief initial investigation based on code supplied by Moji. If the failure reported by Moji is due to a fault in FTN95 then it may relate to the calling convention that is used (the passing of arguments on a stack and the cleaning of this stack after the call).

For Moji calls to FTN95 from Exel work initially but there is a crash after a few calls.

It would be helpfully to know if this behaviour (when calling from Exel) is experienced by others. Also it would help to know if the same problem arises when a stand alone Visual Basic program calls into an FTN95 DLL.

Paul,

As you asked for it, I created a stand alone 64-Bit Visual Basic program. This program calls a function from a FTN95 DLL and returns the result.

The program works fine and without any errors.

Moji

Please display the code for VB, for the Fortran and the SLINK64 script for the DLL.

The fact that it works for VB suggests that FTN95 is functioning correctly.

Here is the Fortran Code (test_code1.f90):

DOUBLE PRECISION FUNCTION test_code1(myarg1, myarg2)
   DOUBLE PRECISION myarg1, myarg2
   test_code1 = myarg1 + myarg2
END

The code is compiled using :

ftn95 test_code1.f90 /64 /-check

and linked by:

slink64 /file:test_code1.dll  test_code1.obj

The VB program is a windows Form program consisting of a form with 2 input boxes (TextBox1 and TextBox2), an output box (TextBox3) and a button (Button1):

Public Class Form1
    Private Declare Function TEST_CODE1 Lib 'test_code1.dll' (ByRef MYARG1 As Double, ByRef MYARG2 As Double) As Double

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim a As Double = 0
        Dim b As Double = 0

        If IsNumeric(TextBox1.Text) Then a = CDbl(TextBox1.Text)
        If IsNumeric(TextBox2.Text) Then b = CDbl(TextBox2.Text)

        TextBox3.Text = TEST_CODE1(a, b)
    End Sub
End Class

I am also interested in being able to transfer information between a 64-bit Fortran program and a .xlsx (multi-tab) file.

If a template of basic functionality can be demonstrated with FTN95 /64, that would be a useful achievment, worthy of a section in ftn95.chm !

John

For the moment you can use the information in the help files for 32 bit FTN95 with Visual Basic. This information is also in the Forum Knowledge Base.

Except one doesn't use F_STDCALL for 64 bits and a call to LoadLibrary is probably not needed.