Transfer data of arbitrary type between VBA and dll

Question

So, I am working on an Excel project that will load a C ++ dll using VBA. What I would like to do is be able to pass an Excel range without a specific type (data can be numeric or categorical) for a C ++ dll (the best way to describe my Excel range is with the variant type).

So the steps probably include:

• Download dll in VBA
• Send the excel range to the dll (the range may contain columns of numbers and / or columns of rows)
• manage data from excel to dll file

I mean using the excel variant and the C ++ variant. But it is not clear to me how to use the C ++ variant, since I could not find good documents on it.

Another suggestion I received is COM programming.

My questions:

• Can a good soul possibly give me directions on how to act? (e.g. by providing a C ++ prototype and a simple example of how to handle the variant)
• Does anyone know any good documentation / tutorial on using C ++ options (and possibly in conjunction with VBA)?
• Is using COMs preferable for using VARIANTS if speed is a problem?
• Does the API use the C API?

UPDATE:

• The size of the ranges that I need to be manipulated can be large (~ 500,000 lines).
• Speed ​​is a factor, so I would like to avoid unnecessary copying as much as possible.

Answer 1

If you want to transfer data only to the dll (and not pointers to real Excel objects such as Range ), you have two main options:

• You have huge data sets and want to avoid copying as much as possible.
  In this case, you can pass the same Variant array that you call by calling Range.Value . To do this, you will need to write a little TLB for the link from VB, in which you would describe the exported C ++ function, waiting for SAFEARRAY(VARIANT)* . This is because the Declare statement will not allow you to pass 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 description will look like this:

   [ 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 

• Your datasets are reasonably sized, and you don't mind small copying.
  Then make your C ++ function an accepted simple int[] and declare it in VB as accepting arr() as Long . On the VB side, select the array on Long and copy the elements from the Range.Value array Range.Value .