Use VBA in Office 2007 applications?

Question

Will VBA leave soon, for example, VB6? Should I not develop new Office applications with VBA? Or should I develop all new Office applications with VSTO?

Update: Read this article recently.

Answer 1

Office VSTO offers many additional features for Office VBA, and although I do not think Microsoft has announced that it intends to terminate VBA (in fact, they directly said that it would be at least until Office 14; Office 2007 = Office 12), I think it’s worth the effort to port your applications to VSTO to take advantage of the added flexibility and power.

In fact, I do not think that depreciation of VBA would be feasible, because at the macro level, business users have a fairly large number of Office programs, and I do not think it will be gone soon. These people usually do not have access to the VSTO-enabled IDE.

Answer 2

VSTO has new features, but also has a number of serious drawbacks compared to VBA.

On the one hand, code access protection can make it difficult to deploy VSTO applications (which is polite).

On the other hand, the VSTO development environment is nowhere available for Power User developers like VBA. For example, there is no macro recorder to get you started.

And the big showstopper is that the .NET interaction with COM objects outside the process does not work. For example, if you want to manage other Office applications (Word, PowerPoint, Outlook) from Excel VSTO, you will find several copies of these applications running in the background for the reasons described in this KB article .

All this, combined with a huge investment in existing VBA applications, means that VBA will not go away any time soon.

Answer 3

Microsoft said VBA will be supported , moving forward in the foreseeable future, but they also recommend using new VSTO applications.

The latest Mac version of MS Office does not support VBA, and 64-bit Windows runs it in virtual 32-bit mode outside the process. Therefore, if you are planning a new application using Office as a platform, VSTO is definitely , but you should not worry too much about the old support.

As @cori notes, this would be a great marketing no-no for MS to just maintain support and break so much existing software.

Answer 4

Microsoft is dropping allusions to the version of Office with integrated VSTO (presumably the same as the integrated VB6 environment is integrated for VBA, so the VS IDE will be integrated for VSTO) since .NET was first released.

Considering how important coding is, and considering that this will not lead to any features that will be visible to users, I doubt very much that this is very important on Microsoft's list of priorities. I can imagine that they superimpose a set of managed codes on top of the existing code base (since Joel Spolsky superimposed many COM objects on top of the existing C code base when placing VBA in Excel in the first place) and launched a new IDE as the value for by default, while hiding the old one. Even this would be a major exercise (imagine a macro record!). Of course, this would make .NET pre-req for Office, which the Office team would only accept under the gun.

Of course, they will never remove VBA from the products - Excel still supports Excel 4 macros, and Word still has a WordBasic Automation object to support Word 6 macros, and there is no indication that they are being deleted because there is too much legacy code to support - and no one has used any of these coding models in a decade.

If Microsoft ever puts the .NET environment in Office (which, frankly, I doubt it will ever happen), they may stop adding VBA support for new Office features. That they will be closest to terminating VBA.

Answer 5

Here is a comment from Microsoft regarding future VBA support. In a nutshell, it does not go away in versions of Windows Office (but it stops for Mac versions).

Answer 6

VBA is far from being depreciated; in fact, VBA should be reintroduced into the next version of Office on MAC ( http://www.microsoft.com/presspass/press/2008/may08/05-13MacBU2008PR.mspx ).

For most people on earth, VBA and C XLL (and VB6 !!) are still the tools of choice. Current .NET connections are slow and provide zero performance gains. Part three tools, such as ExcelDNA, ease the pain a bit, but obviously the unmanaged C-based (and assembler-based) code base of Office is not easy to combine with .NET.

Answer 7

VBA add-ons are a bit complicated to deploy, but VSTO even more so. In addition, VSTO involves a bit of overhead, since you need to run the CLR to run the code.

But the most important thing; VSTO takes away the pain of writing VBA.