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

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

Hi Steve and others

In  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. 


Downloadapplication/zip fcall3-130424.zip895.42 KB
9 posts / 0 new
Last post
For more complete information about compiler optimizations, see our Optimization Notice.

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.

Retired 12/31/2016

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.

MODULE PassAString
  INTEGER, PARAMETER :: vba_long = 4
  ! 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 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
    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

And the VBA bit...

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

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.

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."
    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

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

!   renamed from ConvertStringToBstr(), which has a linker conflict
    USE OLEAUT32    
    CHARACTER*(*), INTENT(IN)              :: string
    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
        ! 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
INTEGER FUNCTION column_range (column, row1, row2) RESULT (range)
    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)
    pBSTR1 = StringToBSTR (cell_id)
    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)
        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)
    CHARACTER(LEN=*), INTENT(IN)    :: column
    INTEGER, INTENT(IN)                :: row
    CHARACTER(LEN=*), INTENT(INOUT)    :: string
    INTEGER                            :: range
    range = column_range (column, row)
    IF (PRESENT(width))    &
        status = AUTOSETPROPERTYINTEGER2 (range, "ColumnWidth", INT2(width))
    CALL NullTerminateString (string)
    status = AUTOSETPROPERTYCHARACTER (range, "VALUE", string)

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.




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.

Paul: My apologies if I was not clear.

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

Many thanks


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


Downloadapplication/zip fcall-130430.zip2.17 MB

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??)

Leave a Comment

Please sign in to add a comment. Not a member? Join today