How to interop C++ with VBA

Gen Li

Gen Li

C++ Certificate with Distinction
Joined
8/20/20
Messages
60
Points
278
I have a VBA program that cost around 2 hours each run. I would like to lift some heavy into C++. (I'm trying to use C++ more and more)

My thought is construct a DLL using C++ and consume that DLL within the VBA code. I think this will make the whole VBA project much faster.

However, I need to pass some cls (vba class) objects and couple arrays into c++ dll on each call, and the c++ code should return an array of user defined object which has multiple data fields. And finally the vba code should consumed the returned objects.

Is it possible? How to setup this construct? Is there any material I can look into this?

(It is different than manipulating Excel COM object within C++ console program introduced in Level9)

Really appreciate for any advice and idea inputs

@APalley
@GONG CHEN
@Andy Nguyen
@Daniel Duffy
 
There's several ways to solve this problem. BTW it is not possible to write a dll in C++, it must be C!

Long story short: use the blue-chip advice from Axel Vogt. In used the same approach on an MBS application 4 years ago. It works fine.


//
It is possible to interface C++ and Excel using ATL but I would not advise it at the moment.
My Excel Driver uses COM indeed.
 
emetrics said:
This may help? Export C++ to Excel
Click to expand...
You can call it what you want so long as you don't call it C++! BTW no harm to call it C, but let's not inflate it.

Personally I find Dalton's approach daunting and middles ages at this stage. It would be torture for the new generations. In fairness, companies use this legacy.
The late Mark Joshi has xlw package.

I discuss C++ Automation and COM add-in the 1st edition of "Financial Instrument Pricing using C++" (Wiley 2004).
 
Last edited:
A friendly package is Excel-DNA in C#

Free and easy .NET for Excel | Excel-DNA

Free and easy .NET for Excel
excel-dna.net excel-dna.net

If I were to do it I would choose (as a learning project (*)) C++/CLI that speaks BOTH native C++ (the stuff in QN course) and .NET.


C++/CLI - Wikipedia

en.wikipedia.org en.wikipedia.org

C++ use in Excel - best option? | Elite Trader

Hi I want some expert/experienced advice on the best approach/method of using C++ code in Excel. For example building some pricing models in C++ and...
www.elitetrader.com www.elitetrader.com


(*) for production, use the Axel Vogt solution.
 
Last edited:
MadCPlusPlus said:
rent than manipulating Excel COM object within C++ console program introduced in Level9)
Click to expand...
Daniel Duffy said:
There's several ways to solve this problem. BTW it is not possible to write a dll in C++, it must be C!

Long story short: use the blue-chip advice from Axel Vogt. In used the same approach on an MBS application 4 years ago. It works fine.


//
It is possible to interface C++ and Excel using ATL but I would not advise it at the moment.
My Excel Driver uses COM indeed.
Click to expand...
Thanks, Duffy! The pdf has some good information I am searching for...

Yes, and maybe I should use ExcelDNA, but I think build a DLL that can be consumed by a workbook via VBA will make the deployment super easy (Basically user just need to copy the workbook and the .dll to his machine)
 
Daniel Duffy said:
Another option might be to mail the Quantlib user group.
Click to expand...
will try that but now i have more appreciation for what our core starts have built.. I just need to derive from one class, build my dll and load it in excel. It flows through nicely.
 
throll said:
will try that but now i have more appreciation for what our core starts have built.. I just need to derive from one class, build my dll and load it in excel. It flows through nicely.
Click to expand...
Sounds good. I never used dlls with 'real' C++ but just good old C. Have you static methods?
 
If I had to do Excel integration I would combine native C++ and .NET worlds

1. Write core code in native C++ and C++/CLI.
2. Write a C# wrapper for stuff in 1.
3. Call stuff in 2 from Excel-DNA.

In reality, legacy code will not go away.
 
I recently tried xlOil and it is fairly well-documented. I used with it Python, but it also supports C++ Excel interop.

P.S. You can use matplotlib for returning plots.

1699203817205.webp
 
Back
Top Bottom