Best Excel Development Architecture? VSTO vs. VBA vs. VB.Net/C#.Netvs. ???

[Scott Bass]

Hi,

First of all, I'm an experienced programmer in a few languages,
including C#, but have not yet learned VBA or VB.Net beyond
rudimentary code.

However, my new employer is a heavy user of Excel, and it would
behoove me to learn to maximize its capabilities.

I've read a number of web articles indicating that VSTO is the
successor to VBA. However, I've also read it does not work with
Visual Studio Express. Is this still the case with VS Express 2010?
If so, then Microsoft wants me to use VSTO in favour of VBA, yet wants
me to spend $$$ for the high end VS Suite? I don't understand this
marketing - I would think it would limit the uptake of VSTO? There is
no way I will get justification to buy VS just to use VSTO vs. VBA.

I've also read here:
http://groups.google.com/group/micr...2?lnk=gst&q=vsto+and+express#0a29c30ee38f2b52
that VBA can be used to call a .dll.

Given the above, what would you recommend as the best architecture for
me to learn to automate Excel's capabilities?

Thanks,
Scott

 

[GS]

I would suggest you start programming directly within Excel's VBA IDE.
This will give you more speedy results, allow you to use the macro
recorder to learn how its objects, methods, properties, and collections
work, AND you can test code on the fly more easily/readily during
development.

As far as your assertion that VSTO is M$'s replacement for VBA goes,
that's never going to happen! VBA has been upgraded for x64 and so I
expect they plan to continue with it as the default macro language for
M$O apps.

If you want some good reference material for serious application
development in the Excel platform then check these out:

http://www.appspro.com/Books/Books.htm

In any case, you'll have to learn Excel's object model if you expect to
achieve any competent level of automation skill as your employer
expects. Once you're familiar with developing directly in VBA then
maybe you'll want to resort to automating via some other language.

DLLs are a good idea (in any language). VBA projects support their use
via References to registered COM objects/libraries.

A word of advice regarding x64 versions and x32 versions; you need to
develop x64 VBA in Excel x64 edition, and x32 VBA in Excel x32 edition.
In the latter case, always develop in the earliest version you expect
your users will use your project with.

HTH