Character(LEN=*) Sample DLL in Excel in modern IVF

Peter_A_1 · ‎04-24-2013

Hi Steve and others

In http://software.intel.com/en-us/forums/topic/275070 we discussed:

In IVF 11 (an 10) I am trying unsuccefully to pass strings to and from excel (i.e.VBA=VB6) using in an stdcall DLL much as I in CVF using len=*.

My conclusion:

To avoid BSTR we discussed two options but I am stuck on both

1) Character(len=*): not working

2) Character(len=X): I have had no luck

Can you please demonstrate one working one? The attached zip is much as the one I sent many seeks ago on the other forum and allows one to test with either Buttons or cell formula.

Peter

Steven_L_Intel1 · ‎04-24-2013

I am not going to be able to look at this for a few weeks. The only way to get character(*) working is to send the length by value at the end of the argument list.

IanH · ‎04-24-2013

I prefer to pass the length explicitly, because that may avoid future changes in the binary interrface for passing strings (32 bit versus 64 bit, compiler options, etc).

(That's a pretend justification - really I prefer it because I can never remember what the binary interface (after the argument, after all arguments, etc) is, so I stick with what I know.)

So, having a look at that case specifically - your DLL_ROUT20 case has got a reference (VBA code) versus value (Fortran code) mismatch for the string length arguments. If you fix that (value probably makes more sense) I think you'd be ok.

Note the length specification expression for the character dummy arguments uses variables that then have their type specified after the expression, in a scope that doesn't have implicit typing. That's an extension to the language. Obviously the code uses other extensions (all that !DEC$ business) so maybe this point is a little silly, but I don't see much point in making the code more reliant on extensions than it needs to be, plus personally I find the "correct" ordering makes the code easier to follow.

Some of the comments in the Fortran for the other cases don't make sense or are inconsistent (perhaps they are out of date).

For what its worth, a simple example. Note that you can put the procedure into a module - which can be handy if you want to do some testing from Fortran code.

[fortran]MODULE PassAString
IMPLICIT NONE
INTEGER, PARAMETER :: vba_long = 4
CONTAINS
! Processes a string sent across from VBA.
!
! Use the following VBA declaration:
!   Private Declare Sub FixedLength Lib "PassAString" _
!       (ByVal str As String, ByVal str_len As Long)
SUBROUTINE FixedLength(str, str_len)
    !DEC$ ATTRIBUTES DLLEXPORT, STDCALL :: FixedLength
    !DEC$ ATTRIBUTES ALIAS:'FixedLength' :: FixedLength
    ! Procedure is STDCALL so this is passed by value by default.
    INTEGER(vba_long), INTENT(IN) :: str_len
    ! We want the address of the string, and don't want the hidden length
    ! to be passed (because we are passing it explicitly). REFERENCE
    ! for the argument does both.
    CHARACTER(str_len), INTENT(INOUT) :: str
    !DEC$ ATTRIBUTES REFERENCE :: str
    INTEGER :: i
    CHARACTER(str_len) :: tmp
    !****
    tmp = ''
    DO i = 1, LEN_TRIM(str)
      tmp(LEN_TRIM(str)+1-i:LEN_TRIM(str)+1-i) = str(i:i)
    END DO
    str = tmp
END SUBROUTINE FixedLength
END MODULE PassAString
[/fortran]

And the VBA bit...

[plain]Option Explicit

' Declare our Fortran procedure.
Private Declare Sub FixedLength Lib "PassAString" _
    (ByVal str As String, ByVal str_len As Long)

'*******************************************************************************
'
' Call our Fortran procedure.
'
' str [in]          The string to process.
'
' Returns whatever gets sent back from Fortran.
'
' str is byval because we modify it internally.

Public Function WhateverYouWantToCallTheFunction(ByVal str As String) As String
Call FixedLength(str, Len(str))
  WhateverYouWantToCallTheFunction = str
End Function
[/plain]

While we are here, this little bit of VBA popped into the code sheet for the workbook can sometimes make working with Excel and Fortran DLL's a bit easier.

[plain]Private Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" _
    (ByVal lpFilename As String) As Long

Private Declare Function FreeLibrary Lib "kernel32" _
    (ByVal handle As Long) As Long

' Handle to the our DLL - loaded when we're loaded, unloaded when
' we are closed. We do this to allow some choice of which DLL gets loaded
' (can point to a debug version) - subsequent calls to
' LoadLibrary(dll_name) (which might be carried out behind our backs by
' VBA!) will just pull in the exact same DLL we load in our initialisation
' here.
Private hDll As Long

Const dll_name As String = "PassAString.dll"

'*******************************************************************************
'
' The workbook open event.
'
' Preloads the helper DLL, initially looking in the workbook's folder, the
' system's DLL search path and finally the Debug subdirectory of the workbook's
' folder.

Private Sub Workbook_Open()

Dim sPath As String         ' Workbook path.

'*****************************************************************************

' Try the directory of this workbook
sPath = ThisWorkbook.Path
If (Right(sPath, 1) <> "\") Then sPath = sPath & "\"
hDll = LoadLibrary(sPath & dll_name)
If hDll <> 0 Then Exit Sub

' Try the "normal" DLL locations - exe directory, the PATH, etc.
hDll = LoadLibrary(dll_name)
If hDll <> 0 Then Exit Sub

' Perhaps we are doing development?
hDll = LoadLibrary(sPath & "Debug\" & dll_name)
If hDll <> 0 Then
    Debug.Print "Debug variant of " & dll_name & " loaded."
Else
    MsgBox "Error: " & dll_name & " was not loaded"
    Exit Sub
End If
End Sub

'*******************************************************************************
'
' The workbook [before] close event.
'
' Calls FreeLibrary to offset the LoadLibrary and reduces the reference count
' for our helper DLL. Depending on what VBA and Excel are doing the DLL may
' then be unloaded.
'
' (Sometimes it is handy just to execute this manually so that you can drop
' the DLL reference count to zero and allow it to be unloaded - perhaps because
' you want to rebuild the DLL without shutting down Excel, restarting Excel,
' reattaching the VS debugger, waiting 30 days for all the DLL symbols to load,
' etc. In that case, comment out the hDll = 0 statement , and don't forget to
' manually run the _Open event when you are ready to start using the DLL
' again, or VBA will get very cross!)

Private Sub Workbook_BeforeClose(cancel As Boolean)
Dim api_result As Long
If hDll <> 0 Then
    api_result = FreeLibrary(hDll)
    hDll = 0
End If
End Sub
[/plain]

Paul_Curtis · ‎04-24-2013

There's no particular problem with using BSTRs. Here are some utility functions which will put text into an Excel cell:

[fortran]

!   renamed from ConvertStringToBstr(), which has a linker conflict
INTEGER(INT_PTR_KIND()) FUNCTION StringToBSTR(string)
    USE OLEAUT32
   USE IFNLS
    CHARACTER*(*), INTENT(IN)              :: string
   INTEGER(INT_PTR_KIND()) bstr
   INTEGER*4 length
   INTEGER*2, ALLOCATABLE :: unistr(:)
   ! First call to MBConvertMBToUnicode determines the length to allocate
   ALLOCATE (unistr(0))
   length = MBConvertMBToUnicode(string, unistr)
   DEALLOCATE (unistr)
   ! Special case for all spaces
   IF (length < 0) THEN
       ALLOCATE (unistr(2))
       unistr(1) = #20   ! Single space
       unistr(2) = 0       ! Null terminate
   ELSE
       ! Second call to MBConvertMBToUnicode does the conversion
       ALLOCATE (unistr(length+1))
       length = MBConvertMBToUnicode(string, unistr)
       unistr(length+1) = 0       ! Null terminate
   END IF
   bstr = SysAllocString(unistr)
   DEALLOCATE (unistr)
   StringToBSTR = bstr
END FUNCTION StringToBSTR

INTEGER FUNCTION column_range (column, row1, row2) RESULT (range)
   IMPLICIT NONE
   CHARACTER(LEN=*), INTENT(IN)       :: column
   INTEGER, INTENT(IN)                   :: row1
   INTEGER, INTENT(IN), OPTIONAL       :: row2
   CHARACTER(LEN=12)                   :: cell_id
   TYPE(VARIANT)                       :: vBSTR1, vBSTR2
   INTEGER*4                           :: pBSTR1, pBSTR2

   WRITE (cell_id, '(A,I6,A)') column, row1, CHAR(0)
   pBSTR1 = leftpack (cell_id)

   CALL VariantInit (vBSTR1)
   vBSTR1%VT = VT_BSTR
   pBSTR1 = StringToBSTR (cell_id)
   vBSTR1%VU%PTR_VAL = pBSTR1

   IF (PRESENT(row2)) THEN
       WRITE (cell_id, '(A,I6,A)') column, row1, CHAR(0)
       pBSTR2 = leftpack (cell_id)
       CALL VariantInit (vBSTR2)
       vBSTR2%VT = VT_BSTR
       pBSTR2 = StringToBSTR (cell_id)
       vBSTR2%VU%PTR_VAL = pBSTR2
       range = $Worksheet_GetRange (worksheet, vBSTR1, vBSTR2, $STATUS=status)
       status = VariantClear(vBSTR2)
       IF (pBSTR2 /= 0) CALL SysFreeString (pBSTR2)

   ELSE
       range = $Worksheet_GetRange (worksheet, vBSTR1, $STATUS=status)

   END IF
   status = VariantClear(vBSTR1)
   IF (pBSTR1 /= 0) CALL SysFreeString (pBSTR1)
END FUNCTION column_range

SUBROUTINE PutTextInCell (column, row, string, width)
   IMPLICIT NONE
   CHARACTER(LEN=*), INTENT(IN)   :: column
   INTEGER, INTENT(IN)               :: row
   CHARACTER(LEN=*), INTENT(INOUT)   :: string
   INTEGER, INTENT(IN), OPTIONAL   :: width
   INTEGER                           :: range

   range = column_range (column, row)

   IF (PRESENT(width))   &
       status = AUTOSETPROPERTYINTEGER2 (range, "ColumnWidth", INT2(width))

   CALL NullTerminateString (string)
   status = AUTOSETPROPERTYCHARACTER (range, "VALUE", string)

END SUBROUTINE PutTextInCell

[/fortran]

Peter_A_1 · ‎04-27-2013

Many thanks to IanH an PaulCurtis for the fast response.

The old CVF example required negligble extra code and allows the same stdcall dll to be used my vba and in, say, Fortran, Mathematica and I assume Matlab etc. As you will know (it is also in my VBA, Sampes link etc):

To pass a VB string, declare the argument as ByVal in the VB module. In the Fortran code,

' specify ATTRIBUTES STDCALL explicitly for the Fortran routine and ATTRIBUTES REFERENCE explicitly
' for the CHARACTER argument, which must be declared with fixed length.

I am happy to change to,adding length arguments and use whaever ByVal or ByRef that is necessary.

Paul Curtis: BSTR is, for now, far too complex. Especially as I really want one generiic DLL for VBA and other languages.

IanH:ByVal did not seem enough to fix i tDLL_RLOUT20). In VBA the string is till "substring argumants out of bounds" leading to an evential crash.

So the question is: can we find a solution as simple as in CVF which, if necessary/ desired passes the strng length. And that talks to both VBA and is still a flexible stdcall DLL.

Thanks

Peter

Paul_Curtis · ‎04-27-2013

PutTextInCell(column, row, string) exactly answers the original question, how to pass a string of indeterminate length LEN(*) to Excel.

BSTRs are only used as intermediate data structures in the supporting routines, your Fortran code does not otherwise need to deal explicitly with this data type at all.

Peter_A_1 · ‎04-27-2013

Paul: My apologies if I was not clear.

Ian:your FixedLength does now work in my Excel so .. problem solved (I assume).

Many thanks

Peter_A_1 · ‎04-30-2013

Ian?

While FixedLength works fine with Excel, I very keen to have the same DLL useable elsewhere. When calling (as a DLL) from Intel Fortran, it does seem to work but debug Local shows STR has "Substring ouf Bounds". In Watch, one can happily obsereve, say, str(1:30). I have even managed str, but most of the time ite is "Substring ouf Bounds".

Thanks, Peter

IanH · ‎04-30-2013

Lots of those files have a stray period in one of the directives after ATTRIBUTES and before ALIAS. Be mindful that the debugger can sometimes fib about this sort of stuff. I'll have a better look tomorrow, but bar that period I think things are working ok. (What's up with the !@#$%^& forum??)