Call DLL from Excel

[gib]

I know this has been addressed before, but the previous info doesn't seem to help.

I am trying to run the test code that comes with Intel Fortran, the xltest.xls + Fcall.f90 example.

Here is the Fortran code:

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

and here is the VBA module code:

Declare PtrSafe Sub FortranCall Lib "D:\Misc\Excel\fcall.dll" (r1 As Long, ByVal num As String)

(I added PtrSafe, but it doesn't make any difference).

When I build the DLL with IVF 2013 and click the button to try to run this in Excel I get:

Run-time error '48':

File not found: D:\Misc\Excel\fcall.dll

although of the course the DLL is there.

[gib]

Note that I had neglected to change one of the Declare lines that David gave me.

Private Declare Sub MyFunc Lib "D:\Fortran\Excel\Fcall.dll" (r1 As Long, ByVal num As String)

should be

Private Declare Sub FortranCall Lib "D:\Fortran\Excel\Fcall.dll" (r1 As Long, ByVal num As String)

but this fix didn't change the error message.  Presumably my Excel uses VBA7.  By the way I'm testing on two machines, one with IVF 2013 on W10, the other with IVF 11.075 on W7.  They both give the same error.

[DavidWhite]

Gib,

Two differences, I think, that I can see

1. You still have REFERENCE in the DEC$ statement

2. If you are using VBA7, as you claim, then you need the PtrSafe keyword in the Lib declaration

[avinashs]

gib wrote:

avinashs, implementing your suggestion didn't change the error message.

@gib I recreated the example with Intel 19.0.2.190 [IA-32] and Office 2016 with latest updates. I was able to run it successfully with no errors in both debug and release versions. Just thought I would share for your reference since I have been recently having problems with calling dlls in Excel as well with the only change being that I am using the latest compiler.

[gib]

As I mentioned a couple of times, David, I'm using the Declare code that you suggested:

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Sub FortranCall Lib "D:\Fortran\Excel\Fcall.dll" (r1 As Long, ByVal num As String)
    #Else
        Private Declare PtrSafe Sub FortranCall Lib "D:\Fortran\Excel\Fcall.dll" (r1 As Long, ByVal num As String)
    #End If
#Else
    Private Declare Sub FortranCall Lib "D:\Fortran\Excel\Fcall.dll" (r1 As Long, ByVal num As String)
#End If

(I previously had left 'MyFunc' in the last declare, but fixing that had no effect, which makes me think I must be using VBA7)

The behaviour is the same with or without REFERENCE.

[DavidWhite]

Are you using a 64-bit version of Excel?  Your declares are using the same DLL for Win64, where in my original example, the Win64 section needed a 64-bit version of the DLL.  64-bit Excel cannot load 32-bit DLL's and visa versa.

[gib]

@avinashs Thanks for that info.  I'm using two old compiler versions, 11.0.075 and 2013.  I think the Excel example was supposed to work way back then, but it's possible that changes to Excel in the meantime have broken the example.  I've tried every combination that I can think of, including using iso_C_binding, and passing the integer argument by value and by reference, but nothing helps.  Very frustrating and a big time-waster.

[gib]

@David That's a good point.  I'm not sure if my Excel is 32-bit or 64-bit.  How can I check this?

[DavidWhite]

When you goto the File section in Excel, and About Excel, it should tell you

[Steve_Lionel]

I just tried the Excel sample with 19.0.3, running Excel from the debugger. Worked fine. I needed to set the Command to the path to excel.exe, the argument to the path to the XLS file, and set a breakpoint in the subroutine.

[gib]

@David interestingly on W7 in Excel I can do File > Help and see that it is a 32-bit version, but on W10 there is no Help or About under File.  I finally tracked it down: File > Account > About Excel and learned that it is 64-bit.  I have built fcall64.dll 64-bit, but the error persists.

[gib]

@Steve is that the only way to get the example to work?  That's not very convenient, since the user needs the compiler installed.  I'd like to give it a try anyway, but I don't know how you go about setting the Command and its argument, not being familiar with the debugger.  Do you think this is possible in the 2013 compiler?  If so I'd be grateful if you'd show me how.

...

I think I've found how it is done: Project > Properties > Debugging > Command etc.  But when I set the Command and argument, then

 Debug > Start Debugging, it starts Excel, but when I invoke the macro the error message is the same as before.

[DavidWhite]

Gib, 

I think Steve was demonstrating how to use debugging with VS to sort out some of the issues.

Regarding the 64-bit Excel, then you will need to use the section of my VBA declares for Win64.

Also, you may need to check the size of the Long variable or the Integers in both VBA and Fortran under 64-bit (I'm not sure on this one - I tend to use Double Precision normally, but I do know that there are differences in Integer sizes between 32 and 64 bit apps).

[gib]

OK, I see.  But when I set a breakpoint in the function the execution does not get there - the error is still sub or function not defined.  This is on W7 with 32-bit Excel.

I have not yet tried this on W10 with 64-bit Excel.

I believe 'long' in Excel is integer(4), and 'integer' is always integer(4) in Fortran. 

Regarding bitness, I'm using your code which covers the possibilities, i.e. if Win64 it links fcall64.dll.

...

I have now tested 64-bit Excel on W10 - the same behaviour as before.

[gib]

I'm not convinced that Excel is even finding the DLL, because when I give it the name of a file that doesn't exist, like zzz.dll, the error is the same.

[gib]

After wasting a day on this, I find that it works if I make the Declare statement Public, not Private.  E.g.

Public Declare PtrSafe Sub FortranCall Lib "D:\Fortran\Excel\release\fcall.dll" (r1 As Long)

I don't understand why David specified Private, and I assume that Steve left the code unchanged (neither Public nor Private) since that works too.

[Steve_Lionel]

Right - I did not touch the code, and I have the latest Excel as well. I first ran the example exactly as described in the ReadMe. I then went to try debugging, as I described, and it worked.

If you are developing a DLL to be used on another system, always distribute a Release configuration, and you can choose to link to the static libraries if the caller will never be Fortran.

[avinashs]

In the sample code, why is the !DEC$ ATTRIBUTES REFERENCE :: num needed when !DEC$ ATTRIBUTES DLLEXPORT, STDCALL, REFERENCE, ALIAS:"FortranCall" :: FortranCall has been previously declared? My understanding is that the ATTRIBUTES automatically apply to all variables. I ran the example with !DEC$ ATTRIBUTES REFERENCE :: num excluded and it does not work.

[Steve_Lionel]

The REFERENCE on num (which is a character variable) prevents the compiler from expecting a passed length. REFERENCE on the routine is not sufficient for that - it just blocks the implied VALUE for STDCALL.