Using Fortran DLL with Excel VBA

I am not an experienced programmer. I program out of necessity to implement scientific algorithms for subsurface fluid flow (oil industry). Since many years I have been writing Fortran 90 code, compiling it using Fortran Powerstation 4.0 (this is a very old 32 bit compiler), producing DLLs and accessing them from Excel using a VBA i/o interface. All this worked (and still works) pretty well.

Recently I have been implementing a rather complex model, so the runtimes on a single processor on my normal Win32 machine became too long. Still, also for this problem, the method I described above works well, just too slow.

After some reading, I decided to move my project to a more powerful computer (an 8 core Xeon with Nvidia GPU) with Win64 and the PGI Fortran compiler.

Since I did this, the interface between the DLL (e.g. proj1.dll) and the VBA code does not work anymore. When running the program in the new 64 bit Excel I get the message “file not found – proj1.dll“. I hardwired the path of the DLL in the VBA code, so it should find it.

I compile the DLL using:
pgfortran -Bdynamic -c file1.f90
pgfortran -Bdynamic -c file2.f90
pgfortran -Bdynamic -c file3.f90
pgfortran -Bdynamic -c file4.f90
pgfortran -Bdynamic -c file5.f90
pgfortran -Bdynamic -c file6.f90
pgfortran -Mmakedll file1.obj file2.obj file3.obj file4.obj file5.obj file6.obj -o proj1.dll[/i]

There are no compiler or link errors; the compiler produces the DLL. I did not add any compiler directives to the Fortran code other than the DLLEXPORT (the same as I was doing before):
!DEC$ ATTRIBUTES DLLEXPORT :: subroutine

Can anybody please help me solve this problem?

Hi CristianE,

I’ve never tried calling a Fortran DLL from VBA before myself, but doing a web search, I found the following post on StackOverflow: excel - VBA Calling C++ DLL - error 48 (file not found) - Stack Overflow

What error number are you getting? Per this post, if it’s 48 then the file not found may mean that it can’t find the dependent PGI Fortran runtime libraries rather than the proj1.dll itself.

Again, I don’t know VBA myself, but if there’s a way to add a PATH to the PGI library directory so it can find the dependent libraries, please set it. Otherwise, you might try copying the DLLs from the PGI REDIST directory to the same directory as your DLL (i.e. “C:\Program Files\PGI\win64\19.10\REDIST”).

Another thought, I wondering if maybe VBA is compiling to 32-bits? If so, the 64-bit proj1.dll may be incompatible.

-Mat

Thank you Mat! I am glad that I am not alone with the problem any more.
The error code is 53.
I did some reading as well. There are suggestions about the runtime PGI libraries… I found the advice to compile using -Bstatic instead of -Bdynamic. It sounds logical, but it didn’t work. I will copy the DLLs to the project directory as next step…

I also found the recommendation to use the /iface:cvf option (this is Intel). As I understand, in PGI world this would be equivalent to -Miface=cvf; this is supposed to use calling conventions compatible to Compaq Visual Fortran. The trouble is that PGI does not have the “cvf” option.

I am still fighting :-).

Cristian

PS:
Mat, I did as you advised … By copying the Redist DLLs to my project directory the problem goes away (for a moment); so VBA now finds the project DLL. However, when I re-calculate the model the program goes in what it looks like an endless loop… I mean such programming mistakes can always happen, but the model calculates ok under x32…
I am not sure what information I should forward, to enable anybody to suggest a way forward.

Cristian

The error code is 53.

Hmm, if the post is correct, then this means it can’t open the proj1.dll. Though it could mean the dependent libraries as well.

I found the advice to compile using -Bstatic instead of -Bdynamic. It sounds logical, but it didn’t work.

Doubt it would since you can’t dynamically open a static library so VBA wouldn’t be able to open it.

The trouble is that PGI does not have the “cvf” option.

No, we wouldn’t have anything like that. My memory may be off here, but I thought Intel bought CVF back in the early 2000s and then turned it into Intel Visual Fortran, so it would make sense for them to have such a flag. Though I high doubt this would be an issue for your program.

