Is Excel VBA best for me?

[joeu2004]

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.

 

[Steve Dalton]

If you are mainly looking to add new worksheet functions to Excel and you
need high performance, you should consider using Excel's C API. It is a
little daunting at first, but as you are at home with C/C++ the only issues
will be the Excel interface. Memory management is also critically
important. I have written a book on the subject and there are plenty of
sources on the internet - just do a search on Excel XLL for example. It is
an old interface and does not support adirect access to all of the Execl
objects, methods, porperties and events that, say, VBA does, but it will be
supported in the next release of Excel (version 12).

Regards

Steve Dalton

 

[Bob Phillips]

You might also want to look at the .Net environment, MS are really pushing
that and as I understand VB.Net and C# should not be totally alien to you.
In these environments, with Excel 2003 On, you should be able to do anything
that you want with the Excel object model, so manipulating workbooks will
not be a problem.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[John Coleman]

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