Is it possible to pass a reference to a pointer from Excel VBA to C++?

Question

I would like to call my own C++ dll function from excel vba:

void my_cpp_fun ( int& n_size, double*& my_array);

The C++ function creates an array my_array of variable size n_size (this size is computed within my_cpp_fun).

Can I interface this function as is to VBA without using any Excel specific stuff in my C++ code?

So basically what I am looking for is a VBA Declare statement like

Declare Sub my_cpp_fun Lib "my_cpp.dll" (n_size As Long, Ref_to_Ptr_Qualifier my_array As Double) 

An additional problem that just occured to me: If I allocate memory inside the c++ dll using new, will that memory be available once the dll function returns control to VB? If that is not the case, the above is pointless...

Answer 1

Short answer: yes, it is possible (and easier than the COM route, in my opinion) to call functions in a DLL from VBA. In my experience, the best way to go is to write wrapper functions with C linkage (to avoid running into various C++ name-mangling schemes) and exposing an interface of pointers rather than references (as the appropriate VBA type to declare a reference argument or result will be rather difficult to predict).

A great guide for how to write appropriate Declare statements (assuming 32-bit Windows) is Chapter 2 of the book "Hardcore Visual Basic", if you can find it.

Note also that any functions exposed to VBA via Declare statements will need to use stdcall (aka WINAPI) calling convention.

TLDR:

I'd do this:

extern 'C' {
    void WINAPI my_cpp_fun_wrapper ( int *n_size, double **my_array )
    { 
        my_cpp_fun(*n_size, *my_array); 
    } 
}

and then

Declare Sub my_cpp_fun_wrapper Lib "my_cpp.dll" (ptr_n_size As Long, ptr_ptr_my_array As Long)

and use the various *Ptr functions of VB6/VBA to get the pointers to my data.

Answer 2

You'd need to create a COM object that exposes your function and load it in your VBA using CreateObject