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

Beginner
1,393 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

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.

46 Replies
Employee
820 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.

Beginner
820 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?

Employee
820 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)

Beginner
820 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!

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

Beginner
820 Views

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

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

Beginner
820 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

integer, intent(in) :: r1

integer, intent(out) :: num

!DEC\$ ATTRIBUTES REFERENCE :: num

num=r1**2

return

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
qtest = x
End Function

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

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

Beginner
820 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?

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

Beginner
820 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!

Employee
820 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

Beginner
820 Views

This is very helpful. thanks as always.

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

Black Belt Retired Employee
820 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?

Beginner
820 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

Beginner
820 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?

Black Belt Retired Employee
820 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.

Beginner
574 Views

Here it is

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