Compile VBA for faster execution

**batman** · 03-13-2010, 05:33 PM

VBA offers marvelous functionality, but being an interpreted language, it is very slow compared to worksheet functions. I would like to compile all or most of the subroutines in my application so they run much faster, hopefully at least 10 times faster. I believe that you have to first convert the VBA code to another language, such as VB or C++, and then use a compiler for that language to create a dll or xll, which can then be referenced in the Excel file.

I have been searching the Internet for products that would do this work for me. So far I haven't found much; perhaps I'm not searching the right way. Can anyone suggest specific software for this purpose?

Some of the products I've seen will also compile the worksheets as well as the VBA code, with the intent of hiding proprietary formulas and/or transforming the entire application into a .exe. That's nice, but I don't need that, at east not yet. I just want to make the VBA code run much faster without losing any functionality.

Would appreciate any recommendations and words of wisdom.

**dominicb** · 03-13-2010, 06:23 PM

Good evening batman

What your actually referring to is known as a COM Add-In. A COM Add-in will run considerably faster than VBA and will offer increased security in terms of people breaking into / reverse engineering your work in any way.

You can only do this with add-ins. You can't just convert any existing modules in a workbook. You are right that your code will need converting, but there is no application that will do this for you. It will need rewriting again from the start. And you will need to learn the language which you want to use before you do it. Using Visual Basic (as it is now called, as opposed to VB6 or VB.Net) would probably be easier (if you don't already know C++) as the syntax is identical, but there are many extra objects / instructions to come to terms with.

I would suggest you have a look here which is where you can download free 2008 Express versions of the Visual Studio Tools (Visual Basic, Visual C#, Visual C++).

You might also like to have a look at Chip Pearson's introduction to the subject here.

HTH

DominicB

**broro183** · 03-13-2010, 06:48 PM

hi,

Edit: Ooopps I was too slow!
At least DominicB's answer is more knowledgeable :-)

VBA's the only language I know (so far) so I can't help with your specific question but I (or others) may be able to help you achieve your aim of faster execution through review of your code - are you able to upload any for us to review?

For tips on good VBA coding have a read of http://www.excelforum.com/excel-prog...id-in-vba.html

If you're already using all the best practices & still want more speed then hopefully someone else can answer your question...

I'm curious though so I went looking & a quick Google search shows:
http://support.microsoft.com/kb/306130
http://www.xtremevbtalk.com/archive/.../t-264506.html (discusses MS Access but I think the links listed would apply for Excel too as it is all using VBA)
http://www.xtremevbtalk.com/showthre...967#post619967 (some knowledgeable discussion)

hth
Rob

**batman** · 03-13-2010, 11:14 PM

Thanks dominicb

I looked at CPearson's material as you suggested. There's a lot there to digest. I have VB experience, although not recent (I think I got up to VB4). I also have an Express version of Visual Studio, although I have done nothing with it yet. I may have to start getting my feet wet, very wet.