# Integrate C++ program into VBA

#### Bridgett

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.

#### Andy Nguyen

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.

#### frankm1342

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.

#### Chris

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

#### Bridgett

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.

#### Andy Nguyen

There used to be some Excel/VBA workshops run by the current students in the program a couple years back. Maybe we can ask one of you guys to demonstrate the power of VBA for us in the future?

#### Bridgett

or better yet, make it our very own little conference so that we could learn from one another and get real-time feedback?

#### Chris

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.

#### maciek

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?

#### Andy Nguyen

maciek said:
How about creating our own database with C++ and VBA codes so we can exchange them?
Great idea. I will look into the database that alumni of the program created. Maybe we can revive some of the projects they started.

#### Bridgett

Great info. Thank you all so much, guys!!! :P

#### Bridgett

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

#### Chris

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

#### Bridgett

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?

#### Chris

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.

#### Bridgett

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.

#### jimmycc

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

#### Ilya W

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

#### Bridgett

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.

Replies
15
Views
2K
Replies
1
Views
2K
Replies
2
Views
2K
Replies
10
Views
4K
Replies
0
Views
771