Passing a 2D Variant Array From VBA to a DLL

[TIMERESRCH]

I've been able to successfully pass a one dimensional array of integers
generated within VBA (by populating an array with random numbers) to a DLL
(written in C). However, I need to pass a 2D array of decimal numbers taken
from the spreadsheet and stored in VBA, but it's not working. I've tried
numerous experiments, and tried to hunt down an answer via Google, etc.

I'm using this code:

Declare Function MatchInteger32 Lib "vbaexts5.DLL" Alias "MatchInteger" (iMatch
As Long, d() As Variant) As Double

'(I'm using Double as the type because the variant array is designated as
Variant/Double in the Locals window, and I'm trying to return one of the
decimal values I'm sending to the DLL as a test.)

Dim d() As Variant
d() = Worksheets("Demo").Range("L11:M46").Value

'(As I understand it, VBA automatically stores a block of numbers from the
spreadsheet as a variant array when presented with a range as above)

iTmp = 2

'The call to the DLL function:
iRes = MatchInteger32(iTmp, d())

'(In this case iTmp is irrelevant, but I've left it in because when I tried to
remove that argument in the DLL function I got an error message when trying to
build the DLL to the effect that "_MatchInteger@8" was unrecognized; this is
part of an essential line of code in the DLL without which VBA can't find the
MatchInteger function in the DLL and returns an error message (453 I believe).
That line of code is: #pragma comment(linker,
"-export:MatchInteger=_MatchInteger@8") I've left iTmp in for completeness
here, but I don't think it has any bearing on the problem of passing a 2D
array.)


The relevant code in the DLL is as follows:

ix[0] = 0 ; ix[1] = 1 ;
if(FAILED(SafeArrayGetElement(*psa, ix, &iNum)))
goto MatchIntegerError;

Each time the DLL is returning the MatchIntegerError value (I've set it at
8000) back to the spreadsheet, so the SafeArrayGetElement command is not
working. Again, everything worked fine when sending a 1D array of integers, so
it has to be something related to sending a two dimensional structure, or the
variant type of the array...I just don't know.

Surely someone must be using DLLs to do fast calculations; I sure hope you can
show me the way. By the way, I'd also like to know how it's possible to debug
the DLL if it's being called from VBA; I'm new to C and it's a mystery to me.
Thanks in advance.

 

[Tom Ogilvy]

There is a difference between a variant array which is probably what you
successfully passed (or an array of integers for that matter) and a variant
variable that holds an array.

Even though you declare

Dim d() as Variant

When you actually do the assignment, I suspect it is changed to a variant
variable that contains an array.

As a test, do this

dim d1() as Variant
Dim d2 as Variant

Redim D1(0 to 3)
D1(0) = 9
D1(1) = 10
D1(2) = 11

iTmp = 2

'The call to the DLL function:
iRes = MatchInteger32(iTmp, D1)
d2 = d1
ires1 = MatchInteger32(itmp,D2)

if the first goes and the second fails, then it might be this problem. If
so

Try changing your function to expect a variant and then see if you can
process the array contained in the variant.

These are just guesses as I haven't tried any of this with a DLL.

--
Regards,
Tom Ogilvy

I've been able to successfully pass a one dimensional array of integers
generated within VBA (by populating an array with random numbers) to a DLL
(written in C). However, I need to pass a 2D array of decimal numbers taken
from the spreadsheet and stored in VBA, but it's not working. I've tried
numerous experiments, and tried to hunt down an answer via Google, etc.

I'm using this code:

Declare Function MatchInteger32 Lib "vbaexts5.DLL" Alias "MatchInteger" (iMatch
As Long, d() As Variant) As Double

'(I'm using Double as the type because the variant array is designated as
Variant/Double in the Locals window, and I'm trying to return one of the
decimal values I'm sending to the DLL as a test.)

Dim d() As Variant
d() = Worksheets("Demo").Range("L11:M46").Value

'(As I understand it, VBA automatically stores a block of numbers from the
spreadsheet as a variant array when presented with a range as above)

iTmp = 2

'The call to the DLL function:
iRes = MatchInteger32(iTmp, d())

'(In this case iTmp is irrelevant, but I've left it in because when I tried to
remove that argument in the DLL function I got an error message when trying to
build the DLL to the effect that "_MatchInteger@8" was unrecognized; this is
part of an essential line of code in the DLL without which VBA can't find the
MatchInteger function in the DLL and returns an error message (453 I believe).
That line of code is: #pragma comment(linker,
"-export:MatchInteger=_MatchInteger@8") I've left iTmp in for completeness
here, but I don't think it has any bearing on the problem of passing a 2D
array.)


