Common issues you might run into when you convert your VBA code to Apps Script with the Macro Converter are listed below.
Printing
VBA APIs that print files are automatically converted to Apps Script, but might behave differently than the original VBA API. Below are two examples:
VBA API | Behavior in Apps Script |
---|---|
PrintOut | Converts to Apps Script, but the Apps Script API prints to a file instead of a printer. You can manually print the PDF file. |
PrintToFile | Converts to Apps Script. The PDF file is saved in your MyDrive folder. |
Unconverted items
The following features aren’t converted by the Macro Converter and need to be converted manually:
- Some types of Triggers
- Userforms
- Unsupported types of Named ranges
Triggers
Two types of triggers, keyboard shortcuts and some event-based triggers, aren’t converted by the Macro Converter. In many cases, you can create these triggers manually.
Keyboard shortcuts
To add keyboard shortcuts, follow the steps to import functions as macros.
Event-based triggers
Some events from VBA code, like BeforeClose
or BeforeSave
, don’t have
equivalents in Apps Script, but you might be able to create a workaround.
For events like BeforeClose
, you can create a custom menu or button to click
to perform the action that needs to take place before you close the spreadsheet.
Since Google Sheets auto-saves each edit, workarounds for events like
BeforeSave
aren’t feasible.
Userforms
In VBA, a UserForm is a window or dialog box in an application's user interface (UI). The Macro Converter doesn’t convert UserForms. You can manually create them in Apps Script.
Create a user form dialogue
- On your computer, open the converted file in Google Sheets.
- At the top, click Extensions > ** Apps Script**.
- At the left of the editor next to "Files," click Add a file > HTML. We recommend that you give the HTML file the same name as your original VBA UserForm.
- Add the fields and information you want to appear in your form. Learn more about HTML forms at W3school.com.
- At the left, click the Apps Script file (GS file) that has your converted code.
- If you already have an
onOpen()
trigger in your code, update it with the code below. If you don’t have theonOpen()
trigger in your code, add the code below.function onOpen() { SpreadsheetApp.getUi() .createMenu('User Form') .addItem('Show Form', 'showForm') .addToUi(); } function showForm() { var html = HtmlService.createHtmlOutputFromFile('userform_module_name') .setWidth(100) .setTitle('Sign-up for Email Updates'); SpreadsheetApp.getUi().showSidebar(html); }
- Replace
userform_module_name
with the name of the HTML file you added. - At the top, click Save project .
- Switch to the Google Sheet and reload the page.
- At the top of the Google Sheet, click User Form > Show Form.
Named ranges
In Excel, named ranges are names given to a single cell or range of cells.
When you convert your Excel file to Google Sheets, some types of named ranges won’t be converted because they’re not supported. Below are two examples:
Unsupported named ranges | Description |
---|---|
Tables | Not supported in Google Sheets, but has a workaround.
To recreate this named range in Sheets, add a named range that points to the A1 notation of the table range. Use the same name as the original named range in your VBA code so that the converted code recognizes it. |
List of ranges | Not supported in Google Sheets. There isn’t a workaround. |
Related articles
- Macro Converter add-on overview
- Determine if VBA macros are compatible
- Convert VBA macros to Apps Script
- Fix errors in your converted code
- Watch Macro Converter tutorials
- List of compatible VBA APIs