• 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!

Integrate C++ program into VBA

Joined
5/15/06
Messages
229
Points
26
I've been writing data-analysis tools using VBA to handle projects at work, but the data sizes keep getting bigger. I feel that it's about time to get C++ programs involved to speed up processes. But I've never done this before. So, I was wondering if anyone here can give me some hints. How can I integrate C++, VBA, Excel, and even Access together? What platform/interface would I need to get? Any idea would be greatly appreciated.
 
Bridgett,
You may look into Application Programmability Component (APC)
The APC, a set of APIs on top of the core Visual Basic for Applications API, simplifies the process of integrating Visual Basic for Applications into your application. It is the preferred method of integration. MFC and C++ helper class templates also are included to ease integration in MFC and C++ applications.
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaroffdev/html/vbahow.asp

All the best.
 
Bridgett, how hard is it to learn VBA compared to c++? There is a guy in our program (1 year ahead of us) who uses VBA in his job here at BofA (he does something with hedge fund risk analysis). I always here ppl saying how it is useful if you can do VBA because then you can make excel do some reaally cool stuff.
 
Frank,

VBA is much simpler than C++. VBA is a procedural language and is based on events, i.e. mouse click, a key press, close form, etc. You can also write separate functions and subroutines.

It is designed to run off Excel, Access, Word, Frontpage. You can write very useful macros and routines that can automate repetetive tasks to building a full blown application.

You can find lots of samples on the web. To practice you can go into Excel/Access, go to the Visual Basic Editor (alt-F11)

Thanks,

Chris
 
Frank-

Chris was right -- VBA is simple and I never really learned it (I just use it). Also, I enjoy using objects (user-defined classes) in VBA -- it helps to make the code more logical, cleaner and reusable.
 
or better yet, make it our very own little conference so that we could learn from one another and get real-time feedback?
 
Actually the best thing about VBA, which I neglected to mention is the ability to integrate other applications in windows via ActiveX. You can use functionality from say Word and build it into Excel.
 
Chris said:
Actually the best thing about VBA, which I neglected to mention is the ability to integrate other applications in windows via ActiveX. You can use functionality from say Word and build it into Excel.

Using ActiveX you can also access to Bloomberg servers and pull anything you want into excel or access. This is used rather for historic, static data. For live data you just type functions in excel cells.

How about creating our own database with C++ and VBA codes so we can exchange them?
 
.net

The following is what I read from some article online:
"With the arrival of VB.net, API calls are now being phased out. You should only use API calls in VB 6 or earlier"


I've heard that .net is really powerful and useful, but I've never touched it. And I doubt if I even have a basic idea about it. Any suggestions or recommendations on learning .net or, specifically, the VB.net?
 
Bridgett,

I use VB.NET at work. I would say its a significant improvement over VB. The fact you can write classes and every object is treated as a class makes for much cleaner and organized code. VB.Net is also fully object oriented and supports multi-threading. The learning curve may be a little steeper than VB but if you know OO programming its not that bad.

I didn't know they were phasing out API calls for .NET. Do you have an article on why they are doing that?

Thanks,

Chris
 
Chris said:
Bridgett,

I use VB.NET at work. I would say its a significant improvement over VB. The fact you can write classes and every object is treated as a class makes for much cleaner and organized code. VB.Net is also fully object oriented and supports multi-threading. The learning curve may be a little steeper than VB but if you know OO programming its not that bad.

I didn't know they were phasing out API calls for .NET. Do you have an article on why they are doing that?

Thanks,

Chris


Thanks for your input, Chris. Question-- reading from your posting, it seems to me that writing classes and using objects are the main advantage that VB.NET has over VB. But one can also easily write/use classes in VB as well, could you point out the difference between the two in terms of using objects? And you meantioned "VB.Net is also fully object oriented", so what exact new features/advantages does it now have comparing to VB?

Lastly, the text I quoted was not really from a complete article (it was just some side notes off a website), but I'm pasting the related passage here for you and let me know if you think it makes sense:

If you have written programs for the Windows platform using Visual Basic (or Delphi or VC++ for that matter) then you have used the Win32 API, at least indirectly. Because, quite simply, any program you write for windows in VB, uses the Windows API. Each and every line of code you write is translated into corresponding API calls which the system uses to get the tasks done.

