fortran DLL in excel 2010 (32 bit or 64 bit) in Windows 7 64 bit

[Massimiliano_C_]

Dear all,

I have read a few threads on this forum about this very problem and I think I have implemented all the suggestions but still cannot solve my problem.

Here is the problem: I used to have a DLL built in Intel fortran compiler 10 and I used to call it from Excel 2003 in my old WinXP laptop. That worked perfectly fine. My company gave me a new laptop with Window7 and Excel 2010 32bit on it and suddenly nothing works.

The first thing I noticed was that the Intel Fortran Compiler 10 was advertised by Win7 as not compatible and I thought that was the problem. I asked and obtained a new Intel(R) Visual Fortran Compiler XE 14.0.1.139 and immediately tried again to compile my dll. No success.

I created a minimum working case that still reproduces the problem and submitted to intel to ask for their help. They answered with a fix and I implemented the fix. Still nothing.

I removed the Excel 2010 32bit and substituted with Excel 2010 64bit, no improvement.

Since at this point I am lost I am posting my case hoping that someone will spot a problem:

Fortran code:

!  test_Dll1.f90 
!
!  FUNCTIONS/SUBROUTINES exported from test_Dll1.dll:
!  test_Dll1 - subroutine 
!
function test_Dll1(x,y,z)
implicit none
double precision :: x,y,z,test_Dll1

  ! Expose subroutine test_Dll1 to users of this DLL
  !
  !DEC$ ATTRIBUTES DLLEXPORT, STDCALL, REFERENCE, ALIAS:"TEST_Dll1_F" :: test_Dll1

  ! Variables

 ! Body of test_Dll1
 test_Dll1=x+y+z

end function test_Dll1

Visual basic for application code:

Private Declare PtrSafe Function TEST_DLL1_F Lib "C:\tmp\test_Dll1.dll" (ByRef x As Double, ByRef y As Double, ByRef z As Double) As Double
Function Test_Dll1(Arg1 As Double, Arg2 As Double, Arg3 As Double) As Double
 Test_Dll1 = TEST_DLL1_F(Arg1, Arg2, Arg3)

End Function

I manually copy the dll in the position specified to keep the path short. I tried to put it together with the excel file, in system32 and in a few other places with no success.

The funny thing is that excel writes #value! but doesn't raise any issue. If I try to debug from Fortran it never enters the dll and hence is never captured by the debugger.

The lack of error message on excel side puzzles me since I have no clue what it is wrong and what to fix.

I have also attached this MWE as a zip file.

Thanks,

Max

[TimP]

The incompatibility of the old ifort with win7 is probably only that the installer is likely to fail.  That wouldn't affect the validity of the .dll which was built previously.

I'm no expert on how Excel finds a .dll, but you seem to be shooting in the dark rather than reading docs.  System32 is where Windows X64 expects to find system .dll for 64-bit mode, so that could work only if you had a 64-bit mode Excel (unlikely) and you had built a 64-bit .dll.  Even if it worked, there would be questions about doing that as opposed to assuring that it is placed on user PATH.  

In win7 you also have security protections not present in XP, so you need to examine attributes, e.g. in File Explorer.

If your .dll refers to a .dll provided by ifort, you would required that the ifort or corresponding distributable library package is installed and the .bat has been run which adds it to PATH.  I suppose you could check the .dll for such dependency by dumpbin /dependents or dependency walker.

Windows does have the habit of quitting silently when not finding a .dll with proper permissions etc.

[Steven_L_Intel1]

When I open your Excel file, Excel tells me there is an error in your formula that would call the DLL, complaining of incorrect argument types - so it never gets as far as calling the DLL. I'm not an Excel expert so I am not sure what the fix would be. Also, I have no experience in using a formula to invoke a DLL - the example we provide uses a button one clicks.

[Massimiliano_C_]

Tim Prince wrote:

The incompatibility of the old ifort with win7 is probably only that the installer is likely to fail.  That wouldn't affect the validity of the .dll which was built previously.

That was also my impression but our IT department here wanted to remove that doubt by having a more recent version

