Pass data of arbitrary type between VBA and dll

Question

So I am working on an Excel Project that is going to load a C++ dll using VBA. What I'd like to do is to be able to pass an Excel range with no specific type (data can be numerical or categorical) to the C++ dll (The best way I can describe my Excel range is of the type variant).

So the steps probably involve:

1. Load the dll in VBA
2. Send the excel range to dll (The range may contain columns of numbers and/or columns of strings)
3. manipulate the data from excel in the dll file

I am thinking of using excel variant and C++ variant. But it's not clear for me how to use the C++ variant as I couldn't find any good documentations on it.

Another suggestion I received was to ues COM programming.

My Questions:

• Could a kind soul possibly provide pointers for me on how to proceed? (e.g. by providing the C++ prototype, and a simple example of how to handle the variant)
• Does anyone know any good documentation/tutorial on using C++ Variants (and perhaps jointly with VBA)?
• Is using COMs preferable to using VARIANTS if speed is an issue?
• Is using the C API an option?

UPDATE:

• The size of the ranges I need to manipulate can be large (~ 500,000 rows).
• Speed is a factor, thus, I'd like to avoid unnecessary copying as much as possible.

Answer 1

Provided you only want to pass data to the dll (and not pointers to actual Excel objects such as Range), you have two fundamental options:

1. You have huge data sets and want to avoid copying as much as possible.
   In this case you might want to pass that same Variant array you get by calling Range.Value. In order to do that, you will have to write a little TLB to reference from VB, in which you would describe your exported C++ function as expecting a SAFEARRAY(VARIANT)*. This is because the Declare operator will not let you actually pass a SAFEARRAY*.
   The function will look like this:

   LONG __stdcall ReturnArrLowerBound(SAFEARRAY** ppArr)
   {
       if (ppArr == NULL) return -1;
       SAFEARRAY* pArr = (*ppArr);
   
       LONG res = 0;
       SafeArrayGetLBound(pArr, 1, &res);
   
       return res;
   }
   

   And the TLB descripion will look like that:

   [
       uuid(A686B138-D8CE-462e-AEF2-75DA4DBF1C75)
   ]
   library foo
   {
       [
           dllname("TestSafearray.dll")
       ]
       module vb
       {
           [entry("ReturnArrLowerBound")]
           LONG __stdcall ReturnArrLowerBound(SAFEARRAY(VARIANT)* ppArr);
       }
   }
   

   And your C++ project will obviously include a def file:

   LIBRARY "TestSafearray"
   
   EXPORTS
       ReturnArrLowerBound
   

2. Your data sets are reasonably sized and you don't mind a little bit of copying.
   Then make your C++ function to accept a mere int[] and Declare it in VB as accepting arr() as Long. On VB side, allocate an array on Longs and copy the elements into it from the Range.Value array.