Calling Intel Fortran DLL from VB in Excel 2010

[mark_randolph]

I am just switching from Compaq Visual Fortran (which seems incompatible with Windows 7) to Intel, but cannot get my DLLs to work from Excel (Office 2010). I have been doing this successfully for years, just putting the DLL on the local directory and directing Excel to it (e.g. by doing a "save as"). Now, I get silly "File not found" (Error 53) or other messages (such as "entry point not found") about 95 % of the time, yet 5 % of the time it works!! Here is my simple example to try to explore this:

Intel Fortan: subroutine XtimesY(x,y,z) (built to "Fortrial.dll")

subroutine XtimesY(x,y,z)

!DEC$ ATTRIBUTES DLLEXPORT,STDCALL,DECORATE,ALIAS:"XTIMESY":: XtimesY

!DEC$ ATTRIBUTES REFERENCE::x

!DEC$ ATTRIBUTES REFERENCE::y

!DEC$ ATTRIBUTES REFERENCE::z

real*4 :: x,y,z

z=x*y

RETURN

END

VB code:

Declare Sub XtimesY Lib "Fortrial" Alias "XTIMESY" (ByRef Par1 As Single, ByRef Par2 As Single, ByRef Par1 As Single)

_______________________________________________________________________________________________________

Sub Macro1()

Dim xx As Single, yy As Single, zz As Single

xx = Worksheets("Computations").Range("C7").Value

yy = Worksheets("Computations").Range("d7").Value

Call XtimesY(xx, yy, zz)

Worksheets("Computations").Range("e9").Value = zz

End Sub

This gives me Error '53': File not found: Fortrial (as I say, about 95 % of the time, but I have got it to work on occasions). As a check, I have compiled the same Fortran code in Compaq (on another computer, and transferred it), and it works fine.

The problem may be in which Fortran library is bound. At present I have set the runtime library to: "Debug Multithread DLL (/libs:dll /threads /dbglibs)" and all other library options to "No".

I'd be grateful to any help! Thanks.

Mark Randolph (very limited software knowledge and zero Visual Studio experience!).

[DavidWhite]

Have a look in my reply to this thread

David

[mark_randolph]

David

Thanks for responding. I thought I was pretty close to what you had suggested previously (since I had seen that thread already, but was not sufficiently experienced to join the discussion). Anyway, I implemented more precisely what you suggested, namely adding "_F" to the subroutine name everywhere, and turning x, y, and z into x(1), Y(1) etc:

subroutine XtimesY_F(x,y,z)

!DEC$ ATTRIBUTES DLLEXPORT, STDCALL, ALIAS:'XtimesY_F' :: XtimesY_F

real*4 :: x(1),y(1),z(1)

z(1)=x(1)*y(1)

RETURN

END

This worked, and the subroutine was successfully called from Excel VB (I tried it several times). THEN, I changed the x(1)*y(1) to x(1)**y(1), recompiled in exactly the same way, copied the DLL across into the right directory after first exiting Excel, opened the Excel file again and redirected Excel to that directory (using Save As)and tried to call the subroutine . . . and got the Error 53, file not found "Fortrial.dll" message! So far I have not been able to get it working again.

I have been through this loop a few times by now (over 2 weeks), and many times after I change some of the ATTRIBUTE details (such as renaming the subroutine with the _F extension) it appears to manage to make its way through the VB-DLL defenses and works OK, but then any further change to the content of the subroutine sends me back to square one, with the file not found error.

Do you have any light to shed on which Runtime library I should nominate when compiling the Fortran? I still suspect that may be part of the problem.

Mark

[anthonyrichards]

I would try specifying the full path to 'Fortrial.dll' in your VB declaration. Why not specify the folder where it is created?

[DavidWhite]

re libraries, I use the Multithreaded option (/libs:static /threads). This way you do not need to redistribute the runtime libraries separate to the DLL.

My installer also adds the path where the DLL is installed, I use no path within the VBA code, otherwise the VBA would need to be changed depending on where the user installed the application.

e.g.

Private Declare Sub EtOHLiqDens_F Lib "AWAProps.dll" _
(TempC As Double, Value As Double, ByVal Units As String)

David

[mark_randolph]

Again, thanks for responding and your suggestions.

I have tried the Multithreaded option (which I agree sounds the most appropriate), with and without Debug, but neither work at present (indeed, have not managed to avoid the "file not found" error ever since my chance success). I was, however, suprised that you specify the "static" Multithreaded option, since I had assumed I would need the "dynamic" (/libs:dll etc) one. Incidentally, I have just tried the static Multithread, and managed to change the error message to "Run-time error 453: Can't find entry point XTIMESY_F in Fortrial.dll", but don't seem able to get past that.

