Problem with SafeArrays from vba in fortran DLL

Patrick_S_5 · ‎10-11-2013

I am trying to get Excel VBA code to interface with a fortran DLL, and am having a vexing problem. I started with the 'VB.NET-SafeArrays' example, but modified slightly to work in VBA:

VBA code:[plain]Private Declare Sub ForCall Lib "<path to dll omitted>" (ByRef array1() As String)
Private myarray(2, 3) As String
Private Sub CommandButton1_Click()
    myarray(0, 0) = "One"
    myarray(0, 1) = "Two"
    myarray(0, 2) = "Three"
    myarray(0, 3) = "Four"
    myarray(1, 0) = "Five"
    myarray(1, 1) = "Six"
    myarray(1, 2) = "Seven"
    myarray(1, 3) = "Eight"
    myarray(2, 0) = "Nine"
    myarray(2, 1) = "Ten"
    myarray(2, 2) = "Eleven"
    myarray(2, 3) = "Twelve"

    Call ForCall(myarray)
End Sub[/plain]

Fortran code (pretty much identical to the example):[fortran]subroutine ForCall (VBArray)

! Set the attributes needed for compatibility with VB.NET. VB always uses STDCALL.
!
!dec$ attributes dllexport, stdcall, reference, alias : "ForCall" :: ForCall
!dec$ attributes reference :: VBArray

use ifcom ! Declare SafeArray and BSTR interfaces

implicit none

integer(int_ptr_kind()), intent(inout) :: VBArray !Pointer to a SafeArray structure

integer, parameter :: LONG_ENOUGH_BUFFER = 2048 ! Assume we won't get a string longer than this
character(LEN=LONG_ENOUGH_BUFFER) mystring ! Fortran string converted to/from BSTR
integer(int_ptr_kind()) :: BSTRptr ! Receives a pointer to a BSTR

! Array in which we will keep track of array bounds
type bounds_type
    integer lb ! Lower Bound
    integer ub ! Upper Bound
    end type bounds_type
integer nbounds ! Number of bounds
type(bounds_type), allocatable :: bounds(:)
integer, allocatable :: indexes(:) ! Array to hold current element indexes

integer :: i, iRes, length

! First, we'll get the bounds of the array. This code makes no assumptions about the number of
! dimensions.
!
nbounds = SafeArrayGetDim (VBArray)
allocate (bounds(nbounds), indexes(nbounds))

do i=1,nbounds
    ires = SafeArrayGetLbound (VBArray, i, bounds(i)%lb)
    ires = SafeArrayGetUbound (VBArray, i, bounds(i)%ub)
    end do


! Example 1 - write to a text file (since we don't have a console) the
! bounds of the array. You'll find this file in the BIN subfolder of
! the VB.NET project folder.

open (2, file="C:\testout.txt", status="unknown")

write (2, *) "Shape of the array passed by VB:"
write (2,'(" (")',advance='no')
do i=1,nbounds
    write (2,'(I0,":",I0)',advance='no') bounds(i)
    if (i < nbounds) write(2,'(",")',advance='no')
    end do
write (2,'(")")')

! Example 2 - Write the values of the string elements to the file. This code
! also makes no assumptions about the number of dimensions.
!
! For each element we:
! 1) Call SafeArrayGetElement to return a pointer to a BSTR element
! 2) Convert the BSTR to a Fortran string (which we then write to the file)
! 3) Free the string which we retrieved
!
! Note that the current interface to SafeArrayGetElement has the second "indices"
! argument defined as a scalar - we work around that by passing the first element
! by reference.
!
write (2, *) "Strings from the array:"

indexes = bounds%lb ! Initialize to all lower bounds
readloop: do
    ires = SafeArrayGetElement (VBArray, indexes(1), loc(BSTRPtr))
    length = ConvertBSTRToString (BSTRPtr, mystring)
    call SysFreeString(BSTRPtr)
    write (2, '(" A(")', advance='no')
    write (2, '(100(I0,:,","))', advance='no') (indexes(i),i=1,nbounds)
    write (2, '(") = ", A)') mystring(1:length)

    ! Determine what the next element is. We increment the last index,
    ! and if it is greater than the upper bound, reset it to the lower bound and
    ! repeat for the next lower index. If we run out of indexes, we're done.
    do i = nbounds, 1, -1
      indexes(i) = indexes(i) + 1
      if (indexes(i) <= bounds(i)%ub) exit
      indexes(i) = bounds(i)%lb
      if (i == 1) exit readloop
      end do
    end do readloop

