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]

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]

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]

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]

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.

[cw202]

http://www.amazon.com/gp/product/0470024690/sr=8-1/qid=1153881903/ref=sr_1_1/102-1372161-4364949?ie=UTF8


http://xlw.sourceforge.net/


Regards,

cw202

[Bridgett]

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

[Bridgett]

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]

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]

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.



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]

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 Weinstein]

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++

//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:

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.

[DominiConnor]

I teach how to build VBA/Excel DLLs in the UK, I have lecture PPTs with some example code.
If you want them drop me a line at my pimping address.


(no, you're not allowed to question why a pimp teaches c++)

[Andy]

Has anyone experienced with Excel 2007 and .net ? Are these combos more problematic than excel 2003 and VBA ?

[Cristian Matei]

There is a free add-in download for VS.Net, similar to VBA and it offers the benefits of the .NET environment. Debugging could be easily done in Visual Studio.net IDE.

http://www.microsoft.com/downloads/details.aspx?familyid=5e86cab3-6fd6-4955-b979-e1676db6b3cb&displaylang=en

[rholowczak]

Not sure if this is helpful, but for Baruch students with access to Books24x7
(see the Newman Library home page) there is a new book posted today:

Financial Applications using Excel Add-in Development in C/C++, 2nd Edition (http://www.amazon.com/Financial-Applications-using-Development-Finance/dp/0470027975/) by Steve Dalton John Wiley & Sons © 2007 (584 pages)
Including example projects that demonstrate the potential of Excel when powerful add-ins can be easily developed, this book is a complete how-to guide for the creation of high performance add-ins for Excel in C and C++ for users in the finance industry.

I did not read this book yet - just thought it might be relevant.

Cheers,

Prof .H.

[Andy]

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.
This requires the machine to have Bloomberg API installed with a valid license, I believe.
Matlab has the financial datafeed toolbox which can get BB, Reuters data directly.
This means we can't just get the data from home computers.
How about creating our own database with C++ and VBA codes so we can exchange them?
I'm really interested in this. I believe many members here have years of experience in VBA, .NET, C++, etc so if they are open to share, I'm all ears.

[DominiConnor]

Bloomberg licences come in several flavours, and you may find that the academic licence you have access to has limitations like the number of series that can be downloaded.

As for Steve Dalton's book, I think you can assume that it works with Excel 2007, because we were on the technical beta team for E12, and his stuff was used to check that it works.

.NET is not a great way of driving Excel. MS has been trying to move people to this mess for years, and their net progress has been negative. At one point they were going to so brain damage Excel by removing add-ins and VBA macros that it resembled the sort of pretty but low function **** that Macintosh users prefer instead of a tool for grown ups.

XLLs are not the easiest interface, and VBA DLLs require you to think clearly, but debugging .NET stuff can be a nightmare.

[Richard]

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?

I don't have experience with VBA or VB.net, but worked in MS environment for a few years, I know they do a good job in education so they can lure admin/dev to their technology. MS is a huge marketing machine.

After some searching, here is a webcast that may be useful:
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032326222&EventCategory=5&culture=en-US&CountryCode=US


It also have a link to more general Office webcasts, but they seem to be less related with VBA.
Microsoft Office System Webcasts: Enhance Your Skill Set with Expert Tips: Enhance Your Skill Set with Expert Tips (http://www.microsoft.com/events/series/officesystemwebcasts.aspx)

If you poke around the site a little more, you may figure out how to search or narrow down to the area that you are interested in. I am sure they have more webcasts or online learning stuff for VBA/Office.

They also have live seminars where you can register and go for free. You can get popcorn, drinks, and maybe a book if you can answer a few silly questions.

Be aware, though, what they tell you in these seminar/webcasts probably works 90% of the time so you will like their technology and get hooked. But then the other 10% you would have to google all over the place, or try to decipher some MS KB articles, and debug to figure it out.

[bobbychopra]

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


Sorry, but I really disagree with your views.
1) Debugging a problem is a skill. You could send messages from your .Net code to excel and it could display the error, or you could write the error messages to a Log file or the system EventLog.

2) What .Net component are you referring to? I am using a .Net assembly which connects to a C++ program [legacy code] and updating cells in Excel in Real-time. It isn't slow. I guess it would be naive for me to judge this issue. I would need to figure out what you were trying to achieve and how it was designed.

3) Nope, no additional purchase.

Here are some examples.
http://msdn2.microsoft.com/en-us/library/aa140061(office.10).aspx (http://msdn2.microsoft.com/en-us/library/aa140061%28office.10%29.aspx)
http://msdn2.microsoft.com/en-us/library/aa140056(office.10).aspx (http://msdn2.microsoft.com/en-us/library/aa140056%28office.10%29.aspx)

[PatM]

here's something

[Vic_Siqiao]

Has anyone experienced with Excel 2007 and .net ? Are these combos more problematic than excel 2003 and VBA ?

is there any big differences between EXCEL03 and 07 that we should pay attention to?

[raywin]

If you wanna integrate C++ into VBA, why not using COM? Currently I am taking intern at trading floor. I found most of trader or trader assistants working with Excel for every minute. Therefore that is the reason why VBA is more popular. C++ is only a tool for Quant or Quant developer. Why we need c++? Since the pricing models of most industry-level trading system are developed by C++. Why we choose COM not Dll or Xll? COM is language independent. That means you can develop COM by C++, C#, VB.NET. But no matter which one you choose, they all implement an universal interface. Therefore from client's view, they are same. This is very flexible and convenient. Since I do touch this topic for short while, if you are interested, we can do research together. Thanks

raywin

[alain]

the last time I used COM it was a nightmare. I don't know how it is now since I haven't touched COM since aroun 2000. At that time, it was a half baked idea and a pain in the butt to code using C++ and the ATL.

Also, I thought COM was obsolete because after that there was COM+ and God knows what else. Ah, whatever you mentioned about Language independence, that was only in fantasy land. If it has changed recently, please enlighten me.

[joe_bradley]

Ah Alain, you are mistaken. The original execution, as I saw it, was brilliant. Here's how it worked circa 1998:

1. Contractor with C++ experience but no COM expertise hired to work on code maintenance.
2. Contractor, wishing to accumulate valuable buzzwords for his resume, immediately suggests use of COM at first opportunity. Technical manager A blows off request, tells contractor to work on job at hand.
3. Manager A replaced my manager B in a sloppy office reshuffling. Code with COM checked into source control the second A is gone.
4. Contractor puts COM on resume.
5. Contractor leaves in the middle of his contract for new job requiring COM, paying $10/hr more.

Contractor wins, Microsoft wins, everybody wins!