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

FotoMagico 6.2.2 - Powerful slideshow cr...
FotoMagico lets you create professional slideshows from your photos and music with just a few, simple mouse clicks. It sports a very clean and intuitive yet powerful user interface. High image... Read more
Default Folder X 5.7 - Enhances Open and...
Default Folder X attaches a toolbar to the right side of the Open and Save dialogs in any OS X-native application. The toolbar gives you fast access to various folders and commands. You just click on... Read more
f.lux 42.1 - Adjusts the color of your d...
f.lux makes the color of your computer's display adapt to the time of day, warm at night and like sunlight during the day. Ever notice how people texting at night have that eerie blue glow? Or wake... Read more
Spotify 1.1.94.872 - Stream music, creat...
Spotify is a streaming music service that gives you on-demand access to millions of songs. Whether you like driving rock, silky R&B, or grandiose classical music, Spotify's massive catalogue puts... Read more
Vitamin-R 4.15 - Personal productivity t...
Vitamin-R creates the optimal conditions for your brain to work at its best by structuring your work into short bursts of distraction-free, highly focused activity alternating with opportunities for... Read more
OfficeTime 2.0.628 - Easy time and expen...
OfficeTime is time and expense tracking that is easy, elegant and focused. Other time keepers are clumsy or oversimplified. OfficeTime balances features and ease of use, allowing you to easily track... Read more
Slack 4.28.182 - Collaborative communica...
Slack brings team communication and collaboration into one place so you can get more work done, whether you belong to a large enterprise or a small business. Check off your to-do list and move your... Read more
DEVONthink Pro 3.8.6 - Knowledge base, i...
DEVONthink is DEVONtechnologies' document and information management solution. It supports a large variety of file formats and stores them in a database enhanced by artificial intelligence (AI). Many... Read more
FileMaker Pro 19.5.4 - Quickly build cus...
FileMaker Pro is the tool you use to create a custom app. You also use FileMaker Pro to access your app on a computer. Start by importing data from a spreadsheet or using a built-in Starter app to... Read more
Backblaze 8.5.0.628 - Online backup serv...
Backblaze is an online backup service designed from the ground-up for the Mac. With unlimited storage available for $6 per month, as well as a free 15-day trial, peace of mind is within reach with... Read more

Latest Forum Discussions

See All

‘Oxenfree: Netflix Edition’ Is Out Now o...
Over the weekend, Netflix and Nightschool Studio announced and released Oxenfree: Netflix Edition (Free) worldwide on iOS and Android. This new version of Oxenfree: Netflix Edition is a separate release, and the prior version that I own, is no... | Read more »
‘Genshin Impact’ Version 3.1 Update Pre-...
Genshin Impact (Free) version 3.1 ‘King Deshret and the Three Magi’ goes live in a few days across iOS, Android, PC, PS5, and PS4. As with prior updates, pre-installation for the upcate has just gone live a few days before release. | Read more »
We’re Digging ‘Shovel Knight Dig’ – The...
We spend the bulk of this week’s podcast talking about the new iPhone 14. Specifically, the iPhone 14 Pro Max which both Eli and myself picked up. The consensus seems to be: They’re great! They’re iPhones! We do lay down our hot takes on all the new... | Read more »
TouchArcade Game of the Week: ‘Loose Noz...
There aren’t a lot of stories like that of the development of Loose Nozzles, and of those games that do have an interesting development story, even fewer are actually decent games to play. Loose Nozzles nails both, though. The way it was created is... | Read more »
SwitchArcade Round-Up: ‘Shovel Knight Di...
Hello gentle readers, and welcome to the SwitchArcade Round-Up for September 23rd, 2022. In today’s article, we’ve got the rest of this week’s releases to look at. There are actually a few big games today, including the hot-hot-hot Shovel Knight Dig... | Read more »
‘Gubbins’ is a Way Too Adorable Word Gam...
There are games whose art style, sounds, and overall vibe just make me smile ear to ear. Games like Hidden Folks, Krispee Street, or Tiny Wings. There’s just something so cool about being able to literally feel the heart that goes into a game. Now... | Read more »
Based on the Baking Reality Show, ‘Naile...
Fans of Netflix’s reality baking show Nailed It! have a new holiday-themed season to look forward to next month when Nailed It! Halloween launches on October 5th, but the fun doesn’t stop there because the show is also arriving as a mobile game the... | Read more »
Cookie Run: Kingdom announces collaborat...
In news sure to excite fans of biscuits or K-Pop music, the Korean sensations BTS have teamed up with Cookie Run: Kingdom for a series of events. After some warm-up episodes, the collaboration will culminate in a BTS in-game concert, so if anyone'... | Read more »
‘Shovel Knight Dig’ From Nitrome and Yac...
Shovel Knight Dig () from Nitrome and Yacht Club Games is this week’s new Apple Arcade release. It is definitely one of my favorite additions to the service ever, and a fantastic game overall. I played it a few hours ago when it started rolling out... | Read more »
SwitchArcade Round-Up: ‘Mario Strikers’...
Hello gentle readers, and welcome to the SwitchArcade Round-Up for September 22nd, 2022. Hunh, lots of twos in the date today. Nifty. As those who read yesterday’s article may remember, I got a vaccine shot about twenty four hours ago and it is... | Read more »