close (2) ! We're done with the file

! Example 3 - Modifying BSTR elements in a SafeArray. Here we append
! " potato" to each of the elements.
!
indexes = bounds%lb ! Initialize to all lower bounds
writeloop: do
    ires = SafeArrayGetElement (VBArray, indexes(1), loc(BSTRPtr))
    length = ConvertBSTRToString (BSTRPtr, mystring)
    call SysFreeString (BSTRPtr)

    ! Append "potato" to the element
    mystring = trim(mystring) // " potato"

    ! Convert it back to a BSTR
    BSTRptr = ConvertStringToBSTR (trim(mystring))

    ! Write it back to the array
    ires = SafeArrayPutElement (VBArray, indexes(1), BSTRptr)
    call SysFreeString (BSTRPtr) ! Free our copy

    ! Determine what the next element is. We increment the last index,
    ! and if it is greater than the upper bound, reset it to the lower bound and
    ! repeat for the next lower index. If we run out of indexes, we're done.
    do i = nbounds, 1, -1
      indexes(i) = indexes(i) + 1
      if (indexes(i) <= bounds(i)%ub) exit
      indexes(i) = bounds(i)%lb
      if (i == 1) exit writeloop
      end do
    end do writeloop

! Deallocate our local arrays, though this would be done implicitly anyway
!
deallocate (bounds)
deallocate (indexes)

return
end subroutine ForCall[/fortran]

This code almost works. The fortran program can determine the correct dimensions of the array, but cannot retrieve the strings. Looking at the output file:

[plain]Shape of the array passed by VB:
(0:2,0:3)
Strings from the array:
A(0,0) = ?
A(0,1) = ?
A(0,2) = ??
A(0,3) = ??
A(1,0) = ??
A(1,1) = ?
A(1,2) = ??
A(1,3) = ??
A(2,0) = ??
A(2,1) = ?
A(2,2) = ???
A(2,3) = ???[/plain]

The attempt to append ' potato' to the strings also almost works. The attached file is a screenshot of what the matrix looks like after the fortran functions returns. I am not very experienced with this sort of thing, but it looks like a unicode to ascii conversion problem. If the fortran code is expecting each character in the array to be two bytes long and the characters are actually a single byte, the conversion will not work. Does this sound like a reasonable explanation? Is there some way of telling the fortran code not to do the character conversion?

Thank you for looking

IanH · ‎10-12-2013

If you are calling this from Excel, then Excel does not use UTF16 BSTR's in calls to VBA procedures - they are "ANSI" BSTR's. The conversion routine in the Intel modules is for the UTF16 variant.

Looking at my own routines, the BSTR handle is equivalent to a C-pointer to a character array, i.e. you can do something like:

[fortran]CALL C_F_POINTER(TRANSFER(bstr, C_NULL_PTR), char_array, [SysStringByteLen(bstr))[/fortran]

where char_array is a fortran pointer to a character array, and then you can copy (or pointer associate) the array elements into an actual character scalar.

Patrick_S_5 · ‎10-16-2013

Thanks for the response. I am trying to get that code to work, but I suspect I don't understand how the variables work. The DLL receives a pointer from VBA which points to a vba safestring. From there, the code

[fortran]ires = SafeArrayGetElement (VBArray, indexes(1), loc(BSTRPtr))[/fortran]

makes the pointer BSTRPtr point to the string at indexes(1) of VBArray. I then try to convert the BSTRPtr to an fortran array with

Initalization:

[fortran]character(LEN=LONG_ENOUGH_BUFFER) char_array

integer :: N1(1)[/fortran]

Called after SafeArrayGetElement:

[fortran]N1(1) = 1

CALL C_F_POINTER(TRANSFER(loc(BSTRPtr), C_NULL_PTR), char_array, N1)[/fortran]

However, the char_array variable ends up as unreadable giberish. Can you see any problems with the way I am doing this?

IanH · ‎10-16-2013

Patrick S. wrote:

N1(1) = 1 CALL C_F_POINTER(TRANSFER(loc(BSTRPtr), C_NULL_PTR), char_array, N1)

The BSTRPtr thing is the address of the string. With the LOC in there you extract the address of the address of the string - one "address of" too many. Get rid of the LOC reference.

Example cobbled together from bits and bobs of my code attached.

Patrick_S_5 · ‎10-31-2013

Sorry for the delayed response. I am not able to get you attached code to run unmodified. However, I was able to glean the important bits from the functions you provided (I think). The code to get a string from the safestring seems to be

[fortran]CHARACTER(:), ALLOCATABLE :: str

CHARACTER(C_CHAR), POINTER :: array(:)

integer(C_INTPTR_T) :: BSTRptr[/fortran]

[fortran]ires = SafeArrayGetElement (VBArray, indexes(1), loc(BSTRPtr))
    CALL C_F_POINTER( TRANSFER(BSTRPtr, C_NULL_PTR), array, [SysStringByteLen(BSTRPtr)] )
    ALLOCATE(CHARACTER(SIZE(array)) :: str)
    FORALL (i = 1:SIZE(array)) str(i:i) = array(i)[/fortran]

This successfully puts the first element of the matrix into the string 'str.' However, running the function you provided gives an error:

Function call:

[fortran]ires = SafeArrayGetElement (VBArray, indexes(1), loc(BSTRPtr))

str = ExcelBSTRToChar(BSTRPtr)[/fortran]

Function:

[fortran] !*****************************************************************************
!!
!! FUNCTION ExcelBSTRToVS
!> Returns a deferred length character string from a Excel BSTR.
!!
!! @param[in]     bstr              The integer point to the BSTR. The BSTR
!! must contain ANSI characters.
!!
!! @return A varying string with the contents of the BSTR.
!!
!*****************************************************************************

FUNCTION ExcelBSTRToChar(bstr) RESULT(str)

    USE IFCOM, ONLY: SysStringByteLen
    USE, INTRINSIC :: ISO_C_BINDING, ONLY: C_CHAR, C_PTR, C_F_POINTER, &
        C_INTPTR_T, C_NULL_PTR

    !---------------------------------------------------------------------------
    ! Arguments

    INTEGER(C_INTPTR_T), INTENT(IN) :: bstr

    ! Function result
    CHARACTER(:), ALLOCATABLE :: str

    !---------------------------------------------------------------------------

    ! Array aliased to the contents of the BSTR.
    CHARACTER(C_CHAR), POINTER :: array(:)

    INTEGER :: i              ! String index.

    !***************************************************************************

    ! Make the contents of the BSTR available as a CHARACTER array.
    CALL C_F_POINTER( TRANSFER(bstr, C_NULL_PTR), array, &
        [SysStringByteLen(bstr)] )

    ALLOCATE(CHARACTER(SIZE(array)) :: str)
    FORALL (i = 1:SIZE(array)) str(i:i) = array(i)

END FUNCTION ExcelBSTRToChar[/fortran]

The errors I get are

[fortran]error #6404: This name does not have a type, and must have an explicit type. [EXCELBSTRTOCHAR]

error #6054: A CHARACTER data type is required in this context. [EXCELBSTRTOCHAR] [/fortran]

Both of these point to the 'str = ExcelBSTRToChar(BSTRPtr)' line. I suspect this error is due to the fact that I havent used fortran in quite a few years, but do you have any suggestions on how to fix this?

IanH · ‎10-31-2013

Well, I can see one error - on line 30 of what you posted of the ExcelBSTRToChar function above (line 278 in the complete file) I've AGAIN left off the KIND= specifier before C_CHAR. I will now spend the rest of the week sleeping in the chook house in contrition. Fortunately I like eating eggs.

But that doesn't explain the error message you see. Are you comping the exact file above? If not, what specific source are you compiling? Note that the ExcelBSTRToChar function in the original example was in a module, which provides an explicit interface for the function inside that module and whereever the module is USEd ("explicit interface" means the compiler explicitly knows what the function looks like).

What compiler version are you running?