How do I pass Excel Ranges to/from Visual C DLLs?

[Ryan Poth]

I used to use the following C code in Visual C/C++ 1.0 with Excel 5.0 to pass Excel ranges to/from
my C DLL functions. I used the Excel4 macro "REGISTER" to register the function with the data type
"K". Now I am using Visual C/C++ 5.0 and trying to "Declare" the function in a Visual Basic macro,
using the line: "Declare Function ReverseCells Lib "TEST" (ByRef Original As Range) As Range" and it
returns the error value #NAME?. I have been searching all resources for a solution and cannot find
what I am certain is an extremely simple answer. Can anybody help?

#include <windows.h>

typedef struct fp {
unsigned rows;
unsigned columns;
double Array[100];
} FP;
typedef FP * LPFP;

__declspec(dllexport) LPFP ReverseCells(LPFP Original);

__declspec(dllexport) void CALLBACK LibMain(HINSTANCE hModule, WORD wDataSeg, WORD cbHeapSize, LPSTR
lpszCmdLine) {}

__declspec(dllexport) LPFP ReverseCells(LPFP Original) {
static FP LocalArray;
unsigned i;

LocalArray.rows = Original->rows;
LocalArray.columns = 1;

for(i = 0; i < LocalArray.rows; i++) LocalArray.Array[i] = 0.0; /* Initialize New array */

for (i=0; i < Original->rows; i++) {
LocalArray.Array[i] = Original->Array[Original->rows-i-1]; /* Reverse the
arrays */
}

return &LocalArray;
}

[Ryan Poth]

[Joe Toomey]

Ryan,

Here's a whole bunch of feedback: First, make sure you're calling the
VBA routine correctly. You mentioned that you get back #NAME?, which
indicates that you're calling the function from a spreadsheet. Assuming
you haven't made your own add-in, you need to make sure you preface the
function name with the workbook it resides in (i.e.
=MYBOOK!ReverseCells(A1:C4) ). Otherwise you'll get #NAME? for all of
your functions.

Assuming that was't your problem, and you are just unable to declare
your function in VBA to pass an FP structure (type K), You have (at
least) three alternatives for getting your Excel range into your DLL
procedure. The easiest way is to continue using the Excel4 REGISTER
command, and then use the "handle" that comes back to call the DLL
function from VBA. You can do this by passing the handle to
Application.Run like this:

Dim iCmd_ReverseCells as Variant, vRange as Variant, vResult as Variant

iCmd_ReverseCells = _
ThisWorkbook.Sheets(sRegisterSheet).Range("cmd_ReverseCells").Value

...

vResult = Application.Run(iCmd_Reverse, vRange)

(Substitute your function's range name and macrosheet, obviously.)

I'm not quite sure how VBA will handle the FP structure (type K), but I
know that it works fine in translating a variant type to an XLOPER
structure (type R.) If you find that by passing and returning variants,
Excel is not able to map your range to & from an FP structure (the GP
Fault should be a dead giveaway here :-), you can always leave the call
to your DLL routine on an Excel4 Macrosheet, and then invoke it from VBA
using the ExecuteExcel4Macro method.

The last and most painful option is to rewrite your DLL routine to use
Excel's IDispatch interface and access the Range of cells directly.
IDispatch is doublely slow in VB flavors, & you'll write 5-10 times more
code than you have today... I'd try one of the other routes if I were
you.

Hope this helps,
--Joe