Volume Number: 24 (2008)
Issue Number: 07
Column Tag: Scripting

VBA's Coming Back. Is it Right for You?

VBA coming back is great, but it's not here yet, and even so, it may not be your best solution.

by Neil Ticktin, Editor-in-Chief/Publisher

First off, if you don't know what VBA, scripting, or other types of automation are, this article is probably not useful for you to choose your path with Microsoft Office. In all likelihood, in your decision to upgrade or not to Microsoft Office 2008, you should look at other criteria such as features, benchmarks, compatibility, new file types, etc... to make your decision. For example, you may want to look at MacTech's benchmarks for Office 2008 (http://www.mactech.com/articles/mactech/Vol.24/24.03/Office2008Benchmark/)

When you are ready for it, you may want to look at scripting Office. It's a great way to automate things that you do often, improve your work flows, and be more efficient on the types of things that you do all the time.

For those you that know what VBA, AppleScript and/or scripting are about, you'll want to read on to determine which path makes the most sense for you. And, if you are interested in converting from VBA to AppleScript, MacTech has the definitive resource on making that transition. See MacTech's Microsoft Office VBA to AppleScript Transition Guide.

Whether you are interested in scripting or not, you should first decide whether the new features or other benefits of Office 2008 for Mac are important to you. You'll want to have that in mind as you look at your scripting options.

Sometimes, even when you want to do something, they are just plain hard and you can't. That's the situation that Microsoft faced when they realized there wasn't enough time to ship Office 2008 for Mac, and still have it include VBA.

Office 2008 was a monstrous task — or better said — a monstrous three tasks to take on. First, the Office code base needed to be moved from Metrowerks CodeWarrior to Apple's Xcode (these are the development environments that they create the programs in). Second, Microsoft needed to make Office 2008 "universal" (i.e., make it run natively on both Intel and PowerPC Macs). And, finally, they needed to complete all the new features, new file formats, and Office 2007 compatibility requirements (the type of things that they do on every Office upgrade).

Each and every one of these things was a big task in and of itself, and unlike previous major versions, Microsoft had to do all three at once.

So, when it came down to the decision of whether to included VBA or not in Office 2008, the answer was clear. They could not support VBA, or they could delay shipping Office 2008 for a long while (possibly as much as a year or two), but Microsoft would not be able to ship Office 2008 in a reasonable time frame and support VBA in that version.

MacTech belives that less than 10% (probably less than 5%) of Office users use VBA. Assuming this is correct, the VBA decision was an easy one. It had to be left out.

Don't get us wrong, VBA is important in Enterprise settings — and there's a whole lot of Office users in the Enterprise setting. Furthermore, VBA is the cross platform solution used by a lot of people even outside the large corporate setting. And, while AppleScript is better for many solutions, it's not cross platform.

As you've now probably heard, Microsoft has delivered some good news for VBAers ... VBA is coming back. But, before you do cartwheels in the hallway, realize that it's not going to be tomorrow. It's still a ways off.

As we said before, it's clear that Microsoft could not make any decision other than dropping VBA for Office 2008. A huge delay would have impacted a great deal more people, and was a far greater pain. But, if nothing else, Microsoft has proven to be good at listening and evolving. They could hear the pain that people felt missing VBA, and they've been looking at the possibility of implementing VBA in the next major release of Office. (For those of you that don't read tea leaves, Microsoft has been very clear over the past year that they do not want to wait four years for between major releases, as was the case between Office 2004 and Office 2008. Prior to that version, typically versions were 2 to 3 years apart.)

Microsoft understands the importance of providing a road map early on, even though we're not particularly used to it in the Mac market. Now that they've made the decision, they are letting the community know well in advance so you can plan accordingly.

Knowing that VBA is coming back has expanded the choices that you have in dealing with scripting Office. We're going to focus on how you can zero in on what is the best choice for you for today's use.

For Office on the Mac, you have two basic choices: AppleScript and VBA. AppleScript support in Office 2008 is beefed up quite a bit from 2004. And, VBA is only available in Office 2004, and will come back some point in the future ... in the next major release of Office for Mac.

Both AppleScript and VBA have their strengths and weaknesses. VBA is cross platform, but is limited to just scripting Office applications, and does not include Entourage. AppleScript allows you to control Mac OS X as well as other Mac applications, is considered to be easier to learn, but is not cross platform.

Some are served best by staying with VBA in Office 2004. Others are better off rewriting their scripts in AppleScript. And, still others are better off using Office 2007 for Windows under a virtualization product like VMware Fusion or Parallels (or even booting into Windows with Bootcamp).

One thing you need to assess is the level of VBA compatibility that you may need. For most people, Office 2004 VBA was more than enough compatible with the version of VBA in recent versions of Office for Windows. Some, however, may require a greater level of compatibility in order to access new features in the Windows version of Office. But, generally, those people are experts in VBA usage, and know exactly what they need. If you aren't sure about your needs, chances are the level of compatibility in Office 2004 will suit you just fine.

With the announcement that VBA is coming back, you need to balance the question of re-writing scripts in AppleScript, or waiting it out with a temporary solution.

The easiest way to figure this out is MacTech's "Office Scripting Advisor" flow chart which asks you questions to hone in on the right solution for you. (See Figure 1.)

Figure 1: MacTech Office Scripting Advisor

One of the effects by VBA not being in Office 2008 is support for Independent Software Vendors (ISVs) that work with Office. These tend to be solutions for specifically targeted or niche markets, but are important nonetheless.

Some of these third party products are based on VBA, while others are based on the plug-in architecture. Microsoft has been looking at those solutions that worked before, but have issues with Office 2008. In some of the most important cases, there are solutions coming.

One common example is Solver (an add-in for Excel) which is used in a number of academic and other settings. Those who upgraded to Excel 2008 found that Solver was no longer supported. As of right now, there's no definitive answer on how this will be play out, but we can tell you that Microsoft is working to resolve this, and similar issues.

For those third parties basing their products on Office, there are two solutions. If you need 2008 support today, you may want to use AppleScript to control Office in your product. In the future, you'll have both VBA and AppleScript, and your choice will become which scripting language suits your needs best. And, you should keep an eye on what Microsoft does with their SDK offerings for the products you want to support.

Now that you have an understanding of what the future holds for Office scripting, you can choose the right solution based on your needs. Today, you have a number of options. In the future, you'll have an optimal set of choices that will suit you even better.

In the end, if you did not find some specific reason to the contrary while reading this article, then you likely fall into one of two groups:

• Office 2004 does what you need.
• Office 2008 with AppleScript does what you need.
• A combination approach of using both Office 2004 (for VBA usage) and Office 2008 for other usage works best for you.

Whatever your situation, you now have a layout of what scripting Office looks like today, and tomorrow.

Neil is the Editor-in-Chief and Publisher of MacTech Magazine. Neil has been in the Mac industry since 1985, has developed software, written documentation, been heading up the magazine since 1992. When Neil writes a review, he likes to put solutions into a real-life scenario and then write about that experience from the user point of view. That said, Neil has a reputation around the office for pushing software to its limits and crashing software/finding bugs. Drop him a line at publisher@mactech.com.