linking a .dll to Excel VBA

linking a .dll to Excel VBA

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. 

 

45 posts / 0 new
Last post
For more complete information about compiler optimizations, see our Optimization Notice.

There is a worked example of calling Fortran from Excel in the compiler samples under MixedLanguage\Excel. You haven't said what goes wrong, but I notice that in the Fortran code you don't also specify REFERENCE in the ATTRIBUTES directive. Lacking that, with STDCALL that makes the compiler assume all arguments are passed by value, but you used ByRef in the VBA code.

 

Retired 12/31/2016

I am running Visual Studio 2008 version 9.021022 and the .sln file in the example you referred me to requires a more recent version of Visual Studio. Is there any way I can access the example from my outdated Visual Studio?

The examples are installed alongside the compiler (up until the 2017 version), so they will be in the oldest VS version supported by that compiler. The source files are there in any case. Which compiler version are you using? The last Intel Fortran version that supported VS2008 was 2013 SP1 (compiler 14.0)

Retired 12/31/2016

I am using compiler 9.0. I got your example up and running, which feels like major progress to me. Now all I have to do is trace back what the differences are between my example and yours and debug. I will post questions if I can't resolve. Thanks for your help!

You are probably not using version 9 of the Intel compiler, as that's from 2005 and doesn't support VS2008. The "9.0" you referenced earlier is the Visual Studio version, which is indeed the version number for VS2008.

Retired 12/31/2016

I think the fortran compiler is version 11.1. Does that make more sense?

Much more, yes. 11.1 supports VS2005 and 2008. You are many versions back from current, though, and I'd recommend an upgrade to the latest 17.0 (2017) version. Your Visual Studio version is also unsupported on current versions of Windows.

Retired 12/31/2016

Here is the example I got from you, which works

subroutine FortranCall (r1, num)

!DEC$ ATTRIBUTES DLLEXPORT, STDCALL, REFERENCE, ALIAS:"FortranCall" :: FortranCall

integer, intent(in) :: r1

character(10), intent(out) :: num

!DEC$ ATTRIBUTES REFERENCE :: num

num = ''

write (num,'(i0)') r1 * 2

return

end subroutine FortranCall

I am trying to morph this into something else and failing. Here is my morphed version

subroutine quad (r1, num)

!DEC$ ATTRIBUTES DLLEXPORT, STDCALL, REFERENCE, ALIAS:"quad" :: quad

integer, intent(in) :: r1

integer, intent(out) :: num

!DEC$ ATTRIBUTES REFERENCE :: num

num=r1**2

return

end subroutine quad

I call this from the following VBA function

Option Base 1
Declare Sub quad Lib "C:\Users\qz0wcd\Desktop\desktop\Li-ion literature\solid phase transport eqns\combined diffusion and stress\multi-species transport\dlltest\dlltest.dll" _
   (r1 As Integer, ByVal x As Integer)
Public Function qtest(r1 As Integer)
Dim x As Integer
x = 0
Call quad(r1, x)
qtest = x
End Function
 

When I make the call, Excel crashes. Can you tell me where the problem is?

 

You need "ByRef" in the VBA Declare for x. I would recommend ByRef for both arguments and adding REFERENCE for both in the Fortran code. I am not sure what VBA does if you leave off either ByVal or ByRef.

Retired 12/31/2016

This seems to be working and I am making progress! Is there any documentation concerning rules for using byRef and byVal, as well as rules for !DEC in the context of fortran to VBA or do we have to figure this out by trial and error?

Thanks again for your help.

The Mixed-Language Programming section of the Fortran documentation has tables about how arguments are passed/accepted for all the various combinations of attributes. You're on your own when it comes to what other languages do, though modern Fortran does have features specific to interoperability with C (not C++). I would start with the VBA documentation for its calling convention modifiers.

Retired 12/31/2016

I wasn't able to find this in the Intel documentation

"The Mixed-Language Programming section of the Fortran documentation"

Can you give me a link?

In any event, I seem to be up and running now (at least for the moment) and I am very grateful to you. Have a good weekend!

I don't know exactly where it is in 11.1, but our current (17.0) link is https://software.intel.com/en-us/node/678420   The particular table I was referring to is https://software.intel.com/en-us/node/678455

Retired 12/31/2016

This is very helpful. thanks as always.

