how to pass string array from C/C++ dll to vba (Excel)

Question

how to pass string array from C/C++ dll to vba (Excel)

dll in Visual Studio

dll is not managed, ATL, etc.

regards Andy

Answer 1

One of the ways to handle this is to write your DLL to return a SAFEARRAY of type Byte as the function result (VB arrays are OLE SafeArrays).

To do this you have to read up on the SafeArray APIs and structures. I don't know of it myself, but the main things you'll need are the SAFEARRAYBOUND structure and the SafeArrayCreate API. What the API returns to you, you return to VBA. And you'll be done.

Answer 2

Microsoft wrote a whitepaper many years ago detailing some best practices for writing DLLs to use with VB5/VB6, which of course would also apply to VBA. It is reprinted with permission, and available in the original Word form, here:

Microsoft: Developing DLLs for VB5 http://vb.mvps.org/tips/vb5dll.asp

See section 8 ("Passing and Returning Arrays") for help with this task. There is an example that is written specifically to demonstrate passing and returning arrays of strings.