linking a .dll to Excel VBA

[daniel_r_bakergm_com]

I have fortran code that I would like to call as a subroutine in Excel VBA. I have followed numerous examples in the internet and on these forums without success. I have tried a simple example

      subroutine quad_F(a,b,c,x,y)
!DEC$ ATTRIBUTES DLLEXPORT, STDCALL, ALIAS:'quad_F' :: quad_F
      double precision a,b,c,x,y
! solves the quadratic equation a*x^2+b*x+c=0 for x
      x=(-b+sqrt(b**2-4*a*c))/(2*a)
      y=(-b-sqrt(b**2-4*a*c))/(2*a)
      z=1
      return
      end

compiled as a dynamic link library project dlltest.dll. I can link to it and call it successfully from another fortran program, but to do this I must link to the static library dlltest.lib in the calling program. There is no way to do this in Excel, so I think I am missing something. Here is the VBA I would like to use

Option Base 1
Declare Sub quad_F Lib "C:\Users\qz0wcd\Desktop\desktop\Li-ion literature\solid phase transport eqns\combined diffusion and stress\multi-species transport\dlltest\dlltest.dll" _
   (ByRef a As Double, ByRef b As Double, ByRef c As Double, ByRef x As Double, ByRef y As Double)
Public Function qtest(a As Double, b As Double, c As Double)
Dim p(2) As Double
Call quad_F(a, b, c, x, y)
p(1) = x
p(2) = y
qtest = p
End Function

I have Windows 7 on a 64 bit machine and I am using Visual Studio 2008 version 9.021022. Any suggestions would be appreciated. 

[IanH]

daniel.r.bakergm.com wrote:

I am a novice in this stuff and I'm not sure how to check all of the things that you mention. I tend to use the GUI instead of command line and here is what I see: ...  (3) the DLL is not linking to anything else ...

When I execute the VBA subroutine Excel generates a #VALUE! error, but I get no diagnostic from the Fortran. So I don't think that the Fortran code is ever being called. I did check that the VBA code runs up to the point where it calls the DLL and there it crashes.

Your Fortran DLL requires the Intel Fortran runtime library DLLs.  If those runtime DLLs cannot be found from within the context of the Excel process, then you will see the the behaviour you observe.