I now have a new intel machine with Windows 7 enterprise, Visual Studio 2010, Microsoft Visual Web Developer 2010, Intel® Parallel Studio XE 2015 Composer Edition for Fortran. I am using a corporate license and, when I downloaded the programs on my new machine, there seems to have been a mismatch between my version of Visual Studio (2010) and my version of Fortran (2015). Nonetheless, when I started up some of my old programs and generated .dll libraries that could be called from Fortran, it all seemed to work. Now, a week later, I come back and the Fortran compiler is still working, it still generates the .dll libraries, but my Excel VBA programs are no longer able to access these libraries.Can anyone shed some light on this problem? If needed, I can provide a simple example of Excel and Fortran programs, which link fine on my old machine but no longer seem to be able to link on my new machine.

Some common causes for this:

1) You have selected an x64 configuration for the DLLs, which Excel won't see (since it is 32-bit)

2) You forgot that on the old computer you had set project properties to default to STDCALL conventions (/iface:cvf or stdref) and didn't do that for the project on the new computer.

3) The DLL links to some other DLLs not present

4) You gave an explicit path to the DLL in Excel which is different on the new computer.

You don't say what goes wrong - is there an error message?

Steve (aka "Doctor Fortran") - Retired from Intel

Steve,

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: 1) Under Project Properties I see Platform Active(Win32) so I don't think I am generating a 64 bit file (2) I don't know how to set project properties to default to STDCALL conventions. Can you tell me how to do that in my GUI? (3) the DLL is not linking to anything else (4) I have checked the explicit path to the DLL in Excel and it is correct.

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.

Thanks, as always, for your help

Here are some of the command lines used to compile and link the program. I copied them from the GUI

/nologo /debug:full /Od /warn:interfaces /module:"Debug\\" /object:"Debug\\" /Fd"Debug\vc100.pdb" /traceback /check:bounds /check:stack /libs:dll /threads /dbglibs /c

/OUT:"Debug\qtest.dll" /INCREMENTAL:NO /NOLOGO /MANIFEST /MANIFESTFILE:"Debug\qtest.dll.intermediate.manifest" /MANIFESTUAC:"level='asInvoker' uiAccess='false'" /DEBUG /PDB:"C:\Users\qz0wcd\Desktop\debugtest\qtest\qtest\Debug\qtest.pdb" /SUBSYSTEM:WINDOWS /IMPLIB:"C:\Users\qz0wcd\Desktop\debugtest\qtest\qtest\Debug\qtest.lib" /DLL

Does this help?

 

Ok, show me the Fortran code of your function. I only need to see the subroutine/function line and all the statements until the first executable line. (If the function is small, show the whole thing. 

Steve (aka "Doctor Fortran") - Retired from Intel

Here it is

subroutine quad (a, b,c,x,y)

!DEC$ ATTRIBUTES DLLEXPORT, STDCALL, REFERENCE, ALIAS:"quad" :: quad

double precision, intent(in) :: a

double precision, intent(in) :: b

double precision, intent(in) :: c

double precision, intent(out) :: x

double precision, intent(out) :: y

!DEC$ ATTRIBUTES REFERENCE :: x

!DEC$ ATTRIBUTES REFERENCE :: y

 

if(b**2-4*a*c.lt.0d0) then

x=-1

y=-1

else

x=(-b+sqrt(b**2-4*a*c))/(2*a)

y=(-b-sqrt(b**2-4*a*c))/(2*a)

endif

return

end subroutine quad

Quote:

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?

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?

Steve (aka "Doctor Fortran") - Retired from Intel

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.

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?

Steve (aka "Doctor Fortran") - Retired from Intel

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

Attachments: 

AttachmentSize
Downloadapplication/zip qtest.zip104.61 KB

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.

Steve (aka "Doctor Fortran") - Retired from Intel

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?

Attachments: 

AttachmentSize
Downloadapplication/zip qtest_functioning.zip99.53 KB

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.

Steve (aka "Doctor Fortran") - Retired from Intel

Here is the log you requested

Try this again

Attachments: 

AttachmentSize
Downloadapplication/zip BuildLog.zip3.27 KB

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.

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

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.

Steve (aka "Doctor Fortran") - Retired from Intel

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.

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.

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.

Steve (aka "Doctor Fortran") - Retired from Intel

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?

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?

Steve (aka "Doctor Fortran") - Retired from Intel

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.

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. 

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

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...

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.

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.

Leave a Comment

Please sign in to add a comment. Not a member? Join today