I am concerned that the DLL, about 20kb in size, is a lot smaller than the equivalent compiled with Compaq VF (110 kb), so I still suspect it is not binding sufficient of the Fortran libraries but cannot work out why not. Admittedly, it is only a single line program, but I would have expected a larger minimum size DLL. I have tried various options such as including the full path, or with and without ".dll" in the library filename, and had no success (and I don't really think it is a VB issue, since the Compaq VF compiled DLL works fine).

Mark

[IanH]

Static linking might avoid problems with the OS loader not being able to find the fortran runtime DLL's when it is loading your DLL.

To avoid confusion, make sure there is only one copy of your DLL on your system.

You can use the dependency walker tool (depends.exe - I think its a utility that comes along with a visual studio install) to see if that entry point is actually exported by the DLL.

If you don't want to play games with the current directory (which sounds a bit flaky to me), you can use the fact that windows checks the names of already loaded DLL's first (before it goes looking through the file system) when it is asked to load a DLL without path information. If your VBA code is in a particular workbook, then use the Workbook_Open and Workbook_Close events to assist.

[vb]Option Explicit

' Win32 specific?
Private Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" _
    (ByVal lpFilename as String) as Long

Private Declare Function FreeLibrary Lib "kernel32 _
    (ByVal handle as Long) as Long

' Handle to the DLL saved during the workbook open
Private dll_handle as Long

Const my_dll_name as String = "MyCalcs.dll"

' Work book open event - loads the DLL from a defined set of paths.
Private Sub Workbook_Open()
  Dim path as String
  '****
  ' Try PATH/system/current directory etc.  Perhaps this should be last.
  dll_handle = LoadLibrary(my_dll_name)
  If dll_handle <> 0 Then Exit Sub

  ' Try workbook directory
  path = ThisWorkbook.Path
  If Right(path, 1) <> "" Then path = path & ""
  dll_handle = LoadLibrary(path & my_dll_name)
  If dll_handle <> 0 Then Exit Sub

  ' Try some other place, which you could get from a registry key.
  dll_handle = LoadLibrary("C:CatDogChicken" & my_dll_name)
  If dll_handle <> 0 Then Exit Sub
  
  Call MsgBox("Error: " & my_dll_name & " was not loaded :(")
End Sub

Private Sub Workbook_Close()
  Dim rc as Long
  If dll_handle <> 0 Then rc = FreeLibrary(dll_handle)
End Sub[/vb]

You then declare the subrotuines and function in VBA that are in your DLL as before, just using the library name with no path information.

[DavidWhite]

Dependency walker is a great tool for checking that entry points in your DLL's and have the case and decoration that you expect. Helps when you add a new routine and forget to export the entry point :-(

It is also available from dependencywalker.com

David

[mark_randolph]

Thanks Ian (and Dave) for your responses, which together have helped me crack the problem. I mistakenly thought that the default Fortran library when you start a new DLL project would be sufficient, but this has turned out not to be the case. Using Depends (very grateful for this tip), it appeared unable to locate some of the referenced Fortran libraries (such as libmmdd.dll), even though these are in several places on my computer. Switching to the (static ?) Multithread library (either debug or not) led to a much larger DLL file, which has worked fine, and the only significant error turned up by Depends was being unable to locate IESHIMS.DLL (also on my computer, not sure why it is needed, but did not seem to matter). Dave, you mentioned using the static Multithread library previously and I thought I had tried it, but obviously there must have been another glitch at the time. Now, it seems to work consistently.

Ian, thanks also for your detailed instructions regarding setting paths to ensure the DLL is found.

I think this just about closes this thread - once again, many thanks for all your help.

Mark

[DavidWhite]

Mark,

Check the documentation at dependencywalker.com - I think the IESHIMS issue is a broken MS DLL.

Just remember when you are using Debug mode that the Intel debug runtime DLL's are not distributable to 3rd parties, only the Release version DLL's.

I use the Static linking of the library files, as this means that you avoid users of your App needing to separately install the runtime libraries. Minimizes the problems like you had where the libraries are not found.

Just keep a track of the settings you use and that work for this project - when you start a new project from scratch, the default settings will again apply, and you will need to repeat what you have done here.

Regards,


David

[mark_randolph]

Dave

Yes, I think there is still a residual problem with the IESHIMS.DLL; I have tried 3 or 4 different DLL programs, and they all show the same Depends problem with this missing element, but three out of four of them run fine on my Wndows 7 (64-bit environment). The fourth did not initially, but when I copied IESHIMS.DLL to the local directory, the DLL was successfully called from Excel.Interestingly, 2 out of the 4 workedwhen run on my previous notebook (Windows XP and Excel2003), while copying IESHIMS got one more working, with just one program that remainsa problem ("Can't find DLL entry point", error 453) inthe older XP environment.

I havegathered quite a bit of experience over the last few days, though not sure I could say I understand much more -but interesting nonetheless!

Regards

Mark

[Steven_L_Intel1]

Ignore any messages about IESHMS.DLL. It is harmless.

[anthonyrichards]

Have you tried replacing

Declare Sub XtimesY Lib "Fortrial" Alias "XTIMESY"

with

Declare Sub XTIMESY Lib "Fortrial.dll"

?

Have you tried adding

MsgBox "Error code is :" & Err.LastDllError

to your VB code in the EXCEL module?