Perhaps you already use VBA to extend Office, but are considering alternatives. You may have already gone Google, or you might be looking at Microsofts Apps for Office options. In particular, youre wondering what to do with all the VBA code built up over the years that you consider essential to enabling your business processes.
This book will show you how to transition from VBA with minimal effort. Even if you are not a VBA user, you will learn how to use Apps Script and its ecosystem to automate processes in the Google Apps platform.
Why Transition from VBA?
In many ways, VBA has been a victim of its own success. Its tight integration with Office and very usable and immediate development environment make it hard to beat. However, its been with us since 1991, the same year that Tim Berners-Lee created the first website, which is still running today, but for historical rather any aesthetic or functional reasons.
Although VBA is as far removed from its 1991 forefather as todays HTML5 sites are from that first website, every version of Office for as long as I can remember has come with a threat that perhaps VBA will not be supported. Office 2008 for Mac did not support VBA, but it was back again by public demand in Office 2011. Office 2016 has just been released, and we can all breathe a sigh of relief to see that VBA is still there. But Microsofts focus is shifting to Office 365 from Office for the desktop. According to Satya Nadella of Microsoft, the most strategic developer surface for us is Office 365.
With incompatibilities between 32-bit and 64-bit versions and between Office for Mac and Windows, and with a reliance on references to libraries that are no longer shipped with Windows, VBA becomes harder for Microsoft to support and for us to use.
Im a longtime proponent and fan of VBA. Im not alone, considering the Office development section of my website still generates almost a million annual page views, and there are still many active VBA communities and forums around. VBA continues to enjoy immense popularity, but (sadly) its probably time to move on, as one of these days it really will no longer be shipped with Office.
This book will show you by example how to do some of the same things you do today in VBA, but in Apps Script. The examples will use the Google Apps platform, but well also look at the Microsoft JavaScript API.
Intended Audience
You are probably already a VBA or .NET developer. You might even already be an Apps Script developer who needs to understand something about VBA to assist with a migration, or perhaps you simply want to learn Apps Script. This is not intended to be a book for beginners, and best fits those who are already comfortable with one or more development languages.
Its not necessary to already know JavaScript, but things will move faster if you do. This is not a JavaScript tutorial, but this book will introduce the language components and syntax and provide enough examples to enable the proficient use of Apps Script.
For add-ins and add-ons (extending Docs and Office with client-side web apps), and the HtmlService
sections of the book, youll need some understanding of HTML, CSS, and the DOM.
The VBA Library
The overall objective of this book is to demonstrate how to apply Apps Script services best practices to solve problems common across Office automation platforms. A key output is a library that emulates many built-in VBA functions in JavaScript. This will allow you to port some of your VBA code and structure to Apps Script with minimal changes, while concentrating on the Apps Script services capabilities.
Reading Order
The first part of the book compares the capabilities of each platform, then moves on to the fundamentals of the JavaScript language, emphasizing how it differs from VBA. That is followed by a long reference section containing an implementation in JavaScript of each of the main VBA built-in functions, as well as some of the utility objects.
If you decide to use this library, it means that you can still write VBA-style code that calls VBA built-in function names, instead of using the native JavaScript equivalent. Alternatively, you can refer to the translations and implement those when you port your applications.
The rest of the book deals with each Google Apps Script service that has an equivalent in VBA (and a few that dont), and will generally show you the contrast in navigating and interfacing between the respective object models.
There are both tutorials and reference material, and the order in which you read them is not especially important, although some of the examples refer back to previous chapters and concepts. It is likely that you already have experience in some of the subjects (or dont plan to use some of the services covered). Its not really required that you read the content sequentially.
In summary, the scope of the material is not only the contrast between how to do things in Apps Script and VBA, but also how to get things done in the Google universe. This will ease the transition to Google Apps, regardless of where you are coming from.
Apps Script is both young and versatile, with new capabilities being added (and old ones being deprecated) regularly, and unlike with VBA, you dont have the option to get stuck on an old version (even if you want to).
The Examples
The code illustrations are a mixture of snippets and longer projects, but they quickly become challenging, using the kind of patterns found in real-world scenarios. This is by design. After all, you are probably already an accomplished developer and Hello, World"level tutorials are not going to be much help for quickly porting VBA applications that have already benefited from significant investment in intellectual and financial capital.