Price Scanner via MacPrices.net

New today at Apple: 13-inch M2 MacBook Pros f...
Apple 13″ MacBook Pros with M2 CPUs in stock and available today starting at $1169, Certified Refurbished, and ranging up to $150 off original MSRP. These are the cheapest 13″ M2 MacBook Pros for... Read more
Sunday Sale: 13″ Apple M1 MacBook Air availab...
Amazon has Space Gray Apple 13″ M1 MacBook Airs on sale for $690.95 for an extremely limited time. Other models are on sale for $849. Their price for the Space Gray model is the cheapest we’ve ever... Read more
Use our exclusive Apple Price Trackers to fin...
Our Apple award-winning price trackers are the best place to look for the lowest prices and latest sales on all the latest Apple gear this season. Scan our price trackers for the latest information... Read more
New promo at Verizon: Get Apple Watch Series...
Purchase a new iPhone 14 at Verizon, and get an Apple Watch Series 8 for as low as $5 per month. $120 in promo credits for the Watch are spread over a 36 month term, reducing the price of the Watch... Read more
Visible drops prices on Apple iPhone 13 model...
Verizon’s low-cost wireless cell service, Visible has dropped prices on iPhone 13 models to new low prices starting at $599: – iPhone 13 Pro Max: starting at $980 + free $200 gift card – iPhone 13... Read more
Back in stock! 14″ MacBook Pros with Apple M1...
Amazon has restocked 14″ MacBook Pros M1 Pro CPUs for $400 off MSRP, starting at only $1599. Shipping is free. Be sure to make your purchase from Amazon rather than a third-party seller. Their prices... Read more
This is the final week to take advantage of A...
Apple’s Back to School promotion for 2022 ends on September 26, 2022. As part of this promotion, Apple will include a free $150 Apple Gift Card with the purchase of any MacBook Air, MacBook Pro, or... Read more
Mac Studio with M1 Max CPU back in stock toda...
Apple has the base standard-configuration Mac Studio available again in their Certified Refurbished section for $1799, and it’s in stock today. Each Mac Studio comes with Apple’s one-year warranty,... Read more
Apple MagSafe iPhone battery on sale for $84,...
Amazon has Apple’s MagSafe Battery on sale for $84 today. Shipping is free. That’s $15 off Apple’s MSRP, and it’s the lowest price for one of these MagSafe batteries among the Apple retailers we... Read more
24-inch M1-powered iMacs available today at A...
Apple has a full range of 24-inch M1 iMacs available today in their Certified Refurbished store. Models are available starting at only $1099 and range up to $260 off original MSRP. Each iMac is in... Read more

Jobs Board

Physician Assistant, Primary Care, *Apple*...
Physician Assistant, Primary Care, Apple Valley (1.07FTE) + Job ID: 65766 + Department: AV Primary Care + City: Apple Valley, MN + Location: HP - Apple Read more
Operations Manager - Mac/ *Apple* Engineerin...
…Responsible for the day-to-day activities relating to the engineering of Apple Macs in a complex, multi-platform environment. Demonstrates strong leadership, Read more
Lead Developer - *Apple* tvOS - Rumble (Uni...
…earnings, and positive sentiment About the role: We are looking for a Lead Apple tvOS Developer to join our application engineering team to expand our video centric Read more
Systems Administrator - *Apple* Devices / J...
…Administration **Duties and Responsibilities** + Configure and maintain the client's Apple Device Management (ADM) solution. The current solution is JAMF supporting Read more
Sr Product Manager, *Apple* TV Platforms -...
…an experienced senior product manager to drive the strategy and requirements for our Apple TV devices, acting as the champion and owner of the holistic experience in Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.