The relevant code in the DLL is as follows:

ix[0] = 0 ; ix[1] = 1 ;
if(FAILED(SafeArrayGetElement(*psa, ix, &iNum)))
goto MatchIntegerError;

Each time the DLL is returning the MatchIntegerError value (I've set it at
8000) back to the spreadsheet, so the SafeArrayGetElement command is not
working. Again, everything worked fine when sending a 1D array of integers, so
it has to be something related to sending a two dimensional structure, or the
variant type of the array...I just don't know.

Surely someone must be using DLLs to do fast calculations; I sure hope you can
show me the way. By the way, I'd also like to know how it's possible to debug
the DLL if it's being called from VBA; I'm new to C and it's a mystery to me.
Thanks in advance.

 

[TIMERESRCH]

Thanks very much. I'll try this when I have a chance tonight and let you know
if there was a breakthrough.

 

[Alan Beban]

I'm in somewhat over my head here, not knowing anything about DLL, but
if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might
try substituting for

Dim d() As Variant
d() = Worksheets("Demo").Range("L11:M46").Value

the folowing:

Dim d() As Double
Assign Worksheets("Demo").Range("L11:M46").Value,d()

That will assign the values from the range to a Double() type array.

Alan Beban

I've been able to successfully pass a one dimensional array of integers
generated within VBA (by populating an array with random numbers) to a DLL
(written in C). However, I need to pass a 2D array of decimal numbers taken
from the spreadsheet and stored in VBA, but it's not working. I've tried
numerous experiments, and tried to hunt down an answer via Google, etc.

I'm using this code:

Declare Function MatchInteger32 Lib "vbaexts5.DLL" Alias "MatchInteger" (iMatch
As Long, d() As Variant) As Double

'(I'm using Double as the type because the variant array is designated as
Variant/Double in the Locals window, and I'm trying to return one of the
decimal values I'm sending to the DLL as a test.)

Dim d() As Variant
d() = Worksheets("Demo").Range("L11:M46").Value

'(As I understand it, VBA automatically stores a block of numbers from the
spreadsheet as a variant array when presented with a range as above)

iTmp = 2

'The call to the DLL function:
iRes = MatchInteger32(iTmp, d())

'(In this case iTmp is irrelevant, but I've left it in because when I tried to
remove that argument in the DLL function I got an error message when trying to
build the DLL to the effect that "_MatchInteger@8" was unrecognized; this is
part of an essential line of code in the DLL without which VBA can't find the
MatchInteger function in the DLL and returns an error message (453 I believe).
That line of code is: #pragma comment(linker,
"-export:MatchInteger=_MatchInteger@8") I've left iTmp in for completeness
here, but I don't think it has any bearing on the problem of passing a 2D
array.)


The relevant code in the DLL is as follows:

ix[0] = 0 ; ix[1] = 1 ;
if(FAILED(SafeArrayGetElement(*psa, ix, &iNum)))
goto MatchIntegerError;

Each time the DLL is returning the MatchIntegerError value (I've set it at
8000) back to the spreadsheet, so the SafeArrayGetElement command is not
working. Again, everything worked fine when sending a 1D array of integers, so
it has to be something related to sending a two dimensional structure, or the
variant type of the array...I just don't know.

Surely someone must be using DLLs to do fast calculations; I sure hope you can
show me the way. By the way, I'd also like to know how it's possible to debug
the DLL if it's being called from VBA; I'm new to C and it's a mystery to me.
Thanks in advance.

 

[TIMERESRCH]

Alan, your array functions appear to be a godsend.

I've spent another night doing numerous tests and not getting very far. At one
point though I noted in the VBA Locals window while in break mode that the data
sent from the spreadsheet into the VBA array via the code and functions you
supplied started at index 1, even if I used the following:

Dim d() As Double

ReDim d(0 To 35) As Double

Assign Worksheets("Demo").Range("L11:L46").Value, d()

In other words, the ReDim command appeared to have no effect on the numbering
of the 36 elements of the array. At that point I decided to check your
downloaded file to see if you had any routine that would change the base to
zero--and sure enough, I came across the ConvertBase function. So I put that
function code into the module, and used this in my procedure:

d() = ConvertBase(d(), 0)

Here are the results and the set-up before and after:

With the spreadsheet range assigned to a VBA array as normal, the DLL failed:

Here's what the Locals window showed in that default situation:

+ d Integer(1 to 36, 1 to 2)


However, using your ConvertBase function following the storing of the array in
VBA, the DLL worked:

+ d Integer(0 to 35, 1 to 2)

Apparently the VBA array sent to the DLL has to have a zero base to coincide
with the way C treats the array, though the columns don't appear to matter in
this case, because the 1 and 2 designations worked fine when using index
addressing in the DLL.

So I succeeded in getting spreadsheet data transferred into the DLL; prior to
this I could only generate an array within VBA itself and send it to the DLL.

I've also managed to get the DLL to recognise two dimensions, as you can see
from above--and after several days of trying that is quite a breakthrough.

Also, when I passed the spreadsheet-default variant array, Excel crashed--so
once again your Assign function for setting up an integer or a double array
instead of the default variant array appears invaluable. (It could be that the
fault was caused by an incorrectly configured parameter being sent back by the
DLL, and so it might not be the fault of the variant array itself--still, your
function appears to overcome a potentially serious problem there.)

Now I just have to figure out how to get decimal values back from the DLL; at
the moment I'm getting back either very large or small numbers when I request a
particular decimal number in the second column of an array, but those odd
values are at least consistent when the same decimal test number is used in the
test cell of the spreadsheet range. I just have to set up the correct type in
the DLL, and/or learn more about how to set up things for compatibility on that
side. (I had a similar problem when trying to return simple integers from an
array sent to the DLL. I had used an integer type over there to select the
integer from the array, but that was returning very strange numbers. When I
changed the type to "short" on the DLL side, as the original code there had
used, I started getting back the correct integers....)

Anyway Alan, I'm indebted. Thanks! And thanks to Tom for his idea, though so
far I haven't been able to check that approach.

 

[TIMERESRCH]

Whoops! You know that thing that Homer Simpson says whenever he makes a
mistake....

After using the double type on the VBA side to send out the decimal array, I
just re-checked the C code on the DLL side and immediately realized I'd used
the "float" type for the decimal values. As I've learned it, the "float" type
in C corresponds to the "single" type in VBA, whereas the "double" type in C
matches VBA's double type. After I'd updated the DLL with the double type,
decimal values started being returned to the spreadsheet. Now I can start to
re-code my VBA application in C and do some really serious calculations.

Thanks again.

 

[Alan Beban]

Thanks for the kind words (omitted); they're much appreciated.

At one
point though I noted in the VBA Locals window while in break mode that the data
sent from the spreadsheet into the VBA array via the code and functions you
supplied started at index 1, even if I used the following:

Dim d() As Double

ReDim d(0 To 35) As Double

Assign Worksheets("Demo").Range("L11:L46").Value, d()

In other words, the ReDim command appeared to have no effect on the numbering
of the 36 elements of the array. . . .

Not exactly. The Assign function picks up the index and dimensions from
the input range/array; if that's a worksheet range, it's necessarily
two-dimensional and 1-based. If you run the following you can see what's
happening:

Dim d() As Double
ReDim d(0 to 35) As Double 'This As Double is superfluous

Debug.Print ArrayDimensions(d), LBound(d) 'This prints 1 0

Assign Worksheets("Demo").Range("L11:L46").Value, d()

Debug.Print ArrayDimensions(d), LBound(d) 'This prints 2 1

Contrast this with:

Dim d() As Double
ReDim d(0 To 3) As Double 'Again this As Double is superfluous

Debug.Print ArrayDimensions(d), LBound(d) 'This prints 1 0

Assign Array(1, 2, 3, 4), d

Debug.Print ArrayDimensions(d), LBound(d) 'This prints 1 0

Alan Beban

 

[Alan Beban]

Because you mentioned speed of execution as a significant factor, be
aware that the second below is faster than the first. Whether the
differential is significant in your application I don't know.

Sub test3004()
Dim d() As Double, rng As Range, arr
Set rng = Worksheets(4).Range("A1:G5")
arr = rng
Assign arr, d
d = ConvertBase(d, 0, 0)
End Sub

Sub test3005()
Dim d() As Double, rng As Range, arr
Set rng = Worksheets(4).Range("A1:G5")
arr = rng
ReDim d(0 To 4, 0 To 6)
For i = 0 To 4: For j = 0 To 6
d(i, j) = arr(i + 1, j + 1)
Next j: Next i
End Sub

Alan Beban

 

[Alan Beban]

The difference on my machine was on the order of three-tenths of a
millisecond.

Alan Beban