Excel VBA vs C++?

Question

Hi there, I am not an IT person. I have asked someone to help me create a VBA add-in function to calculate statistical attributes of time series. The problem is that the process involved a loop calculation, and with a long time series computation becomes quite slow. Also I have hundreds of time series so each row would make a function call and computation would be that much slower again. My question is whether compiling this in C++ would make it markedly faster. What troubles me is that when I calculate this statistical attribute for each row through standard Excel formulae (effectively one for each loop on each row so an awful lot of cells), it is very fast, although the spreadsheet becomes very unwieldy with all these intermediate calculations in additional cells. Many thanks for your help.

Answer 1

VBA is capable of extremely fast computations. It is more likely that your approach for producing these results is very inefficient - there is likely a number of ways to scrape the data from the spreadsheet and perform the calculations much more rapidly if you reevaluate how you are processing it. I have seen many inexperienced programmers use hundreds of thousands of loops and complex variable pushing when a few simple lines of code would have sufficed. Your problem may not be able to be simplified to such an extreme, but I find it highly unlikely that any reasonable business calculation running out of an Excel spreadsheet could become unreasonably slow if designed well.

Answer 2

C++ will be significantly faster than VBA for number crunching. The question you really need to answer is if the complexity of calling out to an external DLL (usually by COM) is worth the speed improvement and increased difficulty in maintaining the application.



If this will be used by many people and the expectation is high that it should return immediately, it will be worth going the C++ path. Conversely, if there's a single user and he can wait a moment or two while the loops run their course, it's probably not worth rewriting in C++.

Answer 3

If you go to my website you will find the Whetstone Benchmark in various programming languages with source code, including Excel/VB, and EXE files from C++ and Visual Basic 4 (All Free).



http://freespace.virgin.net/roy.longbottom/



VB and Excel/VB obtain similar results. A summary of results via different programming languages is at the end of:



http://freespace.virgin.net/roy.longbottom/whetstone.htm



Here you will see that optimised C++ is usually 10 times faster than VB. I assume that these VBs use an Interpreter and not a Compiler. I don’t know about latest VB or Excel from Windows 95 days.