Calling Fortran 95 dll from VBA with structure containing a dynamic array

Question

I have a type structure in Excel VBA which contains a dynamic array. I want to populate the values using a FORTRAN .dll compiled with Compaq Visual Fortran (I know it's old, but I am constrained to CVF and Excel2003).

Public Type T_STRUCT_3
    COUNT As Long
    VALUE As Double
    ARR() As Double
End Type

Public Declare Sub TestCalc3 Lib "FortranLib.dll" ( _ 
                     ByVal X As Double, ByVal n As Long, ByRef a As T_STRUCT_3)
Public Sub Initialize()
    Dim a As T_STRUCT_3, n As Long
    n = 3
    ReDim a.ARR(1 To n)
    Call TestCalc3(X, n, a)
End Sub

What I have tried in Fortran (and failed) is using the following:

module CALCTEST
    IMPLICIT NONE

    INTEGER, PARAMETER :: C_INT = selected_int_kind(9)
    INTEGER, PARAMETER :: C_REAL = selected_real_kind(6, 37)
    INTEGER, PARAMETER :: C_DOUBLE = selected_real_kind(15, 307)

    INTEGER, PARAMETER :: MAX_SIZE = 10
!-----------------------------------------------------------------------    
    type T_STRUCT_3
    SEQUENCE
        INTEGER(C_INT) :: COUNT
        REAL(C_DOUBLE) :: VALUE
        REAL(C_DOUBLE), POINTER :: ARR(:)
    end type T_STRUCT_3

contains
!--------------------------------------------------------------------
    subroutine TestCalc3(X,N,A) 
    !DEC$ATTRIBUTES ALIAS:'TestCalc3' :: TestCalc3
    !DEC$ATTRIBUTES DLLEXPORT :: TestCalc3
    !DEC$ATTRIBUTES VALUE :: N, X

    INTEGER(C_INT), INTENT(IN)      :: N
    TYPE(T_STRUCT_3), INTENT(OUT)   :: A
    REAL(C_DOUBLE), INTENT(IN)      :: X

        A%COUNT = N                 ! Value N is fine and I can assign it to A%COUNT
        A%VALUE = X                 ! Value X is fine and I can assign it to A%VALUE
        A%ARR =(/ (X*I, I=1,N) /)   ! <== how do I point A%ARR to the dynamic array?
                                    ! Here is where the error occurs

    RETURN
    end subroutine

end module

My compilation settings are default

Settings

PS. With fixed length arrays I have no problems. I have populate the values and return them back to VBA nicely.

PS2. I am not using ISO_C_BINDINGS (not available in CVF)

I found out there is an endianess issue. For example the double 0.123456789012346 is passed as 0x3746F6593FBF9ADD instead of 0x3FBF9ADD3746F659 (See binaryconvert.com/…) — John Alexiou
– John Alexiou, Commented Oct 29, 2014 at 18:33

IanH · Accepted Answer · 2014-09-11 21:19:37Z

1

The issue will be that the descriptor used for the Fortran pointer for the array inside the structure in CVF will not be the same as what's used for the equivalent component in VBA.

You will need to "roll-your-own" pointers with so called Cray or integer pointers and safe arrays (which I think is what VBA uses for the component on its side - i.e. the third component on the Fortran side should be an INTEGER that holds a handle to a VBA safe array). I'm not sure what out-of-the-box support CVF provided for the latter.

Questions like this are best asked on the Intel forums as the successor to CVF. It is likely that examples of how to do this already exist on that forum.

answered Sep 11, 2014 at 21:19

IanH

21.6k2 gold badges41 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Alexiou Over a year ago

Ouch .. "roll out my own pointers". Not going to happen. I will look into passing safearrays back and forth into fortran. In C wouldn't the structure just hold a 32-bit pointer to the data (dumb arrays) so there must be a way to get VBA to interop the same way.

IanH Over a year ago

By "roll your own" I mean you need to interpret the safearray within Fortran, rather than relying on the native Fortran array descriptor. The way you would work with the safearray in C and Fortran are essentially the same, bar syntactic differences in the languages - (if my recollection is correct...) the structure holds a handle to a safearray and you need to use the safearray API's to extract the 32 bit pointer to the array data. I have examples of how to work with safearrays in ifort (CVF may have come with some too), but I doubt these examples would work with CVF.

DrOli · Accepted Answer · 2015-08-08 22:09:24Z

If all you really wish to do is as per your example, then why bother with the Structure on the Fortran-side? The example shown is in fact a "fixed length array" problem, since you are passing "n" to the Fortran s/r explicitly, so Fortan sees it as fixed length (c.f. allocatable etc).

That is, just call a Fortran s/r asking to return a "normal" array, and then assign the array to the VBA Struct after return, or directly via the Arg (e.g. depending if you use strategy 1) or 2) below).

When passing arrays between VBA and Fortran, you have some choices, for example:

1) Pass "normal" arrays, such as:

Apparently, all of the information required on the Fortran side in your example is provided explicitly in the arg list, so why not just

