Address common issues

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 APIBehavior in Apps Script
PrintOutConverts to Apps Script, but the Apps Script API prints to a file instead of a printer. You can manually print the PDF file.
PrintToFileConverts 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:

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

New editor

  1. On your computer, open the converted file in Google Sheets.
  2. At the top, click Extensions > Apps Script.
  3. 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.
  4. Add the fields and information you want to appear in your form. Learn more about HTML forms at W3school.com.
  5. At the left, click the Apps Script file (GS file) that has your converted code.
  6. If you already have an onOpen() trigger in your code, update it with the code below. If you don’t have the onOpen() 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);
    }
        
  7. Replace userform_module_name with the name of the HTML file you added.
  8. At the top, click Save project .
  9. Switch to the Google Sheet and reload the page.
  10. At the top of the Google Sheet, click User Form > Show Form.

Legacy editor

  1. On your computer, open the converted file in Google Sheets.
  2. At the top, click Extensions > Apps Script.
  3. At the top of the Apps Script editor, click File > New > HTML File. We recommend that you give the HTML file the same name as your original VBA UserForm.
  4. Add the fields and information you want to appear in your form. Learn more about HTML forms at W3school.com.
  5. At the left, click the Apps Script file (GS file) that has your converted code.
  6. If you already have an onOpen() trigger in your code, update it with the code below. If you don’t have the onOpen() 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);
    }
        
  7. Replace userform_module_name with the name of the HTML file you added.
  8. At the top, click Save project .
  9. Switch to the Google Sheet and reload the page.
  10. 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 rangesDescription
TablesNot 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 rangesNot supported in Google Sheets. There isn’t a workaround.