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.

[Steve_Lionel]

VBA/VB will complain about the DLL you're referencing directly when what it really can't find is a dependent DLL. If you built the Fortran DLL as a Debug configuration but ran Excel outside of VS, you'll get this error. You can 1) Build a Release configuration if you're not trying to debug the DLL, 2) Link against the static libraries, 3) Invoke Excel from inside VS by specifying it in the Debugging property page of the DLL project as the command to run.

[gib]

Thanks for responding, Steve.  I am building a Release config., and running Excel outside VS.  Should I try linking against a static library?  If so, is that just a matter of building the static library then specifying it in that module declaration line?

...

I tried building a static library, but the error message is unchanged.

[DavidWhite]

In my apps, I have found it best to include all the libraries as a static build, as this ensures that the correct version is always available for users.

Also, any string arguments passed from Excel should be filled to the correct length required for Fortran, e.g. using SPACE(20) or similar in the VBA code.

Be aware also, that the case of the function name in the Alias statement must be the same as in the VBA call, otherwise the function will not be found.

Depending on the version of Excel you are using, and the corresponding version of VBA, then you may or may not need the PtrSafe attribute:

I use the following ...

#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Sub MyFunc Lib "MyDLL64.dll" (Value As Double, ByVal Units As String)
    #Else
        Private Declare PtrSafe Sub MyFunc Lib "MyDLL.dll" (Value As Double, ByVal Units As String)
    #End If
#Else
    Private Declare Sub MyFunc Lib "MyDLL.dll" (Value As Double, ByVal Units As String)
#End If
 

[gib]

Thanks David.  Following your suggestion, I have:

#If VBA7 Then

    #If Win64 Then
        Private Declare PtrSafe Sub FortranCall Lib "D:\Misc\Excel\fcall_lib.lib" (r1 As Long, ByVal num As String)
    #Else
        Private Declare PtrSafe Sub FortranCall Lib "D:\Misc\Excel\fcall_lib.lib" (r1 As Long, ByVal num As String)
    #End If
#Else
    Private Declare Sub MyFunc Lib "D:\Misc\Excel\fcall_lib.lib" (r1 As Long, ByVal num As String)
#End If

then the sub is:

Private Sub CommandButton1_Click()
Dim r1 As Long
Dim num As String * 10

     r1 = 123
     Call FortranCall(r1, num)

     TextBox1.Text = "Answer is " & num
 
End Sub

The Fortran is unchanged, i.e. the Alias name is 'FortranCall'.  This has changed the error message, implying (possibly) that the .lib is now found:

Compile error:

Sub or Function not defined.

The highlighted line is:

Call FortranCall(r1, num)

Considering that this is an example supplied with the Intel Fortran compiler, one might expect it to work.

[DavidWhite]

As Steve mentioned, the error might be to some other DLL, not the one you are calling.  You might need to check using dependency walker to determine which DLL is missing.

The other one is to try is to define r1 as a single element array on the Fortran side.

[DavidWhite]

If you can find my post from a couple of years ago, I think I posted a full example (I couldn't find it earlier)

[gib]

David, since the error references the Fortran subroutine, it's hard to see that the problem might be a missing DLL.  In any case, how would I use DW to find out what Excel is missing?  I don't have a .exe.  You did see that (following Steve's suggestion) I'm now linking the static library?

[DavidWhite]

DW will tell you whether your DLL is failing to find dependent DLL.  Make sure you open the copy of the DLL that Excel is loading, not one in your VS folder.

I don't trust the Excel error messages; they can say that the function is missing simply because there was an error when the function call was made (e.g. the arguments trampled over the stack, etc).

I don't have REFERENCE in my !DEC$ declaration for the entry point, only DLLEXPORT, STDCALL and ALIAS.  I use REFERENCE for the string arguments in a separate declaration.

[gib]

Sorry, David, I don't know what this means: "Make sure you open the copy of the DLL that Excel is loading, not one in your VS folder."

What DLL is Excel loading?

[DavidWhite]

According to your code, you are using "D:\Misc\Excel\fcall.dll"

Run Dependency Walker on that DLL to check that all required DLL's are able to be loaded.  

It is possible that the DLL you build in VS is OK because of the extra folders in the PATH while in VS; when you get to Excel if not all of the required routines are linked statically, then it is possible that there is still a DLL that Excel can't find, resulting in the error when you attempt to call your function.

[gib]

David, as I wrote a couple of times, I changed to using the static library, as Steve suggested. This is from #5:

Thanks David.  Following your suggestion, I have:

#If VBA7 Then

    #If Win64 Then
        Private Declare PtrSafe Sub FortranCall Lib "D:\Misc\Excel\fcall_lib.lib" (r1 As Long, ByVal num As String)
    #Else
        Private Declare PtrSafe Sub FortranCall Lib "D:\Misc\Excel\fcall_lib.lib" (r1 As Long, ByVal num As String)
    #End If
#Else
    Private Declare Sub MyFunc Lib "D:\Misc\Excel\fcall_lib.lib" (r1 As Long, ByVal num As String)
#End If

[DavidWhite]

In all my apps I still use a DLL which is called from Excel.

However, this DLL is built using the Multithreaded option (/libs:static /threads).  This means that the required Fortran libraries get incorporated into your DLL, and so no further dependencies should be needed.

So

1. Create a DLL using the static libraries

2. Refer to the DLL in the VBA Lib statements (not to the LIB)

[gib]

OK, I see.  I have build with Multithreaded libraries (/libs:static /threads), and I get the same error - Sub or Function not defined.  DW doesn't show that fcall.dll has any missing DLLs.

[DavidWhite]

Gib,

Can you show me your source code, or at least

1. the DEC$ statement

2. the Declare Lib statement in the VBA code

3. the actual function call in VBA

While it doesn't seem likely from the discussion to date, I suspect that there is a case mismatch somewhere between the function name in the VBA and in the DLL.

You could also check the DLL entry points in the DW output to make sure that the names and case match what you're expecting.

[Steve_Lionel]

"Sub or function not defined" is not the same as "DLL not found"! So now it found all the DLLs, and you just have a name mismatch. David's requests are helpful in diagnosing the problem.

[avinashs]

Try building your Fortran DLL project in the Win32 Release configuration with the CVF calling convention (/iface:cvf) and library option set to "Debug Multithreaded". I find this to be the most reliable way to use IVF DLLs in Excel. I say "most reliable" because while the example provided by Intel works, if the same settings are used for my own DLLs, then Excel crashes in some cases.

@Steve Lionel In recent times (i.e. after IVF 17 or 18), the DLLs built under the debug configuration do not work any more in Excel or cause crashes. This was not the case prior to that. I don't know whether this is a change in IVF, Excel, MSVS or a combination thereof.

[Steve_Lionel]

There's nothing magic about the settings in the example project. I'll try the samples again and see how they work for me.

[gib]

Steve, as I showed in response #5, the error message changed when I used David's suggestion for the declaration in VBA.

David, I previously showed all the code - the only change is in the Declare code you provided.  Note that I didn't write this, it is the Intel example.  Here it all is again:

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

The VBA sub:

Private Sub CommandButton1_Click()
Dim r1 As Long
Dim num As String * 10

     r1 = 123
     num = Space(10)
     Call FortranCall(r1, num)

     TextBox1.Text = "Answer is " & num
 
End Sub

The VBA declaration code:

#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 MyFunc Lib "D:\Fortran\Excel\Fcall.dll" (r1 As Long, ByVal num As String)
#End If

[gib]

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

[gib]

Being suspicious about 'string*10' being compatible with 'character(10)', I tried making r1 the only subroutine argument.  No change to the error message.