Tim Prince wrote:

I'm no expert on how Excel finds a .dll, but you seem to be shooting in the dark rather than reading docs.  System32 is where Windows X64 expects to find system .dll for 64-bit mode, so that could work only if you had a 64-bit mode Excel (unlikely) and you had built a 64-bit .dll.  Even if it worked, there would be questions about doing that as opposed to assuring that it is placed on user PATH.  

So if you read my post I tried bot excel 2010 32 and 64 bits. I know that system32 is for 64bits and as soon as I had the excel 2010 64bits I compiled with 64 bits and placed the dll right there. My preference though lies with specifying the path in the VB call as I did and as you can see in the VBA snippet.

Tim Prince wrote:

In win7 you also have security protections not present in XP, so you need to examine attributes, e.g. in File Explorer.

This was my preferred suspect but I cannot find documentation about this. I tried to google it and couldn't really find anything useful. How to give permissions to a specific file or directory for dll to work.

Tim Prince wrote:

If your .dll refers to a .dll provided by ifort, you would required that the ifort or corresponding distributable library package is installed and the .bat has been run which adds it to PATH.  I suppose you could check the .dll for such dependency by dumpbin /dependents or dependency walker.

Windows does have the habit of quitting silently when not finding a .dll with proper permissions etc.

Dependency walker says that all the necessary stuff is there. Bear in mid that I am not even trying to run this on someone else pc but on mine where I compile and I have all the stuff.

It is worth nothing that I use the dll for computing the properties of water and steam and that I use excel to dimension and design things. I am not a professional programer.

Max

[Massimiliano_C_]

Steve Lionel (Intel) wrote:

When I open your Excel file, Excel tells me there is an error in your formula that would call the DLL, complaining of incorrect argument types - so it never gets as far as calling the DLL. I'm not an Excel expert so I am not sure what the fix would be. Also, I have no experience in using a formula to invoke a DLL - the example we provide uses a button one clicks.

Funny I see no error when I open the same thing that I uploaded. Can you point me to the example that you mention?

Thanks,

Max

[Steven_L_Intel1]

When I open the .xlsm file and enable macros, Excel displays a yellow warning triangle next to your formula cell A7 and says "A value used in the formula is of the wrong data type".  I can see it entering your macro and trying to call the DLL, but as you say, nothing happens and there is no error. I don't know what is going on here.

You will want to use 32-bit Excel and a 32-bit DLL.

The example we provide is called Excel and is in the MixedLanguage.zip archive underC:\Program Files (x86)\Intel\Composer XE 2013 SP1\Samples\en_US\Fortran Make sure you unzip this to your desktop or another writable folder, not under Program Files.

[Anthony_Richards · Accepted solution]

In your Fortran ALIAS statement, the name is given in mixed UPPER and LOWER case.

In the EXCEL, you refer to the name as all UPPERCASE, so EXCEL is looking for TEST_DLL1
but you have provided an alias 'TEST_Dll1', so EXCEL cannot find it.

You forgot to make the letter 'L' upper case in the name.

Change things to match and you will be OK.

[Massimiliano_C_]

Anthony Richards wrote:

In your Fortran ALIAS statement, the name is given in mixed UPPER and LOWER case.

In the EXCEL, you refer to the name as all UPPERCASE, so EXCEL is looking for TEST_DLL1

but you have provided an alias 'TEST_Dll1', so EXCEL cannot find it.

You forgot to make the letter 'L' upper case in the name.

Change things to match and you will be OK.

Wow this worked. I have re-read this thing 2 million times and always missed that. Thanks!! Many thanks!!

I really cannot express how grateful I am.

Now I'll go back to my real case and make it work.

Max

[Anthony_Richards]

You can avoid similar problems in future by always using UPPER CASE for EXCEL rouitnes that are called from an external
Fortran-compiled DLL.
You can then omit the 'ALIAS' attribute from your 'EXPORT' compiler directive, as Fortran forces UPPER CASE for exported symbols by default. Thus the exported symbols in the DLL will match your EXCEL calls automatically.