Subroutine ForSub(X,n,Arr)
!
Real(XX), Intent(In)    :: X
Integer, Intent(In)     :: n
Real(XX), Intent(Out)   ;; Arr(n)
!
Arr(1:n) = ....
!
End Subroutine ForSub

The VBA side will require the appropriate declartion of the Sub, and then also a VBA sub which will have a "direct array" syntax, something like

Dim Arr(1 to n)
Call ForSub(X, n, Arr(1))

... etc

Also on the VBA side, with this approach, you will need to then assign the Fortran created Array explicitly to each element of the VBA Struct.

... this is a kind of brute force approach, and though not too efficient on execution, it is easy and reliable (though a bit more of pain on the VBA side for more complex settings).

2) Pass (just the array) as Variant/SafeArray

Alternatively, you can, either as Variant/Array, or a Structure, pass the values. However, as IanH suggests, this requires taking the Args as pointers to the Variant or SafeArray (can use either, though the details vary a little ... I use both Variants and Cray pointer approaches to pass arrays, depending on the specific issues at hand). The full explanation is a rather tedious, but the Fortran side might (depending on the exact approach taken) look like (for simplicity, assuming passing arrays, rather than structs), in this example the Variant is passed in :

!
!************************************************************************
!   Basic stats: returns a vector of stats associated with a series X
!************************************************************************
!
!DEC$ ATTRIBUTES DLLEXPORT :: BASICSTATS1D_XL
!
    Subroutine BASICSTATS1D_XL(X_VARIANTARRAY, N, RESULTS, IERR, &
                                ISTATOPTIONX, &
                                lSTATOPTIONCUMX, &
                                UPPERQUANTILEX, &
                                LOWERQUANTILEX, &
                                LALLOWNONNUMERICX_K2
!
!
    ! This s/r is provided so that long length data sets can be processed, as opposed to XL 29 limit
    ! ==> BUT ALSO, to permit stats for seriest that includ #NA's etc, i.e. return stats for
    !     just the legitimate numeric values in the data set ... if so desired.
    !
    Use ARTType
    Use ARTStats, Only: BasicStats
    !
    Use ARTF90ExcelMixedLangMod, Only: ARTExcelVarArray1D
    !
    ! Necessary to define interface to SafeArrayxxx calls
    !
    Use IFCom !Use dfcom in your case
    !
    Implicit None
    !
    !
    Type(Variant), Intent(In)                   :: x_VariantArray  ! this is an "in" example, but same declaration for "out" also
!
:
:
    !
    Call ARTExcelVarArray1D(VarArray_Ptr = x_VariantArray%VU%Ptr_Val, n = n, FArray = x(:), iErr = iErr, &
                            nAllowNonNumericX = nZZ)
    !

Here the arg x_VariantArray is declared as a CVF/IVF Variant, and the VBA Variant can be accessed directly. Alternatively, one can send just the pointer (i.e. as a Cray Pointer/Integer) and also access the data that way, but the details are a little different.

Notice that the (custom) s/r ARTExcelVarArray1D() is used to convert the array inside the Variant to a Fortran array. A symmetric s/r converts Fortran arrays to Variants that are Intent(Out), etc. An extremely abridged version (I've removed much of the pre- and post-processing and the substantial amount of "VT testing/coercion" etc) of such an s/r is (this one is for Integer(4)'s):

    Subroutine ARTExcelVarArray1D_Int(VarArray_Ptr, n, FArray, iErr, iCoerceX, iFailedCoerceX, iFailedCoerceValX)
    !
    Use IFCom, Only: SafeArrayGetDim, SafeArrayGetLBound, SafeArrayGetUBound, SafeArrayAccessData, SafeArrayUnAccessData
    Use IFWinTY, Only: Variant              ! Variant appears to be in IFCom also, so could STREAMLINE
    !
    Integer, Intent(In)             :: n
    !
    ! Declare array_ptr to be a pointer to an integer.  When Cray pointers
    !  are declared, they must point to something; this DUMMY integer is
    !  simply a placeholder so we can declare the pointer.
    !
    !
    !
    Integer                         :: dummy
    !
    Pointer (VarArray_Ptr, dummy)   ! Can't have Intent() with Ptr's , but this is an (In)
    !
    Integer, Intent(In), Optional   :: iCoerceX, iFailedCoerceX, iFailedCoerceValX
    !
    Integer, Intent(Out)            :: iErr
    !
    Integer, Intent(InOut)          :: FArray(n)
    !
    !
    !
    ! Locals
    ! ------
    !
    ! Declare another pointer, which will be used as the head of the
    !  array of EmployeeInfo's.
    !
    Pointer (Data_ptr, ArrayData)   ! Can't have Intent() with Ptr's, but this Local
    !
    Type(Variant)                   :: ArrayData(n)
    !
    Integer                         :: i, j, k   ! Loop variables
    Integer                         :: iStart, iEnd, nDim
    !
    Integer                         :: Status
    !
    !
    iErr = 0
    !
    FArray(:) = 0
    !
    !
    ! Get SafeArray Shape/Extent etc
    ! ------------------------------
    !
    !
    nDim = SafeArrayGetDim(VarArray_Ptr)
    !
    !
    !
    Status = SafeArrayGetLBound(VarArray_Ptr, 1, iStart)
    Status = SafeArrayGetUBound(VarArray_Ptr, 1, iEnd)
    !
    !
    ! Use the SafeArray routine to get the address of the array of Data
    ! -----------------------------------------------------------------
    !
    Status = SafeArrayAccessData( VarArray_Ptr, Data_ptr )
    !
    If( Status /= 0 ) Then
        !
        !
        iErr = -Status
        Go To 99999
    End If
    !
    !   Return value            Meaning
    !   -------------------------------
    !   S_OK                    Success.
    !   E_INVALIDARG            The argument psa was not a valid safe array descriptor.
    !   E_UNEXPECTED
    !
    ! ... skip lots of things, for this simple illustration
    !
    !
    Call ARTExcelVarArray1D_Int_XX(ArrayData, n, FArray, iErr, iCoerceX, iFailedCoerceX, iFailedCoerceValX)
    !
    !
    !
99999   Continue
    !
    !
    ! When you are done, you must 'deaccess' the data.
    ! ------------------------------------------------
    !
    Status = SafeArrayUnaccessData(VarArray_Ptr)
    !
    If( Status /= 0 ) Then
        !
        ! perform your error handling
        !
    End If
    !
    !
    End Subroutine ARTExcelVarArray1D_Int




Pure Subroutine ARTExcelVarArray1D_Int_XX(ArrayData, n, FArray, iErr, iCoerceX, iFailedCoerceX, iFailedCoerceValX)
!
Use IFWinTY, Only: Variant              ! Variant appears to be in IFCom also, so could STREAMLINE
    !
    !
    Integer, Intent(In)             :: n
    !
    !
    !
    Type(Variant), Intent(InOut)        :: ArrayData(:)
    !
    Integer, Intent(In), Optional   :: iCoerceX, iFailedCoerceX, iFailedCoerceValX
    !
    Integer, Intent(Out)            :: iErr
    !
    Integer, Intent(InOut)          :: FArray(n)
    !
    !
    !
    ! Locals
    !
    Integer                         :: i, iFailedCoerceVal
    !
    !
    !
    !   VT_Type Verification etc
    !   ------------------------
    !
        ! Then some elements of the Array are not required type (e.g. Int, Real etc)
        !
        ! Note: requires option for coercion to other Types
        !       - e.g. if Real(SP) (ie. VT_Type = 2)  then maybe OK etc
        !       - if Int, (i.e. VT_Type = 2 or 3), then what ??
        !... skip lot's of things for this simple illustration
        !
        ! First, apply "Error" handling
        !
        !... skip lot's of things for this simple illustration
    ! Now, fill in the fields
    ! -----------------------
    !... skip lot's of things for this simple illustration
    !
    ForAll(i=1:n)
            FArray(i) = ArrayData(i)%VU%Long_Val
    End ForAll
    !
    !
    !
    End Subroutine ARTExcelVarArray1D_Int_XX

In this particular example, the data is passed in as a straight 1-D array (via the Variant). The Variant Type includes various bits of information indicating what it actually contains, and querying those elements is required to determine the specifics of the conversion between Variant's etc and Fortran vars.

I wrote my own Variant/SafeArray/BString etc "converters" (it's quite a bit of work and there are many details), but there are examples in the CVF/IVF package, and on on-line. In general, you will need a collection of such routines to deal with Int/Real/BString 1-D/n-D, etc etc, and lot's of "overloading".

The BString/VBString case is even more tedious, especially if your array is an array of B/VBStrings.

Once you have created explicit (Cray) pointer based vars, and/or explicit Variant vars, stepping through the code with Watch is extremely useful to see how those Types are structured and the information they contain

You may also wish to look at/get a hold of Canaima's F90VB package, which does all this (and much more) for you (though there are slight differences in the internal structure between CVF/IVF Variants and Canaima's F90VB Variants etc).

The syntax on the VBA side is also a little different, depending on whether your VBA vars are declared as Variant, Range, etc

As a matter of interest, passing arrays by this approach executes VERY MUCH faster (primarily since there no need to perform VBA side element-by-element array assignment, and the many other "weird" things that are required on the VBA-side (VBA/COM is not particularly array-friendly), especially with multi dim arr's, Ranges, etc).

The down side is that the coding is much more involved on the Fortran side, and requires a fair amount of pre- and post-process checking for robust implementation.

3) For Typed/Structured Vars

To do this with Structures is just an extension, but with "much more gore". There is an example of one particular way to pass Structure in the CVF package in the dir ... MsDev\DF98\SAMPLES\MIXLANG\VB\TYPEARRAYS, and much discussion on line.

You can do all of this without any ISO binding, if you wish.

Collectives™ on Stack Overflow

Calling Fortran 95 dll from VBA with structure containing a dynamic array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related