Perhaps you already use VBA to extend Office, but are considering alternatives. You may have already “gone Google,” or you might be looking at Microsoft’s Apps for Office options. In particular, you’re 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.
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, it’s 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 today’s 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 Microsoft’s 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.
I’m a longtime proponent and fan of VBA. I’m 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) it’s probably time to move on, as one of these days it really will no longer be shipped with Office.
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.
For add-ins and add-ons (extending Docs and Office with client-side web apps), and the
HtmlService sections of the book, you’ll need some understanding of HTML, CSS, and the DOM.
The rest of the book deals with each Google Apps Script service that has an equivalent in VBA (and a few that don’t), 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 don’t plan to use some of the services covered). It’s 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 don’t have the option to get stuck on an old version (even if you want to).
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.
All examples (VBA and Apps Script) are publicly available on GitHub, the details of which you’ll find in Appendix A, but I encourage you to try to create some of the code in the Apps Script IDE to build up familiarity with the environment. Some of the VBA examples are more appropriate for Office for Windows and may not be fully compatible with Office for Mac. Of course, the Apps Script examples are platform independent.
Dim someVBA as string
I’ve omitted much of the exception handling that would normally need to be built in, simply so that the code can remain as focused as possible on the explanation of the topic in hand. If you do reuse any of the code in real applications, don’t forget to extend the error handling to your house standard.
Once you have mastered the techniques demonstrated, you should be in a good position to port all your legacy applications over to Apps Script with minimal effort.
Constant width bold
Constant width italic
This icon signifies a tip, suggestion, or general note.
This icon indicates a warning or caution.
Supplemental material for this book is available for download at https://github.com/brucemcpherson/GoingGas.
This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.
We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Going GAS by Bruce Mcpherson (O’Reilly). Copyright 2016 Pepada limited, 978-1-4919-4046-4.”
If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at email@example.com.
Technology professionals, software developers, web designers, and business and creative professionals use Safari Books Online as their primary resource for research, problem solving, learning, and certification training.
Members have access to thousands of books, training videos, and prepublication manuscripts in one fully searchable database from publishers like O’Reilly Media, Prentice Hall Professional, Addison-Wesley Professional, Microsoft Press, Sams, Que, Peachpit Press, Focal Press, Cisco Press, John Wiley & Sons, Syngress, Morgan Kaufmann, IBM Redbooks, Packt, Adobe Press, FT Press, Apress, Manning, New Riders, McGraw-Hill, Jones & Bartlett, Course Technology, and hundreds more. For more information about Safari Books Online, please visit us online.
Please address comments and questions concerning this book to the publisher:
We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at http://bit.ly/going-gas.
To comment or ask technical questions about this book, send email to firstname.lastname@example.org.
For more information about our books, courses, conferences, and news, see our website at http://www.oreilly.com.
Find us on Facebook: http://facebook.com/oreilly
Follow us on Twitter: http://twitter.com/oreillymedia
Watch us on YouTube: http://www.youtube.com/oreillymedia
When I started putting together the material for this book, I never realized there was so much I didn’t know, nor that it would take so long to find it all out and write it down. Many people have helped me along the way, and I’d like to take a little of your time to thank them.
I owe a great debt of gratitude to my truly international and esteemed group of technical reviewers, who gave generously of their time and knowledge to ruthlessly pick apart everything I wrote. Ian Macro (England) gave me a particularly hard time, pointing out those kinds of errors that are easy to miss as an author.
Fellow GDEs (Google Developer Experts) Martin Hawksey (Scotland), Ivan Kutl (Czech Republic), and Riël Notermans (Netherlands) kept me in line when my prose became too extravagant, my claims lacked foundation, I had missed an important detail, or I was just plain wrong.
Special thanks go to my longtime friend (and ex-boss), Ron Roberts, who supported me as I became interested in the APIs that Google was creating with such rapidity, and who so graciously allowed me the latitude to become part of the Google Developer Expert community.
Apologies go to my Cairn terriers, who will be especially pleased that these writings are over (for now). Their thrice-daily walks became a little shorter as my book deadline approached, but now we can get back to normal.
Most importantly, thanks to my wife, Blandine, whose rigorous encouragement inspired me to get typing when I would rather have been eating cheese, drinking wine, playing Baldurs Gate, or even washing the car. Without her support and tolerance, I would never have got past the blank page that confronted me on day 1.
Finally, thanks to my editor, Meg Foley, and the team at O’Reilly Media who continue to give ordinary people like us the opportunity to have a voice, and without whose guidance, assistance, and hard work this book would not have been possible.
Without the great products created by both Google and Microsoft, I would have had nothing to write about. The pace of innovation is staggering, the APIs are exciting, and I can’t wait to see what’s coming up next so I can get started on my next project.