Microsoft 365 Question: Excel VBA Instantiate C++ class

Question

I recently upgraded to from Office 2010 to Microsoft 365.

I have many years of Excel VBA, c#/.NET, C++, and Visual Studio 2019 (including earlier versions of all) experiences.

I have an application that I started ~8 years ago with Excel with VBA code for optimization algorithms that essentially aren’t possible in Excel.

For speed, I converted this VBA code to a C#/.Net COM add-in (DLL). The c# algorithm code is a class with both parameters (double, integers, strings, etc. and arrays of previous, etc.) and functions. It is possible to very efficiently code in C# and then use this class by instantiation in VBA with default parameter values. And then selectively in VBA update a subset of the parameters for the specific class function call run. (Essentially solution in a class DLL).

Recently, for greater latency reduction, I coded this algorithm in C++ (DLL). It is very fast in C++ compared to C# (with typical simple parameters: ~1 sec C++, ~20sec C#, substantially worst worse with more normal parameters).

In C++ I can’t find information on how to directly instantiate the C++ class from VBA. I have to call a C++ function and inefficiently pass all default including specific run parameters, then instantiate the class in C++, set all class parameters, and call required class function(s).

Can you guide me to information, examples, documents, experts, etc. on how to directly instantiate C++ (in DLL) class with parameter and functions from Excel 365 VBA?

My code will be more efficient, more flexible, and much simpler to use.

Answer 1

Just like a C# assembly must be wrapped with a COM wrapper to be able to be used from VBA, so also with a C++ ActiveX DLL.

That, or you can use a "classic" DLL with exported API entry points, which then get Declare-d in VBA.

Answer 2

Hi JimGunn,

Maybe you can refer to this answer: https://stackoverflow.com/a/25317559/14324452

Best Regards, Dylan

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our * *documentation* to enable e-mail notifications if you want to receive the related email notification for this thread.**

Answer 3

Make your DLL to COM Module with ATL
Then you can use the C++ Class in VBA or VBS.

Answer 4

If I understand you correctly you want to create a COM object using C++ that can be instantiated/used by Office VBA.

You can use ATL to create an in-process (DLL) server. First create a new ATL project - creating-an-atl-project. Make sure it is a Dynamic Link Library.

After the project is created you can use the built-in wizard to add a Simple Object adding-an-atl-simple-object. This will add the infrastructure to your project for a COM object. When using the wizard select Single Threaded Apartment and Dual interface.

At this point the project contains the code for a C++ class that represents the COM object. This bare-bones class and the interface that it implements has automatic support for IUnknown/IDispatch but it is now up to you to add the methods and properties of interest.

For adding methods and properties, see adding-a-method-visual-cpp and adding-a-property-visual-cpp. The ATL wizards will take care of infrastructure like updating the IDL file, adding declarations to the header file and empty definitions to the cpp file. You must provide the actual code to be executed.

Unfortunately, Microsoft made substantial changes to the ATL wizards in Visual Studio versions after VS2015. These changes were not well done and MS asserted that the problems were finally corrected in VS2019 and were then eventually back-ported to VS2017. See the-big-atl-wizard-misery.html You may find discrepancies between the actual functioning of the wizards in VS2019 and the MS documentation.