TweetFollow Us on Twitter

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

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

Should You Read This Article?

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.

Why VBA Went Away

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.

VBA is coming back, but it's not tomorrow

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.

Your Choices

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

ISV Support, Excel Solver and Other Add-Ins

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.

What To Do

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.

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

Latest Forum Discussions

See All

Whitethorn Games combines two completely...
If you have ever gone fishing then you know that it is a lesson in patience, sitting around waiting for a bite that may never come. Well, that's because you have been doing it wrong, since as Whitehorn Games now demonstrates in new release Skate... | Read more »
Call of Duty Warzone is a Waiting Simula...
It's always fun when a splashy multiplayer game comes to mobile because they are few and far between, so I was excited to see the notification about Call of Duty: Warzone Mobile (finally) launching last week and wanted to try it out. As someone who... | Read more »
Albion Online introduces some massive ne...
Sandbox Interactive has announced an upcoming update to its flagship MMORPG Albion Online, containing massive updates to its existing guild Vs guild systems. Someone clearly rewatched the Helms Deep battle in Lord of the Rings and spent the next... | Read more »
Chucklefish announces launch date of the...
Chucklefish, the indie London-based team we probably all know from developing Terraria or their stint publishing Stardew Valley, has revealed the mobile release date for roguelike deck-builder Wildfrost. Developed by Gaziter and Deadpan Games, the... | Read more »
Netmarble opens pre-registration for act...
It has been close to three years since Netmarble announced they would be adapting the smash series Solo Leveling into a video game, and at last, they have announced the opening of pre-orders for Solo Leveling: Arise. [Read more] | Read more »
PUBG Mobile celebrates sixth anniversary...
For the past six years, PUBG Mobile has been one of the most popular shooters you can play in the palm of your hand, and Krafton is celebrating this milestone and many years of ups by teaming up with hit music man JVKE to create a special song for... | Read more »
ASTRA: Knights of Veda refuse to pump th...
In perhaps the most recent example of being incredibly eager, ASTRA: Knights of Veda has dropped its second collaboration with South Korean boyband Seventeen, named so as it consists of exactly thirteen members and a video collaboration with Lee... | Read more »
Collect all your cats and caterpillars a...
If you are growing tired of trying to build a town with your phone by using it as a tiny, ineffectual shover then fear no longer, as Independent Arts Software has announced the upcoming release of Construction Simulator 4, from the critically... | Read more »
Backbone complete its lineup of 2nd Gene...
With all the ports of big AAA games that have been coming to mobile, it is becoming more convenient than ever to own a good controller, and to help with this Backbone has announced the completion of their 2nd generation product lineup with their... | Read more »
Zenless Zone Zero opens entries for its...
miHoYo, aka HoYoverse, has become such a big name in mobile gaming that it's hard to believe that arguably their flagship title, Genshin Impact, is only three and a half years old. Now, they continue the road to the next title in their world, with... | Read more »

Price Scanner via MacPrices.net

B&H has Apple’s 13-inch M2 MacBook Airs o...
B&H Photo has 13″ MacBook Airs with M2 CPUs and 256GB of storage in stock and on sale for up to $150 off Apple’s new MSRP, starting at only $849. Free 1-2 day delivery is available to most US... Read more
M2 Mac minis on sale for $100-$200 off MSRP,...
B&H Photo has Apple’s M2-powered Mac minis back in stock and on sale today for $100-$200 off MSRP. Free 1-2 day shipping is available for most US addresses: – Mac mini M2/256GB SSD: $499, save $... Read more
Mac Studios with M2 Max and M2 Ultra CPUs on...
B&H Photo has standard-configuration Mac Studios with Apple’s M2 Max & Ultra CPUs in stock today and on Easter sale for $200 off MSRP. Their prices are the lowest available for these models... Read more
Deal Alert! B&H Photo has Apple’s 14-inch...
B&H Photo has new Gray and Black 14″ M3, M3 Pro, and M3 Max MacBook Pros on sale for $200-$300 off MSRP, starting at only $1399. B&H offers free 1-2 day delivery to most US addresses: – 14″ 8... Read more
Department Of Justice Sets Sights On Apple In...
NEWS – The ball has finally dropped on the big Apple. The ball (metaphorically speaking) — an antitrust lawsuit filed in the U.S. on March 21 by the Department of Justice (DOJ) — came down following... Read more
New 13-inch M3 MacBook Air on sale for $999,...
Amazon has Apple’s new 13″ M3 MacBook Air on sale for $100 off MSRP for the first time, now just $999 shipped. Shipping is free: – 13″ MacBook Air (8GB RAM/256GB SSD/Space Gray): $999 $100 off MSRP... Read more
Amazon has Apple’s 9th-generation WiFi iPads...
Amazon has Apple’s 9th generation 10.2″ WiFi iPads on sale for $80-$100 off MSRP, starting only $249. Their prices are the lowest available for new iPads anywhere: – 10″ 64GB WiFi iPad (Space Gray or... Read more
Discounted 14-inch M3 MacBook Pros with 16GB...
Apple retailer Expercom has 14″ MacBook Pros with M3 CPUs and 16GB of standard memory discounted by up to $120 off Apple’s MSRP: – 14″ M3 MacBook Pro (16GB RAM/256GB SSD): $1691.06 $108 off MSRP – 14... Read more
Clearance 15-inch M2 MacBook Airs on sale for...
B&H Photo has Apple’s 15″ MacBook Airs with M2 CPUs (8GB RAM/256GB SSD) in stock today and on clearance sale for $999 in all four colors. Free 1-2 delivery is available to most US addresses.... Read more
Clearance 13-inch M1 MacBook Airs drop to onl...
B&H has Apple’s base 13″ M1 MacBook Air (Space Gray, Silver, & Gold) in stock and on clearance sale today for $300 off MSRP, only $699. Free 1-2 day shipping is available to most addresses in... Read more

Jobs Board

Medical Assistant - Surgical Oncology- *Apple...
Medical Assistant - Surgical Oncology- Apple Hill Location: WellSpan Medical Group, York, PA Schedule: Full Time Sign-On Bonus Eligible Remote/Hybrid Regular Apply Read more
Omnichannel Associate - *Apple* Blossom Mal...
Omnichannel Associate - Apple Blossom Mall Location:Winchester, VA, United States (https://jobs.jcp.com/jobs/location/191170/winchester-va-united-states) - Apple Read more
Cashier - *Apple* Blossom Mall - JCPenney (...
Cashier - Apple Blossom Mall Location:Winchester, VA, United States (https://jobs.jcp.com/jobs/location/191170/winchester-va-united-states) - Apple Blossom Mall Read more
Operations Associate - *Apple* Blossom Mall...
Operations Associate - Apple Blossom Mall Location:Winchester, VA, United States (https://jobs.jcp.com/jobs/location/191170/winchester-va-united-states) - Apple Read more
Business Analyst | *Apple* Pay - Banco Popu...
Business Analyst | Apple PayApply now " Apply now + Apply Now + Start applying with LinkedIn Start + Please wait Date:Mar 19, 2024 Location: San Juan-Cupey, PR Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.