VSTO and VB Express

[agibsonsw]

Hello. VB 2010 Express.
I haven't yet found a clear answer, but is it possible to use VSTO to automate (or get data from) Excel from within Visual Basic 2010 Express? If so is there a download link available for VSTO?

Could someone describe in brief the difference between using VSTO and VBA for Office automation? Andy.

[HansV]

VSTO uses VB.Net while VBA uses, er, VBA.

According to Configuring a Computer to Develop Office Solutions, you need Visual Studio 2010 Professional, Premium or Ultimate to be able to include the Microsoft Office Developer Tools, i.e. not for Visual Basic Express.

[agibsonsw]

@Hans Thank you.

I might not need VSTO. A company use VB6 and Excel/VBA for their financial model (Monte Carlo, etc.). They want to look at VB.Net and VBA - they haven't mentioned VSTO. So, if I'm smart , I won't mention these letters in combination .

But seriously.. I believe it will be easier for them (but still challenging) to migrate their existing solution from VB6 to .Net, without trying to convert VBA code to VSTO (and learn it!) at the same time.

Based on my current understanding of VSTO, and the above requirement, I don't believe it would be essential for them at this stage. Any performance benefit (which is debatable and, I believe(?), depends on the type of interaction with Excel that occurs) would be negated by the complexity involved in trying to learn VSTO and translate their current VBA solution.

I would appreciate your comment(s) on the above? I could consider showing some code in both VSTO and VBA - then they might recognise the challenge that it presents. Andy.

[HansV]

I'm old-fashioned: I prefer to remain with VBA. It is the "native" programming language of the Office applications.
The demise of VBA was announced in the second half of the 1990's, just like that of DAO. It's now almost 15 years later, and VBA and DAO are still going strong; they will be in the next version of Office...

[agibsonsw]

Q. Yes, there seems to be a lot of debate about VSTO (and MS's pricing policy).

Regards, Andy.

[Jan Karel Pieterse]

It is not easier to migrate a VB6 solution to VB.NET than to migrate a VBA solution to VB.NET. VBA and VB6 share the same syntax, VB.NET is significantly different.

A VB.NET solution is guaranteed to be worse in performance than the VBA or VB6 solution, because the .NET solution "talks" to Excel through a slower interface.
How big the performance hit is depends on how much interacting with Excel is involved.

A VB6 solution will become obsolete in time, as Office 64 bit cannot run VB6 addins. Right now, Office 2010 64 bit installations are a tiny minority, but you can expect the percentage to grow with the upcoming new releases of Office.

VSTO is an add-on to VB.NET which makes creating Office solutions using VB.NET easier. It helps with all the plumbing needed to make your .NET solution with with Office. Some examples (not having used VSTO a lot myslef, so there is a lot more):
Task pane editor to create your own custom task panes
Ribbon editor
Wizard to create installers.

Just my 2 cts.

[agibsonsw]

Hello Jan and thank you.

I believe they intend to migrate their current solution because they are aware that VB6 will become obsolete, but I shall advise them that they won't see a performance boost.

I know that VB6 and VBA are the same. They will face the challenge of converting VB6 to VB.Net but the VBA, which is embedded within the VB6 (to automate Excel), can essentially be carried across to VB.Net, I believe(?). I'm thinking that once they've achieved this migration they could then consider the (future) benefits of converting the VBA to VSTO - and the time and effort/challenge that this would involve.

Regards, Andy.

[Jan Karel Pieterse]

The syntax of VB is much like VB6 and VBA, with some crucial differences. You can get away with using most of the VB6 syntax you are used to using in VB.NET, but it isn't the recommended way as far as I know (think of error handling).
There is NO VBA in VB6, even if you're addressing Excel. It remains VB6 using the Excel object model. You can do the same using C++. Point being that you have to program in the VB6 syntax, even if you're addressing Excel. Because VB6 and VBA are mostly the same, it appears to be containing Excel VBA, but it does not, it is VB6 code doing stuff with the Excel Object model.

[HansV]

I admit in advance that the following is rather pedantic, but I have it on authority of a Microsoft employee.
VBA is the programming language used both in VB6 and in the VBE in the Office applications.
VB6 offers a development environment that lets you create stand-alone applications written in VBA.
The VBE in Office applications lets you create code to be run within those applications.
Each Office application has its own object library in addition to the core VBA library.
VB6 can also use these object libraries.

[agibsonsw]

Thanks both

I've been advised that they brought in some 'genius' who has already converted their VB6 (or VBA - take your pick ) model to VB.Net.

They want to gain familiarity with VB.Net and translate some of their VBA skills across. This is not such an issue but I'll need to make sure that we agree on the precise content to cover. What's the betting that some awkward *** will ask me about CLR, XAML, Assemblies, VSTO or LINQ - and expect a comprehensive answer at the drop of a hat

Perhaps I should create a "prepared statement" (and maybe a very short example) for some of these topics - wikipedia anyone? And, of course, I shouldn't mention these topics myself . (Although, I should mention VSTO towards the end of the session.)

Regards, Andy.