API (Application Programmers Interface) is a set of predefined Windows functions used to control the appearance and behaviour of every Windows element (from the outlook of the desktop window to the allocation of memory for a new process). Between them, these functions encapsulate the entire functionality of the Windows environment. So we can consider API as the native code of Windows. The other languages act as an attractive and often user-friendlier shell to the API promoting easier and automated access to it. An example is VB, which has replaced a sizeable portion of the API with its own functions. But every line of code written in VB is converted to its equivalent API calls.


So, when it says the API calls "phase out", does it mean one should avoid direct API calls, since there are sizable replacements in VB now?
 
Bridgett said:
But one can also easily write/use classes in VB as well, could you point out the difference between the two in terms of using objects? And you meantioned "VB.Net is also fully object oriented", so what exact new features/advantages does it now have comparing to VB?

Without going through a list of features, what I find most useful about .NET everything is treated as a class, the forms,listbox,textbox, datagrid, etc. With that you can extend these classes into your own class and add your own functionality. You can overload an event or attribute of an object to have new or different functionality or even add another object to it. You can for example have a combobox to dropdown a datagrid.


Bridgett said:
So, when it says the API calls "phase out", does it mean one should avoid direct API calls, since there are sizable replacements in VB now?

I've only used API for VBA so I can't say from direct experience that you use API for .NET for things. But it would make sense that there are some Windows functions out there that .NET just doesn't have and an API call would be necessary.
 
Chris said:
I've only used API for VBA so I can't say from direct experience that you use API for .NET for things. But it would make sense that there are some Windows functions out there that .NET just doesn't have and an API call would be necessary.

Chris I have to agree with you on this one. And thank you for the explanation.

So much to learn, so little time.
 
I had researched into using .NET to create components for Excel earlier this year (Jan 2006). Here is what I found:


1) Debugging problems is quite tedious. Is the problem in your Excel code, .NET code, or an environmental issue? When .NET code bombs out, the error does not surface.

2) The .NET component is quite slow.


3) I think an additional add-on has to be purchased for Visual Studio.



Jimmy
 
Hi Bridget,
My name is Ilya I'm a part time student on the program since september 2004. I'm working on the same problem now, and can show you simple example how to integrate c++ dll file and vba excel. I'm working with c++.net and vba embedded in excel 2003. Here is simple method how to import one dimensional array from VBA to C++ and process it there. At least you will have some idea how to work with it.

C++ CODE:

1. open empty windows 32 project
2. insert new .h file and call it MyDll.h
3. insert .DEF file which links your c++ functions with VBA functions MyDll.Def
4. insert .cpp file MyDll.cpp

you can send array from VBA to C++ using the first element of array on VB side or using SAFEARRAY structure, here is simple example

also in VB and C++ Double have the same size, but integer in VB is short in C++

C++:
//prototype in MyDll.h
#include <windows.h>
short __stdcall Double_Array(double *arr, short size);
 
//MyDll.Def
EXPORT
Double_Array
 
//MyDll.cpp simple code to change all elements to 3
short __stdcall Double_Array(double *arr, short size){
int i;
for (i=0; i<size; i++)
arr[I]=3;
return(0);
}

VBA CODE:
Code:
Option Explicit
 
'path to library
Private Declare Function Double_Array _
Lib "C:your_path\MyStDll.dll" (ByRef arr As Double, ByVal size As Integer) As Integer
 
Private Sub Command1_Click()
Dim arr(1 To 5) as Double, lbsum As Double, lasum As Double
Dim arr_size as Integer, k as Integer, i As Integer
arr(1) = 2
arr(2) = 2
arr(3) = 2
arr(4) = 2
arr(5) = 2
 
'Sum before call to c++
For i = 1 To UBound(arr)
lbsum = lbsum + arr(i)
Next i
 
arr_size = CInt(UBound(arr()))
k = Double_Array(arr(1), arr_size)
 
'Sum after call to c++
For i = 1 To UBound(arr)
lasum = lasum + arr(i)
Next i
MsgBox "Sum of the elements before= " & lbsum & " " & _
"Sum of the elements after= " & lasum
End Sub
good luck
 
Ilya-

Thank you very much for sharing the info. It gives me a good idea on how to put pieces together. I will try to run some pilot programs when I get the chance and I probably will have more questions for you since you are steps ahead of me on this matter. Again, thank you for helping.
 
Back
Top