Intel® Fortran Compiler
Build applications that can scale for the future with optimized code designed for Intel® Xeon® and compatible processors.

linking a .dll to Excel VBA

daniel_r_bakergm_com
2,955 Views

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. 

 

0 Kudos
46 Replies
Steven_L_Intel1
Employee
1,569 Views

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.

 

0 Kudos
daniel_r_bakergm_com
1,569 Views

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?

0 Kudos
Steven_L_Intel1
Employee
1,569 Views

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)

0 Kudos
daniel_r_bakergm_com
1,569 Views

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!

0 Kudos
Steven_L_Intel1
Employee
1,569 Views

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.

0 Kudos
daniel_r_bakergm_com
1,569 Views

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

0 Kudos
Steven_L_Intel1
Employee
1,569 Views

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.

0 Kudos
daniel_r_bakergm_com
1,569 Views

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?

 

0 Kudos
Steven_L_Intel1
Employee
1,569 Views

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.

0 Kudos
daniel_r_bakergm_com
1,569 Views

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.

0 Kudos
Steven_L_Intel1
Employee
1,569 Views

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.

0 Kudos
daniel_r_bakergm_com
1,569 Views

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!

0 Kudos
Steven_L_Intel1
Employee
1,569 Views

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

0 Kudos
daniel_r_bakergm_com
1,569 Views

This is very helpful. thanks as always.

0 Kudos
daniel_r_bakergm_com
1,569 Views

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.

0 Kudos
Steve_Lionel
Honored Contributor III
1,569 Views

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?

0 Kudos
daniel_r_bakergm_com
1,569 Views

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

0 Kudos
daniel_r_bakergm_com
1,569 Views

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?

 

0 Kudos
Steve_Lionel
Honored Contributor III
1,569 Views

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. 

0 Kudos
daniel_r_bakergm_com
1,323 Views

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

0 Kudos
Reply