I did as you advised … By copying the Redist DLLs to my project directory the problem goes away (for a moment); so VBA now finds the project DLL.

Ok, so I was probably correct that VBA just couldn’t find the dependent runtime libs.

However, when I re-calculate the model the program goes in what it looks like an endless loop… I mean such programming mistakes can always happen, but the model calculates ok under x32…
I am not sure what information I should forward, to enable anybody to suggest a way forward.

Hard to tell though going form 32-bits to 64-bits in Fortran usually wasn’t a problem.

I’d probably attack this from two directions.

First write a Fortran and/or C driver to call your routine directly and then from the DLL. This should check if the routine is ported correctly from 32 to 64-bits.

Second, write a very basic routine in a DLL that you then call from VBA.

Between the two, hopefully helps determine if the issue is with the routine itself, or with the DLL call.

-Mat

Dear Mat, I reply late; I’ve been travelling. Yes, it seems that now the files are where they should be and can be found by the calling program.
I suspect that by switching from x32 to x64 there is a data type (i.e. length) mismatch… probably the length of integers (long in VBA). I’ll need to go step by step with simple routines and make sure that the data is read and transferred to the DLL properly. I was hoping for a magic shortcut, but there isn’t any…
Thank you for your help! I’ll let you know as I progress.

Cristian

Dear Mat,
I wrote a very simple DLL-Excel application. The Fortran DLL code is this:

	subroutine DUMMY1(x, y)	
	!DEC$ ATTRIBUTES DLLEXPORT :: DUMMY1
	!DEC$ ATTRIBUTES ALIAS:'DUMMY1' :: DUMMY1

	integer	x
	integer	y

	y = x

	return
	end

I compile and link the code using this:

pgfortran -Bdynamic -c simpleF.f90
pgfortran -Bdynamic -Mmakedll simpleF.obj -o S_LIB.dll

So I generate the S_LIB.dll with only one subroutine in it (DUMMY1).
I access the DLL from VBA with this code:

Public Declare PtrSafe Sub DUMMY1 Lib "C:\TMP\HydroMap\DEV\Simple\S_LIB.dll" (ByRef x As Long, ByRef y As Long)

Option Base 1
Option Explicit


Sub CY()

Dim INPX As Range
Dim OUTY As Range
Dim x As Long
Dim y As Long

Set INPX = Range("INPX")
Set OUTY = Range("OUTY")
x = INPX(1, 1).Value
y = 0
Call DUMMY1(x, y)
OUTY(1, 1).Value = y

End Sub

The VBA code has input / output interface to Excel through two named ranges (INPX and OUTY). So all I want is to pass an integer value (X) to VBA, call the DUMMY1 subroutine in the DLL, set Y = X, pass the Y value back to VBA and write it in the named range OUTY in the Excel table.

The DLL, together with the PGI redistributable libraries and the Excel file are in the same directory (c:\TMP\HydroMap\DEV\Simple), which is hardwired in the VBA code.

When I run the VBA code I get the same error as before (Error code 53: cannot find file “S_LIB.dll”).

I am running out of ideas… Do you have an advice for me?

Cristian

Dear Mat,

After some additional reading, I checked my DLL with a Dependency Walker. I found that my DLL (S_LIB.DLL - see my last message) finds all the necessary PGI DLLs (I copied the redistributable DLLs to the project directory), but there is a lower level set of DLLs (134 DLLs), most of them named API-MS-WIN-*.DLL and a few named EXT-MS-WIN-*.DLL, which my DLL cannot find.
I cannot imagine that finding and copying all these DLLs in the project directory is the preferred option. This looks to me more like an installation problem.

Some additional information:

  • I am using Widows 10


  • I have installed Visual Studio 17 before installing the PGI compiler.

Do you have an advice for me, what I need to do?

Cristian

Hi Cristian,

