Passing an array of strings from Excel to a PowerBASIC DLL

[Michael Fitzpatrick]

Greetings! The code just below is in an Excel Module. The intent here is to pass an array of strings from Excel to a PowerBASIC DLL. (I am using Excel 2007 and PowerBASIC 9.01.)

 
Declare Function ProcStrArry Lib "C:\PBWin90\MySamples\MySample1.dll" (ByRef x$) As String
Sub TestPassStrArrs()
Dim MyVarArr(0 To 3) As String
MyVarArr(0) = "xc"
MyVarArr(1) = "SR"
MyVarArr(2) = "DIR"
MyVarArr(3) = "COO"
Dim AnswStr As String
AnswStr = ProcStrArry(MyVarArr(0))
MsgBox "AnswStr = " & AnswStr
End Sub

The PowerBasic code which was compiled with a #COMPILE DLL is just below:

 
FUNCTION ProcStrArry ALIAS "ProcStrArry" (BYREF x AS STRING) EXPORT AS STRING
DIM xPtr AS STRING POINTER
xPtr = VARPTR(x$)
DIM Result0 AS STRING
DIM Result1 AS STRING
DIM Result2 AS STRING
DIM Result3 AS STRING
Result0 = @xPtr[0]
MSGBOX "Current value of Result0 is: " & Result0
Result1 = @xPtr[1]
MSGBOX "Current value of Result1 is: " & Result1
Result2 = @xPtr[2]
MSGBOX "Current value of Result2 is: " & Result2
Result3 = @xPtr[3]
MSGBOX "Current value of Result3 is: " & Result3
FUNCTION = "DONE"
END FUNCTION

When I execute the Excel VBA code, the following messages are displayed by the PowerBASIC DLL:
Current value of Result0 is: xc
Current value of Result1 is
Current value of Result2 is
and then Excel hangs with the message: "Microsoft Office Excel has encountered a problem and needs to close ..." (Excel hangs, and consequently the fourth message is not displayed.) I must be close to the correct code since the first message displayed is correct. The second message should have been "Current value of Result1 is SR", and the third message should have been: "Current value of Result2 is DIR",
I tried the third line of the DLL (xPtr = VARPTR(x$)) both with and without $ after x in the VARPTR call with the same result. I also tried using
STRPTR instead of VARPTR but that causes Excel to bomb as soon as instruction "Result0 = @xPtr[0]" of the DLL is executed. Any help will be greatly appreciated.

For comparison purposes, analogous (and very similar) code succeeded in correctly transferring an array of Double Precision numbers from Excel to a PowerBASIC DLL.
The Excel code for this is:

