Call Fortran DLL from Excel VBA with derived type argument

[gib]

Is this possible?  I'd like to be able to use an array of derived types as a sub argument.  All the members are real scalars.

Apologies, please ignore this - I just did a quick test and found that the answer is yes.  A VBA Type works seamlessly with a Fortran derived type in this simple case of all scalar members.

[FortranFan]

Yes.  You can have a user-defined type (UDT) in VBA: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/type-statement

Type MyUdt
    Component1 As Single ' Or Double. 
    Component2 As Single ' Or Double
    ..
End Type 

And declare your Sub to include the UDT as a parameter:

Public Declare Sub Fortran_Sub Lib "C:\..\..dll" (.., ByRef Foo As MyUdt(1), LenFoo As Integer, ..)

Note the above is set to pass the first element of the user-defined type (declared as 1-based index, an option in VBA)..  It;s then accompanied by an explicit parameter passed by value which is the number of elements in the array of this type.

Then, on the Fortran side, I suggest you use standard Fortran facilities toward interoperability with C; the dummy argument toward the array of the derived type is declared as assumed size.  But note if you need to support 32-bit version of Microsoft Office, you must attribute the procedure as STDCALL:

   type, bind(C) :: MyUdt
      real(kind=c_float) :: Component1 ! Or kind=c_double
      real(kind=c_float) :: Component1 ! Or kind=c_double
      ..
   end type MyUdt

   subroutine Fortran_Sub( .., Foo, LenFoo, ..) bind(C, name="Fortran_Sub")
   !DIR$ ATTRIBUTES STDCALL
      ..
      type(MyUdt), intent(inout)        :: Foo(*)
      integer(c_int), intent(in), value :: LenFoo
      ..
   end subroutine 

[JAlexiou]

The trick here is that you cannot submit an array of `Type` to Fortran. You submit the first item in the array with `ByRef`. Below is working example:

FORTRAN

module vector_algebra
    TYPE vec3
        REAL(C_DOUBLE) :: X = 0.
        REAL(C_DOUBLE) :: Y = 0.
        REAL(C_DOUBLE) :: Z = 0.
    END TYPE
contains
    function MinDistance(points, count) result(d)
        !DEC$ATTRIBUTES DLLEXPORT, ALIAS:'MinDistance' :: MinDistance
        !DEC$ATTRIBUTES VALUE :: count
        integer, intent(in) :: count
        type(vec3), intent(in) :: points(count)        
        ! The code goes heere
        
    end function

end module

VBA

Public Type Vec3T
    X As Double
    Y As Double
    Z As Double
End Type

Private Declare Function MinDistance Lib "FortranLib.dll" _
    (ByRef points As Vec3T, ByVal count As Long) As Double

Private Sub calcLabel_Click()
    n = ...
    
    Dim i As Long
    Dim pt() As Vec3T
    ReDim pt(1 To n)
    For i = 1 To n
        pt(i).X = ...
        pt(i).Y = ...
        pt(i).Z = ...
    Next i
    
    Dim d As Double
    d = MinDistance(pt(1), n)
    
End Sub

As you can see the Fortran code takes an argument with the size of the array and declares the array of user type based on that value. make sure you declare the size argument as `VALUE` and in VBA with `ByVal`. For passing the array from VBA use the first element `pt(1)` and the count `n`.

[gib]

Perhaps I should have made it clear that I coded an example and found that it worked.  That's why I edited my original comment immediately, and asked that it be ignored.  Instead I got two responses telling me what I already knew.

[FortranFan]

gib wrote:

Perhaps I should have made it clear that I coded an example and found that it worked.  That's why I edited my original comment immediately, and asked that it be ignored.  Instead I got two responses telling me what I already knew.

I posted what I did in Quote #2 starting with a "Yes" as a clear confirmation it's trivially simple now to achieve what was asked in the first paragraph of the original post.  However the original post failed to include any helpful details on how the question got answered which wasn't good netiquette toward any other reader who would be interested in the topic.

Quote #2 was thus an attempt to help those other readers but with the point Excel VBA, a Microsoft product, really interoperates with Microsoft C/C++ compiler and the suggestion for Fortran coders is to follow standard facilities as much as possible to interface with this processor.