ExcelBanter - View Single Post

John Coleman

wrote:
I am a Unix systems programmer with 20 years of
experience writing programs in C (not C++). Now I am
retired and searching for a programming environment
primarily to write simulations for financial analysis. I
expect to integrate the simulations into Excel spreadsheets.

My question is: what are my alternatives, and what do
you think I would be happiest with, based on my background?

Excel VBA seems like an "obvious choice", based on those
requirements. But I wonder: will I be happy with the
performance and programming language?

Also I wonder: do I want to use VB by itself? Is that even
an option? Alternatively, is it easy to use C/C++ on
Windows and import the data into Excel?

I would welcome feedback from people ideally with
experience with both Excel VBA and other programming
environments, notably Unix/Linux and C/C++, who can
offer some comparative data.

Also, pointers to specific objective magazine articles on
the subject would be helpful.

TIA.

Greetings,
I have used both C and Excel VBA to experiment with genetic
algorithms, mostly VBA. I have found VBA much easier to program and
debug, and it is *very* convenient to have the spreadsheet there to do
things like dump statistics for each generation and generate charts on
the fly. The speed is acceptable for many purposes. As a ballpark
figure Excel VBA will execute 5-10 million instructions per second (on
a 1.5 gh machine). This makes it much closer to pure VB (compiled, but
not exactly quick) than to VBScript (purely interpreted), so the
partial compilation into p-code that VBA does gives you a speed boost
compared to a purely interpreted language. On the other hand - there
is no question that C (or C++) will absolutely blow VBA out of the
water in sheer speed. Some of the things I have done with genetic
algorithms in C would have required days of computer time with VBA.

As a rough rule of thumb - if your typical simulation involves less
than a billion elementary steps/computations, than VBA is attractive
for ease of programming, charting etc, but if you want to do really
heavy-duty simulations you should probably stick to C/C++. If you want
easy interaction with Excel in a Windows environment maybe you could
look into C# or managed C++ in Visual Studio.Net (I am not quite sure
how this version of C++ compares with the older visual C++ in speed,
but it will clearly be much better than VBA).

Hope that helps

-John Coleman