Declare Function ProcArry Lib "C:\PBWin90\MySamples\MySample1.dll" (ByRef x#) As Double
Sub TestPassArrs()
Dim MyVarArr(0 To 3) As Double
MyVarArr(0) = 123.456
MyVarArr(1) = 789.111
MyVarArr(2) = 246.864
MyVarArr(3) = 111.222
Dim AnswDbl As Double
AnswDbl = ProcArry(MyVarArr(0))
MsgBox "AnswDbl = " & AnswDbl
End Sub

and the PowerBASIC DLL code is:

FUNCTION ProcArry ALIAS "ProcArry" (BYREF x AS DOUBLE) EXPORT AS DOUBLE
DIM xPtr AS DOUBLE POINTER
xPtr = VARPTR(x#)
DIM Result0 AS STRING
DIM Result1 AS STRING
DIM Result2 AS STRING
DIM Result3 AS STRING
Result0 = STR$(@xPtr[0])
MSGBOX "Current value of Result0 is: " & Result0
Result1 = STR$(@xPtr[1])
MSGBOX "Current value of Result1 is: " & Result1
Result2 = STR$(@xPtr[2])
MSGBOX "Current value of Result2 is: " & Result2
Result3 = STR$(@xPtr[3])
MSGBOX "Current value of Result3 is: " & Result3
FUNCTION = @xPtr[0] + @xPtr[1] + @xPtr[2] + @xPtr[3]
END FUNCTION

Upon execution of the Excel code, all correct results were obtained.

[Edwin Knoppert]

Should be varptr() , iow just pass it as designed.

Since it is byref.. i assume the string is in unicode.
VB habbit is convert to ansi when byval (useless in this case).

I don't think you can change it with PB strings (not byref that is)

[Blair Heinzlmeir]

Michael,

Did you ever get this working? That is passing strings to and from Excel.

Blair,

[Michael Fitzpatrick]

Passing string arrays from Excel to a PowerBASIC created DLL

Hi Blair:

Greetings! After a further bit of study, I came to the conclustion that with the current version of PowerBasic, an array of strings cannot be efficiently passed from Excel to a PowerBasic DLL. Please read Bob Zale's post just below which I believe provides the reason (OLE strings are not allocated contiguously by Windows. They can be virtually anywhere Windows thinks is best for you.) Just below Bob's post is my March 28th post which, I believe, provides the most efficient way of simulating the passing of an array of strings from an Excel macro to a PowerBasic DLL. Of course, I am always open to further suggestions of ways to more efficiently accomplish this.

Bob Zale's 3/28/09 post
If I were you, Michael, I'd report this terrible bug at once to SuperBasic Technical Support. However, if they aren't able to help, you might try this idea in PowerBASIC instead. {smile}

If you try that approach, you'll need to correct your code a bit. You are passing a parameter as a pointer to the target data of a dynamic (OLE) string. OLE strings are not allocated contiguously by Windows. They can be virtually anywhere Windows thinks is best for you.

Rather, you need to pass the VARPTR, get each handle, and follow it successively to each target data item. There are other methodologies, as well.

PowerBASIC strings function just fine.

Best regards,

Bob Zale
PowerBASIC Inc.
My March 28th post:
　
Passing string arrays from Excel to a PowerBASIC created DLL
Greetings! I thank everyone of you for your responses. My main purpose in creating the DLL was to take some slow-running interpreted code from an Excel VBA module and place it in a DLL which could run that code much faster, the code in a DLL being compiled rather than interpreted. Therefore my main objectives are speed, speed, and speed.
Now here is my bright idea for essentially transferring string information from Excel to a DLL which will process it. Since I have only 4 different types of strings: "xc", "COO", "SR" and "DIR", I will let these strings be represented by integers as follows:

Code:

String Use this Integer Representation
"xc" 4
"COO" 3
"SR" 2
"DIR" 1
For example, the string array whose elements are:
OfcArry(0) = "xc"
OfcArry(1) = "DIR"
OfcArry(2) = "SR"
. . .
OfcArry(49) = "COO"

can instead be represented by an array of integers whose elements are:
IOfcArry(0) = 4
IOfcArry(1) = 1
IOfcArry(2) = 2
. . .
IOfcArry(49) = 3

and I believe that transferring this array of 50 integers from Excel to the PowerBASIC DLL is probably much faster than any of the schemes suggested or hinted at in this thread. Of course, if anyone has a faster and/or simpler scheme than this one, I will be happy to adopt it. May you have a blessed day.

Sincerely,

Michael Fitzpatrick

[John Petty]

Michael
Depends on what you you want to do with them. As you have such a limited range of string possabilities you could change your array definition in VBA from "Dim MyVarArr(0 To 3) As String" to "Dim MyVarArr(0 To 3) As String * 3" This will be faster in VBA as it does need to use the OLEautomation engine when you load the array. You would the change your DLL function declare in VBA to "Declare Function ProcStrArry Lib "C:\PBWin90\MySamples\MySample1.dll" (ByRef x() as string * 3)" so actually passing the address of the array descriptor.
The PB DLL would have code including the following

#INCLUDE "VBAPI32.inc"
FUNCTION ProcStrArry ALIAS "ProcStrArry" (BYREF x DWORD) EXPORT AS STRING ' x is now the address of the array descriptor
    local MyArrayAddress as dword
    MyArrayAddress = vbArrayFirstElem(x)
    REDIM MyStringArray(vbArrayUBound(x, 1) as STRING * 6 at MyArrayAddress
    'Documentation is sketchy so dimemsion might be 0
    'STRING * 6 as should be UNICODE so must build own strings for comparisons if that is what you want
    'so xc would be CHR$(00) & "x" & CHR$(00) & "c" & CHR$(00) & CHR$(00)
    'which would only be done once at the start of the call or as GLOBALS on the DLL load

If you must use a variable length string array there is a similar method but not as fast.
John