MacTech’s Mac Office VBA to AppleScript Transition Guide


Mac natives inside and outside of MacBU have provided their efforts and expertise to produce a guidebook to help you navigate the transition from VBA to AppleScript. That’s right, MacTech magazine’s April edition will be a massive 150 page blockbuster entitled “Moving from Microsoft Office VBA to AppleScript: MacTech’s Guide to Making the Transition” and is authored by none other than Mac Office MVP and all around great person (so I’m told) Paul Berkowitz. Paul and many of the other Mac Office MVPs work very hard on AppleScript and lots of other important Office topics on behalf of all of us (check out the MVP page to learn more about this group of experts). MacTech – along with Paul and a cast of editors and reviewers whose combined experience with AppleScript and VBA boggle the mind – have stepped up big time with this book.

MacTech subscribers receive this generous tome as their April edition. Not yet a subscriber but want the guide? It’s a great time to subscribe and, if you act now (before April 2), you might even be able to get a complimentary subscription. Or, support your local newsstand. MacTech magazine can be found in the mac tech section.

Comments (21)

  1. Eric says:

    Does this take into account those people that need have VB Macros function in a heterogeneous environment with other people using Excel for Windows???   If not, then we need to have a transparent system to translate VB Macros for Excel (and possibly Word) for Windows spreadsheets and documents into AppleScript and visa versa.

  2. Josh says:

    I agree!  I’ve come across several programs written in VBA for Excel that would make much of the time consuming parts of my research easier and faster.  Unfortunately, I must either purchase a second Office lisence to run on my VM, or use someone elses machine.  I’ve actually taken to writting short perl scripts to perform some of the tasks that are easier to automate.  Some sort of *REAL* cross-platform support for macros would be a huge time saver.

  3. Joe says:

    I cannot understand why so many companies out there are able to provide complete cross platform compatibility with thier products, but the worlds largest software company cannot.

    I remember at MacWorld when Microsoft stood up and introduced Office 98 and apologized for earlier versions (specificailly 6 and 7 that used an identical code base as Windows, but used shims for the Macintosh interface).  Everyone applauded and cheered.  Perhaps they did so not fully understanding what the future would bring.

    By not using the same core code base, the Macintosh version has seen features removed with each progressive version and Windows features either slowly implemented or never at all.  This should not really be surprising given the amount of work required under this model.

  4. Mr Lizard says:

    NeoOffice for Mac has announced they’ll be supported VB Macros.

    If an open-source company can do it, why can’t MS?

    I’ve read the infamous blog post about how long it would take. Fair enough. But how come it hasn’t taken NeoOffice that long?

  5. patrick says:

    we had a better plan than this transition, we just got rid of the macs and replaced them with windows compatible computers.

    It makes sense, we got the word that macs weren’t really first class citizens on our network anyways, and for complete seemless integration, all of our departments were better served with windows compatible computers. With the staff that weren’t able to make the transition to windows because of lack of compatible skills, we made a nice severance package available to them.

  6. Mike says:

    Remember. Microsoft business model is to put the company’s OS on every PC. Don’t forget that.

    By breaking Microsoft Office for Mac, users that require a heterogenious environment will either have to purchase a PC that runs Microsoft’s OS or they will have to purchase the OS to run on their Macs. It’s a win win situation for Microsoft in either case.

  7. somemacuser says:

    I need VBA script for my spreadsheet!!!

  8. I’ve programmed for more than 20 years, across multiple OS’s and various hardware platforms. Porting can be tricky, and it may be hard, but it is not impossible.  So, I cannot believe that the decision to eliminate VBA in Mac Office was based on technical issues.

    I do believe that MS is intentionally making Office in-compatible, or at least limited to Windows OS – as has been done before with other applications (MS Access, MS Project, MS Visio to name 3.

  9. Stephen says:

    I too am an Excell VBA user and concerned about loss of compatability, however I understood that VBA was being dropped in future versions of Windows Office as well… I await the future with interest.

  10. <I>I’ve programmed for more than 20 years, across multiple OS’s and various hardware platforms. Porting can be tricky, and it may be hard, but it is not impossible.  So, I cannot believe that the decision to eliminate VBA in Mac Office was based on technical issues.</I>

    Well, so has Rick Schaut.

    http://blogs.msdn.com/rick_schaut/archive/2006/08/09/693499.aspx

    Read it, and Eric Schwiebert’s companion piece:

    http://www.schwieb.com/blog/2006/08/08/saying-goodbye-to-visual-basic/

    And then you tell me why they are full of crap and lying to us (because that is essentially what you are accusing them of, since you do not believe there is a valid technical reason). As someone who’s also worked in this field for an extended period of time who also has experience in cross-platform issues, I don’t see any reason to doubt what they are saying.

    The fact of the matter is that VBA wasn’t designed to handle massive changes in underlying architecture and be portable, the way Java and NeXTStep were. That’s unfortunate. In my opinion, the way VB comes back to the Mac platform is through VB.NET runtimes when WPF/E is mature enough to handle it (if the MBU chooses to go that way-  they may not consider it a high priority if AppleScript suffices).

  11. Joe says:

    eponymous

    I have read what was written and understand their complications, HOWEVER, I also have several questions and comments to which now one addressed in  Eric Schwiebert’s Blog. Since OS X 10.1 was released it has been planned that Coco was Apple’s prefered Framework going forward. IF MS had planned on continuing Office for the Mac in the long term I would have thought they would have had a small team of developers working on porting Office from Carbon to Coco slowly. If they had been working on it since the release of Office X it is quite possible that they would be able to have released Office 2007 WITH VBA and Coco support.

    As I also pointed out MS claims to have the largest Mac Develompent team outside of Apple. What are they working on if they are that big? Where is Visio for the Mac? Where is Access? Where is a full outright Exchange Client? (Yes I have read the comments regarding the Windows dependencies of many of the features BUT Apple made Quicktime work in Windows I would hope that MS has the talent to port the required libraries to Mac OS X)  

    In fact since Office 98 they have taken away at least one feature that was quite useful that was opening Word Docs with embedded visio documets so you could at least view and print them.

    While Apple Script is a great technology and it is great to see MS fully implement it in the coming version of Office BUT it doesn’t solve the problem where Office is used in a Corporate environment that supports Macs and PCs that have corporate Word, Excel and PowerPoint templates with VBA scripts embedded.

    At this point I would envision Apple porting AppleScript to Windows before you will see Microsoft make a truely file for file compatible version of Office for the Mac.

  12. Hansel says:

    “IF MS had planned on continuing Office for the Mac in the long term I would have thought they would have had a small team of developers working on porting Office from Carbon to Coco slowly”

    I don’t think you understand Carbon and Cocoa very well. Cocoa is not the magic framework you think it is to just fix every problem you see in Office.

    Office is a very mature product, which means its got tons and tons of code which I’m assuming to be C++ or C. Cocoa works extremely well with Objective-C whilst Carbon works with C++. It makes absolutely no sense to throw away all that code unless there is a VERY good benefit of going Cocoa.

    The ‘Cocoaification’ of Office will bring in very little benefit to Office. Instead, you’ll be looking at a 2010 release of Office 2004 done in Cocoa. AFAIK most of the cocoa goodies can be accessed via Carbon. Carbon is not some second class framework 🙂

    cheers…

  13. dennis says:

    Gee … this is all very nice, but I’m still waiting for those Office 2007 translators that were coming "free and fast" back on Dec. 5, 2006.

    I guess if I had to sit and watch Windows boot every day, I might think five months was "fast", but …

  14. Joe says:

    Yes I understand what a framework is and that there would be pain involved, however, as you state there is a "lot of legacy code" some of which may be unecessary or inefficient on today’s systems. Sometimes you need to refactor how you do things to make them more efficient and/or take advantage of more features. I suspect moving beyond Lepord Apple will continue to aggressively evolve the Cocoa frameworks and Carbon hooks will continue to be updated to a point but it appears to me that Cocoa is the future and Carbon is the status quo. Most recently it has been rumored that Apple is gutting portions of Quicktime (as I understand the blue print to getting Carbon into OS X to begin with) to make them more accessible and feature rich to Cocoa frameworks. I also suspect that the new "Core" managers will be very easy to implement and make use of in a Cocoa based application then Carbon. What IF Apple were to change architectures again, say Cell or Itanium  or Sparc (all of which are highly unlikely but worth an IF) I suspect that Cocoa authors would have a much easier time migrating to that New architecture via Apple’s intrinisic tools and the Cocoa framework then Carbon framework based applications.

  15. “If they had been working on it since the release of Office X it is quite possible that they would be able to have released Office 2007 WITH VBA and Coco support.”

    It’s also quite possible that they could have invented a process for cold fusion or transmuted lead into gold, if they had tried. We won’t know, because they thought this was the best way to deliver software.

    Why do people persist in assuming people in the MBU (and Adobe, while we’re at it, since they DID release new apps in Cocoa like Lightroom, but not legacy apps like Photoshop or InDesign CS3) are completely incompetent and don’t understand how to write their own software- when the folks at the MBU are the ones who actually have access to the source code and you don’t? Have you ever participated in a project where there’s millions of lines of source that rivals the complexity of an operating system? Why do you think they’d ignore simple answers that are so obvious to you?

    For the record, I HAVE participated in cross-platform development. It isn’t as easy as you think, esepcially when you are dealing with 20 year old code that made valid assumptions back in the day of 68030 processors and 4 MB RAM being a top of the line machine, but needs to be dealt with differently now. Perhaps we should trust that Apple is not the center of the computing universe, and some Adobe and Microsoft employees have some domain knowledge about their projects as well, and might have valid reasons for their choices.

  16. Joe says:

    My comments have nothing to do with the compentency of the MacBU, rather more to do with the business practices of Microsoft. IF Mac Office was in their long term strategy (assuming 2 or 3 releases from Office X) then shouldn’t they have had teams evaluate and look to migration over time to the new frameworks and runtime environment? (Very much like Apple had been doing with OS X on intel OR Intel was doing with the Core platform?) I am sorry the whole VBA engine is something that should have been addressed a while ago, especially if there was a long term strategy for Mac Office. If you read these posts you will see there are quite a large number of people who will not be upgrading to this new version because they view VBA compatibility between the Windows and Mac world to be the keystone in the product. People have also been clamoring for Access and a full Exchange experience, yet MS seems to ignore these requests (yet they have no problem loosing money on Xbox, Zune, MSN, MSNBC etc).  

  17. <I>IF Mac Office was in their long term strategy (assuming 2 or 3 releases from Office X) then shouldn’t they have had teams evaluate and look to migration over time to the new frameworks and runtime environment? </I>

    Maybe because Steve TOLD them “Carbon is for existing Mac apps; Cocoa is for new Mac apps”, and they found that to be true when they looked at it?

    Keep in mind that it’s not just Microsoft that releases their apps in Carbon: Adobe also does their Creative Suite in Carbon, too. So is Adobe part of this conspiracy, too?

    <I>If you read these posts you will see there are quite a large number of people who will not be upgrading to this new version because they view VBA compatibility between the Windows and Mac world to be the keystone in the product.</I>

    It’s unfortunate that almost 20 years ago, the folks responsible for VB implementation on the Mac didn’t realize the MacOS would be ported from 68K to PowerPC to Intel, and that Metrowerks would go out of the compiler business, so they implemented VB on the Mac in a way that makes it very, VERY hard to do a straightforward port that doesn’t involve years of additiional work. That’s a design flaw under the circumstances.

    That being said, being able to see 15 years into the future with your code is quite the engineering feat. Apple didn’t even do a good job of it back then (think Pink/Copland).

  18. Joe says:

    In my experience I have not had issues with Adobe files being created on one platform not work on the other. Nor have I read of any glaring feature ommissions between the version for Mac vs Windows for their cross platform applications. I do not use there software extensively so I cannot say with 100% accuracy this is the case.

    As you point out though the Applications in Mac Office have been around for about 20 years now. The versions of Excel and Word where originally written in a USCD Pascal type compiler that I believe was running on a VAX compiled down to PCODE with a PCODE interperter. Then in 1994 time frame when the Mac version of the Office package was released as a product those versions were written using MFC, an attempt to cross compile the native Windows version to the Mac directly. That unforutnately for many people was the worst versions of the applications. Thus the re-write for Office 98 with the foundation of the MacBU and the transition to Mac native tools like Codewarrior and about the same time when the VBA solution that is in use today was created.  So there is at least three times when major foundational, possibly major re-writes of the code base has occurred.

    Specifically relating to VBA my understanding/reading of how they implemented it in Office 98, while "clever" and  "insteresting" appears to have been a bit of a nightmare to maintain even if OS 9 and Codewarrior were still around (things like 64-bit, enhanced insturction/ABIs, too many compiler/assembly dependencies etc. (observations from schwieb’s blog) At some point with the migration to OS X and 64 bit computing, forget the intel migration for now, this was going to need a major overhaul. Better sooner rather than later.

  19. Nonsense! says:

    Please excuse me for using critical words – this guidebook is totally nonsense.

    The reason people use MS Office on Mac is just because they need to read/write files made on Windows. If applescript on Mac version of Excel does not work on Windows, there is no reason for people to use Applescript on Mac.

    Totally nonsense and outrageous movement.

  20. w says:

    I write VBA macros in Excel to run on both Macs and PCs. Please tell me how changing from VBA to AppleScript will allow me to continue to support PCs. Will the next version of MS Office for PC support AppleScript? If not, this guide is less than useless, I’ll have to ask my clients to switch to OpenOffice instead.

  21. HubmaN says:

    IMO, I think that Cocoa and Carbon CAN be ported to Windows. After all, remember that NeXT had NeXTSTEP running on a Win32 platform, and Cocoa and Carbon IS a part of that… including the "black box", etc.. You also might have to consider that Apple was considering Windows NT before they thought about BeOS and NeXTSTEP…