- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Link Copied
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Paul: My apologies if I was not clear.
Ian:your FixedLength does now work in my Excel so .. problem solved (I assume).
Many thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page