- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Link Copied
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think the fortran compiler is version 11.1. Does that make more sense?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is very helpful. thanks as always.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page