Writing a DLL in Fortran/calling from Excel

Question

I'm trying to write some numeric functionality in Fortran that can be later accessed in Excel. I'm using Office 2013 (64-bit), Visual Studio 2015 "Community" and Intel Fortran (Intel Parallel Studio XE 2017); and I'm able to correctly compile executable programs. I'm also able to generate DLL files, which I presume to be correct.

Here's a minimal Fortran code example I've worked out from this tip:

function id(x)
    !DEC$ ATTRIBUTES DLLEXPORT :: id
    integer :: id, x
    id = x
    return
end function

and I have this on the Excel side under the VBA code for Sheet1:

Option Explicit
Option Base 1
Public Declare PtrSafe _
    Function id Lib "mydll.dll" _
    (ByVal x As Integer) As Integer

By doing this, I manage to have the formula bar in Excel to autocomplete the function name, but evaluating any instance causes the cell to briefly flash 0 and then turn #VALUE!.

I've tried a number of things in combinatorial/cargo cult fashion: use ByRef instead of ByVal, have functions use only integers or single-precision (real*4) or double-precision (real*8) floats and then things that make even less sense.

I have finally ran out of random key combinations to mash, and there are very few sources covering this exact use case. If I must boil this down to something more precise than "what to do next":

• Am I having problems because of numeric types or something related to an "impedance mismatch" between the Fortran and Excel worlds?
• Am I instead missing necessary boilerplate, glue code and such?

Answer 1

It seems you are unaware that Intel Fortran provides several worked examples of interfacing Excel/VBA and Fortran. Download the Intel Parallel Studio XE for Windows Sample Bundle from https://software.intel.com/en-us/product-code-samples. In the ZIP file, compiler_f\MixedLanguage\Excel is probably the one most relevant to you. An important aspect is that any calls from VBA need to specify the STDCALL calling mechanism on the called routine side, or else you'll get stack corruption.

Answer 2

Here is the snippet from Intel visual Fortran help files.

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 on the excel side they used:

Declare Sub FortranCall Lib "C:\Temp\Fcall.dll" (r1 As Long, ByVal num As String)

It will be good to get more examples with different inputs and outputs to help users.