• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

How to interop C++ with VBA

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.
 
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#

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.




(*) for production, use the Axel Vogt solution.
 
Last edited:
thanks but already looked there, the design documantation links are dead
 
rent than manipulating Excel COM object within C++ console program introduced in Level9)
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.
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)
 
Another option might be to mail the Quantlib user group.
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.
 
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.
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.png
 
Back
Top