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.

Billon

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.

Timoteo

Thanks! If I am not mistaken, writing the TLB involves COM programming, right? And then does that limit the portability of the application (particularly on linux machines)? – Billon

@Billon No, it does not involve COM programming per se. You only write TLB in order for VB to understand your C++ function signature. Other than that, the function is a plain, non-COM, classic style exported function. – Timoteo

Thanks for the prompt response. Does using Variant allow me to pass mixed types to the DLL? Or it only allows having a single type (be it strings or numbers, etc). – Billon

@Billon Each element of the array is VARIANT, so they all have the same type - VARIANT. What each variant contains is completely different question, and yes, they all can contain different actual data. You decide whether or not you want to handle that on the C++ side. – Timoteo

Thanks for your help! I really appreciate it. I will try to implement it (may take me a few days) and will get back to you if I face any further problems. – Billon