difficulty passing Arrays to an EBasic dll

[EvangelMike]

Greetings! I am having difficulty with with either the parameter list of an EBasic Sub or the Arrays in Excel's DECLARE statement.

My Excel module VBA code is as follows:

Option Base 0
Option Explicit
Public Declare Sub TestPassVars Lib "C:\ExcelDLLs\TestPassVars.dll" (ByRef MaxInsInv As Double, ByRef SrCount As Double, ByRef K As Long, ByRef InsiderInv() As Double, ByRef InsiderJrSr() As String, ByRef LstRowData As Long)

Sub ChkPassVars()

Dim K As Long
Dim MaxInsInv As Double
Dim SrCount As Double
Dim InsiderInv(0 To 100) As Double
Dim InsiderJrSr(0 To 100) As String
Dim LstRowData As Long

K = 4
MaxInsInv = 123321#
InsiderInv(0) = 12333#
InsiderJrSr(0) = "xc"
LstRowData = 5400
SrCount = 3.5

Call TestPassVars(MaxInsInv, SrCount, K, InsiderInv(), InsiderJrSr(), LstRowData)

End Sub

MaxInsInv, SrCount, K, and LstRowData (all non-arrays) are being passed correctly to TestPassVars. The arrays,  InsiderInv() and InsiderJrSr() are not being passed correctly to the TestPassVars subroutine in the Emergence Basic DLL.

The TestPassVars subroutine is written in Emergence Basic, compiled as a DLL, and is as follows:

export TestPassVars
SUB TestPassVars(MaxInsInv:Double BYREF, SrCount:Double BYREF, K:INT BYREF, InsiderInv[]:Double BYREF, InsiderJrSr[]:String BYREF, LstRowData:Int BYREF)
HelloWindowDBL("MaxInsInv = ", MaxInsInv)
HelloWindowDBL("SrCount = ", SrCount)
HelloWindow("K = ", K)
HelloWindowDBL("InsiderInv(0) = ", InsiderInv[0])
HelloWindowDBL("InsiderInv(1) = ", InsiderInv[1])
HelloWindowSTR("InsiderJrSr(0) = ", InsiderJrSr[0])
HelloWindowSTR("InsiderJrSr(1) = ", InsiderJrSr[1])
HelloWindow("LstRowData = ", LstRowData)
'ENDSUB

export HelloWindow
export HelloWindowDBL
Export HelloWindowSTR

DEF w1 as WINDOW

SUB HelloWindow(WMsge: STRING, WAmt: INT)
REM open the window
OPENWINDOW w1,0,0,350,350,@MINBOX|@MAXBOX|@SIZE,NULL,"Simple Window",&main
REM print a message
PRINT w1, WMsge,WAmt
REM when w1 = 0 the window has been closed
WAITUNTIL w1 = 0
ENDSUB

SUB HelloWindowDBL(WMsge: STRING, WAmt: Double)
REM open the window
OPENWINDOW w1,0,0,350,350,@MINBOX|@MAXBOX|@SIZE,NULL,"Simple Window",&main
REM print a message
PRINT w1, WMsge,WAmt
REM when w1 = 0 the window has been closed
WAITUNTIL w1 = 0
ENDSUB

SUB HelloWindowSTR(WMsge: STRING, WAmt: String)
REM open the window
OPENWINDOW w1,0,0,350,350,@MINBOX|@MAXBOX|@SIZE,NULL,"Simple Window",&main
REM print a message
PRINT w1, WMsge,WAmt
REM when w1 = 0 the window has been closed
WAITUNTIL w1 = 0
ENDSUB

REM every time there is a message for our window
REM the operating system will GOSUB here
SUB main
    IF @MESSAGE = @IDCLOSEWINDOW
        REM closes the window and sets w1 = 0
        CLOSEWINDOW w1
    ENDIF
ENDSUB

I suspect that I have either not correctly declared the parameter list of Excel Sub TestPassVars or the parameter list of EBasic sub TestPassVars. Thank you very much in advance for your assistance and suggestions.

By the way, use of the END statement in the above Emergence Basic code causes Excel to crash. I originally had an END statement in the code, but had to remove it. It took me hours to figure out why Excel crashed every time it called the DLL. Hopefully someone will investigate this problem. May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

[Ionic Wind Support Team]

Michael,
You didn't do your research.  That link I gave you explains how VBA passes arrays, and it isn't in a normal manner.  It uses COM SafeArrays, which is a special type of array only used by some Microsoft languages and of course by COM.

Here is the Microsoft link that explains it:

http://msdn.microsoft.com/en-us/library/ms221145.aspx

Very complicated, and you will have your work cut out for you to access them.  They are passed by pointer, and may contain VARIANT elements.

As for END.  Why did you think you should use an END statement?  I would expect it to crash, because END calls EndProcess to exit an executable and should never be used in a DLL.

Paul.

[EvangelMike]

Greetings! Thanks, Paul, for steering me in the right direction. It is interesting to note that I had submitted a nearly identical post to Microsoft's "Discussions in Excel Programming" and not one person there responded with any comments or suggestions. I'd say that Paul is top notch at keeping on top of things and being very helpful, and the other posters here have been very helpful as well. I'd also like to say that Tech Support for Emergence Basic is the best I have seen for any software I have purchased. Keep up the good work! I am currently researching SafeArrays. Here is an interesting selection I found on page 86 of "Financial Applications using Excel Add-in Development in C/C++" (The Wiley Finance Series)  by Steve Dalton

3.7.4 Passing VB arrays to and from C/C++
You may want to pass a VB array directly to or from a DLL function. When passing a
VB array to a DLL, the C/C++ function should be declared in the VB module as shown
in the following example. (The ByRef keyword is not required as it is the default.)

Declare Function C_safearray_example "Example.dll" _
          (ByRef arg() As Double) As Double

The corresponding C/C++ function would be prototyped as follows:

double _stdcall C_SafeArray_Example(SAFEARRAY **pp_Arg);

As you can see, the parameter ByRef arg() is delivered as a pointer to a pointer to a
SAFEARRAY. Therefore it must be de-referenced once in all calls to functions that take
pointers to SAFEARRAY's as arguments, for example, the OLE SafeArray functions.

When returning VB arrays (i.e., SafeArrays) from the DLL to VB, the process is similar
to that outlined in the previous sections for array Variants. SafeArray arguments passed by
reference can also be modified in place, provided that the passed-in array is first released
using SafeArrayDestroy().

In practice, once you have code that accepts and converts array Variants, it is simpler
to first convert the VB array to array Variant. This is done by simple assignment of the
array name to a Variant."

May you have a blessed day.
Sincerely,
Michael D Fitzpatrick