When companies make the future-proof move to Google Workspace coming from a Microsoft Office environment, it can be challenging to transform the dependencies on macro heavy Excel files to Google Sheets. With the recent roll-out of Google’s highly anticipated Macro Converter tool, that challenge has been diminished drastically. Save time through automation while reducing the work needed to convert files. In this article, we’ll guide you through how to use the tool.
This article is Written by Tobias Vander Stockt, Change Manager at Devoteam G Cloud
A lot of companies rely on macros to automate tasks in Excel spreadsheets: whether it’s cleaning up a sheet with raw data or automating a business process. It’s a good way to save time and avoid having to perform the same, repetitive tasks.
When switching to Google Sheets and its functional scripting language Apps Script, it can be a challenge to learn a whole new way of writing code and transform the dependencies on Excel’s VBA (Visual Basic for Applications).
With the new Macro Converter tool, that part has become a lot easier. Simply installable as a Google Workspace Add-on, it helps you assess which parts in VBA need to be adapted for a successful transformation and guides you through the conversion process.
Prerequisites
Now, before you can start to use this incredible add-on, please note that:
- You must have a Google Workspace Enterprise plus or an Enterprise for Education account.
- You should have some proficiency with scripting languages (either VBA or Apps Script, preferably both).
- You need to install it first, by simply going to the Google Workspace Marketplace. Once installed, you’ll find it in the sidebar when visiting Google Drive.
Checking the compatibility
The first step you’ll need to take is to generate a compatibility report for the file(s) you want to convert. An Excel file with VBA macros is considered compatible if all APIs used in the macros have a direct equivalent in Apps Script. If your macros aren’t fully compatible, you might be able to apply workarounds or adjust the code to make them work with Apps Script. The level of compatibility will be indicated by three statuses:
- Supported Exactly – The file contains APIs that have exact equivalents in Apps Script
- Supported with Workarounds – At least one API will be supported once a workaround is performed in VBA.
- Needs more investigation – At least one API will need further investigation or the tooling couldn’t determine which API is used exactly.
Before you generate the report, make sure you:
- Know the exact location of these files in your My Drive or Shared Drive(s)
- Create a destination folder for all your reports and macro files.
Once that’s clear, open the Macro Converter and generate the report.
The report will be split up into 5 different sections, accessible by browsing through the different sheets. Next to a summarised analysis of the checked file, you’ll find these sheets:
- Compatibility: details on which files will convert automatically and which will need manual investigation.
- Detailed Analysis: for a given file, which APIs require further investigation.
- APIs to investigate: which incompatible functionalities/APIs come up regularly and finally. What’s great about this sheet is that It will show the exact reason for incompatibility, give a detailed description of the issue and provide you with links to Microsoft documentation on how to solve/investigate it.
- APIs with workarounds: which API workarounds are almost there.
Workarounds
Once you have investigated the APIs with status Needs investigation or Supported with workaround, you can modify the VBA code to pass the compatibility check. If the function performed by the API isn’t critical to your VBA macro working properly, remove it from your VBA code. If it is, change your code to implement a similar behavior with supported VBA APIs.
After you make changes to your VBA code, run the compatibility report again. This step helps confirm the increased compatibility of your files and flags any additional updates that should be made.
If you’re unable to switch to a supported VBA API, leave it as it is. After you convert the desired file to Apps Script, a description of a workaround will be described in a readme file together with the actual output file. This will assist you in creating the workaround in Apps Script, instead of VBA.
For extra guidance on how to modify incompatible VBA APIs, please visit this Google Apps Script help page.
Conversion and Final Checks
For files that have the status Supported exactly, you can proceed to the conversion phase. Then you can convert your file to a google sheet. Together with the output file, as mentioned above, you’ll receive a ReadMe, that will assist you in creating the Apps Script workaround.
After the compatibility check and conversion, You might need to make adjustments to your new Apps Script code to make sure your code works as intended or address common issues: You might need to manually create items, like VBA UserForms, in Apps Script.
Make sure to test your Apps Script by running the code and testing the trigger (f.e. onOpen(), onEdit(), or onClick())
If you’re stuck, I can highly recommend you to select the Help button in the tool itself. It leads you to developers.google.com, where you’ll find step-to-step instructions, an overview of common issues, and some video tutorials.
This article is Written by Tobias Vander Stockt, Change Manager at Devoteam G Cloud
Feel free to contact us directly if you have any questions on the Macro Converter add-on!