While I was able to recreate the error, I’m not familiar enough with VBA to know how to fix it. I thought it may be because I’m using a 32-bit Excel, but I went back and built the DLL with our 16.10 release, the last one where we supported 32-bits, but still got the same error.

Still seems like VBA can’t find a dependency, even with the REDIST DLLs copied to the local directory. Though the Microsoft docs don’t indicate how to add dependency locations to search (I’m looking at: Access DLLs in Excel | Microsoft Docs).

Info on the error can be found at: Error in loading DLL (Error 48) | Microsoft Docs

Note that I was able to write a simple Fortran driver and it was able to call the DLL routine without issue. So I think the error has to do with VBA rather than the DLL itself but I’ve taken it as far as I can. Sorry I couldn’t find a solution for you.

-Mat

Dear Mat,

It seems to be an unusual application for the developer community; very difficult to find a solution on the web (the distance between PGI Fortran and Excel VBA is too large :-)).
When looking at the list of unresolved dependencies in my DLL and locating these DLLs on my computer it becomes clear that they are all part of the Windows SDK or VS platform. The PGI DLLs are found.
When I am using my old MS Fortran compiler (x32) this problem is not there. In a way this can be expected since all components come from the same stable (i.e. MS).
I am just not computer literate enough to understand all this, but maybe the document below gives some hints:

… if you could have a look…

Cristian

It seems to be an unusual application for the developer community;

Agreed. This is the first time I’ve heard of anyone doing this.

I am just not computer literate enough to understand all this, but maybe the document below gives some hints:

Not much useful info here. Basically just gives some simple instructions on how to create DLLs and export symbols, which you’re already doing via the DEC directives. Doubt that this is the problem.

When I am using my old MS Fortran compiler (x32) this problem is not there. In a way this can be expected since all components come from the same stable (i.e. MS).

What you may try is to remove both PGI and Fortran from the equation and try creating a simple DLL using MS C++. I’m suspecting that the issue may be that Excel is a 32-bit binary (at least mine is) and may have issues calling a 64-bit DLL. So seeing if you get the same issue with a MS C++ built 64-bit DLL, then you might have better luck getting help from the wider MS community. If you can get that to work, then we can see if any suggestions can then be applied to PGI Fortran.

-Mat

Dear Mat,

I am using Fortran DLLs together with VBA Excel since many years. The configuration so far was Windows 7 (x32), Excel 2013 (x32) and MS Developer Studio Fortran PowerStation 4.0 (1994) also x32. This works without any problems.

I moved since January to a more powerful computer (a Dell precision with NVIDIA) with Windows 10 (x64) and Excel 2019 (x64). Therefore my x32 DLLs do not work anymore. I changed the platform because I am developing a larger and more complex model, which is too slow on the old platform (it works though). Since I want to use GPU acceleration, the PGI Fortran compiler seems to me the obvious choice (I actually didn’t find any alternative). I also found on the web reports of people using the Intel Fortran compiler for DLLs used with Excel x64 (also the GNU compiler), but no reports using the PGI compiler. This is why I came to believe that the problem I am encountering must be specific to PGI… some sort of compiler directive which must be specified…

The link I’ve sent you in my last message was specifying following:

The way the name is decorated depends on the language and how the compiler is instructed to make the function available, that is, the calling convention. The standard inter-process calling convention for Windows used by DLLs is known as the WinAPI convention. It is defined in Windows header files as WINAPI, which is in turn defined using the Win32 declarator __stdcall.

A DLL-export function for use with Excel (whether it is a worksheet function, macro-sheet equivalent function, or user-defined command) should always use the WINAPI / __stdcall calling convention. It is necessary to include the WINAPI specifier explicitly in the function’s definition as the default in Win32 compilers is to use the __cdecl calling convention, also defined as WINAPIV, if none is specified.

This is the part I don’t understand and I thought it might be important…

I also have an additional question: I see on the PGI web page that I have the option of purchasing support on an yearly basis together with the Professional license. Is this option more likely to help me move forward with this? It is really important…

I thank you for your help and I hope you don’t lose your patience with me any time soon :-).

Cristian