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]

What do you mean by "crashes"?  Be specific.

Running something under the debugger from within Visual Studio results in a different environment for the thing being run, compared to just running it from my programs or whatever.  In particular, you will have a different PATH...

[daniel_r_bakergm_com]

Ian, I think you have pointed out the main difference. In the debugger, there is a different PATH. When I say it crashes, I mean that the fortran program executes properly and correctly computes the output variables, but it is unable to pass them back to the VBA. As a result, the VBA routine gets a #VALUE! error. However, when it runs from the debugger, it is able to pass the values of the output variables back to the VBA and everything executes properly.

In this regard, should there be a difference in execution time when I run the program from debugger as opposed to simply calling the DLL from Excel? All of this assumes, of course, that I can call it from Excel, which I can on my old machine.

[daniel_r_bakergm_com]

Guys, my IT people were here today to look at this problem, and it appears that they found a solution. There is an Excel Add-in called Analysis ToolPak that had been installed on my old machine and was not installed on my new machine. Once it was installed on the new machine, the DLL seems to run without problems. Not sure why this is necessary, but it seems to work, at least for now. Thanks to all of you for your help.

[Xu__Qinwu]

Dear Steve, I am having a similar or different problem. When I run the excel file to call the Fortran DLL in the VBA code. It says "can not find the file (fortran dll)". However, I am able to debug inside the Fortran code to open excel and read inputs and then output results to excel.  Do you have any recommendations? Thank you.

[Steve_Lionel]

In most cases, when VB or VBA says that it cannot load a DLL, what it really means is that it can't load a DLL that your DLL is dependent on. This happens most often if you build a debug configuration but run Excel separately. There is a complete worked example of calling Fortran from Excel in the samples bundle. If you're debugging, what I usually recommend is to go to the Debugging property page in your DLL project and set the command to start as the path to excel.exe (or whichever executable calls your DLL.) You will need to set a breakpoint in your DLL routine.