Typically the 32 bit and 64 bit variants of the Fortran runtime DLLs are installed in separate folders under as C:\Program Files (x86)\Common Files\Intel\Shared Libraries\redist\...  Are those folders there?  Windows uses the list of directories present in the PATH variable to locate implicitly loaded DLLs.  Are the folders containing the runtime DLLs in the value of PATH that Excel would have when it executes?  Typically things like language runtimes are installed on the system path, but each user account can add its own set of directories to the PATH.  If you open a command prompt that does not preconfigure its environment in some way (i.e. press and release the Windows-R keys, type cmd<enter> - but don't use the compiler provided command prompts) then type PATH<enter>, do you see the runtime folders listed in full in the displayed path?

[Steve_Lionel]

Your Fortran code looks ok. Are you sure the Excel declaration is pointing to the right location? The run-time DLLs Ian mentions should already be in PATH. One thought - I see you are building a Debug configuration. This will work only on a computer where Intel Fortran is installed. Are you copying the DLL to some other system to use in Excel?

[daniel_r_bakergm_com]

I have now tried to run this code on 3 different machines. It works on two of them but not on the new machine I have. The most convenient explanation for these problems is that I am giving VBA the incorrect directory to look for the DLL library, but I have checked this out, so that explanation won't work.

Ian, the folder Shared Libraries does not exist on my computers, even on those machines where the code runs, but the DLL libraries always wind up in the Debug folder for the project in question. Again, I have taken pains to make sure that the path to the DLL library is correct.

What bothers me the most about this problem is that the programs did execute from Excel on the day that I first installed the compiler. Then, several days later they no longer worked. Steve, the new machine does have a Fortran compiler and debugger installed on it. In fact, I can still compile link and debug programs on this machine. The only thing I seem to be unable to do is to link a DLL to an Excel VBA routine. Somehow something changed on this machine in the intervening days after the installation.

Next week I will have some IT guys come down to look at my new machine and see if they can find any problems. I will let you know if they find anything.

[Steve_Lionel]

Here's an easy thing to do. Download Dependency Walker, run it and open your DLL with it. Click FIle > Save As and save a .dwi file somewhere convenient. Zip this and attach the zip to a reply here. Some errors and warnings in Dependency Walker are expected.

Are you using the DLL on the same system where you built it?

[daniel_r_bakergm_com]

Steve, yes I am using the DLL on the same system where I built it. I am attaching the zip file you requested. Thanks

[Steve_Lionel]

Thanks. The one interesting bit is the reference to MSVCR100D.DLL as being in Windows\System32. That's the debug version of the MSVC run-time library and it doesn't belong in Windows\System32. Normally the debug DLLs aren't there - maybe you copied it in?

Try building your DLL as a Release configuration, or set the Libraries > Use Run-Time Library property to "Multithreaded /MT" and rebuild.

[daniel_r_bakergm_com]

Steve, I didn't copy anything in, this is the way it was apparently installed. For comparison, I am attaching the same file from another machine where it works. Does this tell you anything?

[Steve_Lionel]

Yes, this one is linked differently, referencing a side-by-side assembly of the Visual C++ 2008 run-time library, which is what I would expect. The non-working one is linked incorrectly, and someone would have had to make a specific configuration change to cause that to happen.

Please do this - on the non-working system, change the project property Linker > Show Progress to "Show all progress messages", do a rebuild, ZIP the buildlog.htm from the rebuild and attach it here.

[daniel_r_bakergm_com]

Here is the log you requested

[daniel_r_bakergm_com]

Try this again

[daniel_r_bakergm_com]

Steve, I'm sorry, I did this on the machine that works. I will now do it on the machine that doesn't work. My apologies for this.

[daniel_r_bakergm_com]

I'm confusing myself now. The file I sent you is from the machine that doesn't work. Please ignore my last post.

[Steve_Lionel]

Ok, thanks. This looks ok - I may have been mistaken about "incorrect linking". I see that the "doesn't work" one is using the Visual Studio Shell (which is fine.)

I think at this point I would try to step through the code in the debugger. It's a bit tricky to do this with a DLL - here are the basic steps:

1. In your DLL project, set Debugging > Command to the full path of EXCEL.EXE
2. Open your Fortran source and set a breakpoint at the first executable line of the function.
3. Press F5 to start debugging.
4. Excel will open. Do whatever you need in Excel to invoke the Fortran function

If all goes right, the debugger will stop in your function and you can examine the arguments to see if they're correct, and step through the code.

[DavidWhite]

What Steve is proposing normally works well - I do this frequently.

The main thing to remember is that you need to ensure that Excel is loading the debug version of the DLL, and that it is loading the latest version.  My Excel code expects the DLL to be in a particular location, so I need to copy the DLL from the Debug folder of the project to this location.  As long as the DLL copies are the same, the debugger does not complain.

DLL dependencies can be an issue moving the DLL to other machines.  I always build my DLL projects using static library includes so that I know that everything I need from the DLL is included, and so that my users do not need to install any other libraries.

[daniel_r_bakergm_com]

Steve, in Project Properties>Debugging>Command, I put in C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE. This path works in the sense that, when I click on it Excel opens and I can open the program that is calling my fortran. However, when I start the debugger, I break and get the message "Debugging information for EXCEL.EXE cannot be found or does not match. Cannot find or open the PDB file" It also asks me if I want to continue. I answered YES and Excel opened. I called my program, which executed correctly, but when it was time to return control to Excel I get "no source available.. No symbols are loaded for any callstack frame" It does give me a call stack location of

VBE7.DLL!53e27ab()

Does this help explain anything? Clearly there are some pointers that are not going to the correct places.

[Steve_Lionel]

That's normal behavior. Did it stop in your Fortran source and let you step through it? David's caution about making sure the DLL path is exactly the one you built in VS is very important.

[daniel_r_bakergm_com]

Yes, it stops the fortran and runs through it correctly. This time, I didn't attempt to step into the Excel at the end of the fortran, but simply hit continue, and the program in excel executed correctly! So I deleted the break points in the fortran, and it still executes correctly. I then saved the excel and restarted it. On restarting it, the original problem re-occurred. Can you interpret what is happening here?

[Steve_Lionel]

That's strange, and I am at a loss to explain it. Excel/VBA allows you to run in a debugger, doesn't it? I am not familiar with this so can't help with specifics.

The only thing I can think of is that there is some cell value being saved with the XLSX that is interfering with the call. Too bad the Excel is so vague about what the problem is. Maybe it has some sort of error reporting mechanism?

[daniel_r_bakergm_com]

Excel VBA does have its own debugger, and it was the first thing I tried to use. The VBA program executes correctly until it calls the fortran program and then it crashes. The fortran debugger does not seem to be able to access the excel code and the VBA debugger does not seem to be able to access the fortran code. But, if I have breakpoints in the fortran code, the whole thing seems to run correctly. Somehow calling the fortran with the debugger gets things to run, even after the breakpoints in the fortran are eliminated. It only crashes when I start it without using the fortran debugger.

[daniel_r_bakergm_com]

I now realize that I don't need breakpoints to get this to work. All that I need is to use the debugging command to point to the excel file and to start the whole program from the fortran debugger. Then, when excel comes up, I start the excel program that I want to use and everything seems to run. In some sense, one might argue that this is a fix for my problems, except that it is a bad fix and very awkward. Tomorrow I think we will try to re-install the fortran and hopefully these issues will be resolved. 

[DavidWhite]

Daniel,

If you can start the debugger from VS, open Excel and then call and break inside your DLL, but not get back to Excel after the call, it is possible that your code has corrupted the stack on the call.  E.g., the variable types and lengths, especially strings, if you are using them, are inconsistent across the VBA/Fortran call.

David