解决常见问题
使用集合让一切井井有条
根据您的偏好保存内容并对其进行分类。
下面列出了使用 Macro Converter 将 VBA 代码转换为 Apps 脚本时可能会遇到的常见问题。
正在打印
用于打印文件的 VBA API 会自动转换为 Apps 脚本,但其行为可能与原始 VBA API 不同。以下是两个示例:
VBA API | Apps 脚本中的行为 |
---|
PrintOut | 转换为 Apps 脚本,但 Apps 脚本 API 会打印到文件而不是打印机。您可以手动打印 PDF 文件。 |
PrintToFile | 转换为 Apps 脚本。PDF 文件会保存在您的“我的云端硬盘”文件夹中。 |
未转化的商品
以下功能不会通过宏转换器进行转换,需要手动转换:
触发器
宏转换器不会转换两种类型的触发器:键盘快捷键和某些基于事件的触发器。在许多情况下,您可以手动创建这些触发器。
键盘快捷键
如需添加键盘快捷键,请按照将函数作为宏导入的步骤操作。
基于事件的触发器
VBA 代码中的某些事件(例如 BeforeClose
或 BeforeSave
)在 Apps 脚本中没有对应的事件,但您或许可以创建一种解决方法。
对于 BeforeClose
等事件,您可以创建自定义菜单或按钮,以便在关闭电子表格之前点击该菜单或按钮来执行所需的操作。
由于 Google 表格会自动保存每次编辑,因此无法针对 BeforeSave
等事件采取变通方法。
在 VBA 中,UserForm 是应用界面 (UI) 中的窗口或对话框。宏转换器不会转换 UserForm。您可以在 Apps 脚本中手动创建它们。
- 在电脑上,使用 Google 表格打开转换后的文件。
- 点击顶部的扩展程序 > ** Apps 脚本**。
- 在编辑器左侧的“文件”旁边,依次点击“添加文件”图标 add
> HTML。建议您为 HTML 文件指定与原始 VBA UserForm 相同的名称。
- 添加您希望在表单中显示的字段和信息。如需详细了解 HTML 表单,请访问 W3school.com。
- 在左侧,点击包含转换后代码的 Apps 脚本文件(GS 文件)。
- 如果您的代码中已有
onOpen()
触发器,请使用以下代码更新该触发器。如果您的代码中没有 onOpen()
触发器,请添加以下代码。
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);
}
- 将
userform_module_name
替换为您添加的 HTML 文件的名称。
- 点击顶部的“保存项目”图标 save。
- 切换到 Google 工作表并重新加载页面。
- 在 Google 工作表的顶部,依次点击用户表单 > 显示表单。
命名范围
在 Excel 中,命名范围是指为单个单元格或单元格范围指定的名称。
将 Excel 文件转换为 Google 表格时,某些类型的命名范围不会转换,因为它们不受支持。以下是两个示例:
不受支持的命名范围 | 说明 |
---|
表 | Google 表格不支持,但有解决办法。
如需在 Google 表格中重新创建此命名范围,请添加指向表格范围的 A1 表示法的命名范围。使用与 VBA 代码中原始命名范围相同的名称,以便转换后的代码能够识别该名称。
|
范围列表 | Google 表格不支持此功能。没有变通方案。 |
相关文章
如未另行说明,那么本页面中的内容已根据知识共享署名 4.0 许可获得了许可,并且代码示例已根据 Apache 2.0 许可获得了许可。有关详情,请参阅 Google 开发者网站政策。Java 是 Oracle 和/或其关联公司的注册商标。
最后更新时间 (UTC):2025-08-31。
[null,null,["最后更新时间 (UTC):2025-08-31。"],[[["\u003cp\u003eWhen converting VBA to Apps Script, printing functions behave differently; \u003ccode\u003ePrintOut\u003c/code\u003e prints to a file instead of directly to a printer, while \u003ccode\u003ePrintToFile\u003c/code\u003e saves the PDF to your MyDrive.\u003c/p\u003e\n"],["\u003cp\u003eThe Macro Converter does not handle certain VBA features, including some triggers, UserForms, and unsupported named ranges, requiring manual conversion.\u003c/p\u003e\n"],["\u003cp\u003eKeyboard shortcuts and some event-based triggers (like \u003ccode\u003eBeforeClose\u003c/code\u003e or \u003ccode\u003eBeforeSave\u003c/code\u003e) from VBA require manual configuration or workarounds in Apps Script due to functional differences.\u003c/p\u003e\n"],["\u003cp\u003eUserForms need to be recreated in Apps Script using HTML, and integrated into the spreadsheet through custom menus or sidebars.\u003c/p\u003e\n"],["\u003cp\u003eWhile most named ranges are converted, tables and lists of ranges require manual adjustments or are unsupported, necessitating alternative approaches in Google Sheets.\u003c/p\u003e\n"]]],[],null,["# Address common issues\n\nCommon issues you might run into when you convert your VBA code to Apps Script\nwith the Macro Converter are listed below.\n\nPrinting\n--------\n\nVBA APIs that print files are automatically converted to Apps Script, but might\nbehave differently than the original VBA API. Below are two examples:\n\n| VBA API | Behavior in Apps Script |\n|---------------|------------------------------------------------------------------------------------------------------------------------------|\n| `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. |\n| `PrintToFile` | Converts to Apps Script. The PDF file is saved in your MyDrive folder. |\n\nUnconverted items\n-----------------\n\nThe following features aren't converted by the Macro Converter and need to be\nconverted manually:\n\n- Some types of [Triggers](#triggers)\n- [Userforms](#userforms)\n- Unsupported types of [Named ranges](#named_ranges)\n\n### Triggers\n\nTwo types of triggers, keyboard shortcuts and some event-based triggers, aren't\nconverted by the Macro Converter. In many cases, you can create these triggers\nmanually.\n\n#### Keyboard shortcuts\n\nTo add keyboard shortcuts, follow the steps to [import functions as\nmacros](https://developers.google.com/apps-script/guides/sheets/macros#importing_functions_as_macros).\n\n#### Event-based triggers\n\nSome events from VBA code, like `BeforeClose` or `BeforeSave`, don't have\nequivalents in Apps Script, but you might be able to create a workaround.\n\nFor events like `BeforeClose`, you can create a custom menu or button to click\nto perform the action that needs to take place before you close the spreadsheet.\n\nSince Google Sheets auto-saves each edit, workarounds for events like\n`BeforeSave` aren't feasible.\n\n### Userforms\n\nIn VBA, a [*UserForm*](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/userform-object) is a window or dialog box in an application's user\ninterface (UI). The Macro Converter doesn't convert UserForms. You can manually\ncreate them in Apps Script.\n\n#### Create a user form dialogue\n\n1. On your computer, open the converted file in [Google Sheets](http://sheets.google.com).\n2. At the top, click **Extensions** \\\u003e \\*\\* Apps Script\\*\\*.\n3. At the left of the editor next to \"Files,\" click Add a file add **\\\u003e HTML**. We recommend that you give the HTML file the same name as your original VBA UserForm.\n4. Add the fields and information you want to appear in your form. Learn more about HTML forms at [W3school.com](https://www.w3schools.com/html/html_forms.asp).\n5. At the left, click the Apps Script file (GS file) that has your converted code.\n6. 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. \n\n ```gdscript\n function onOpen() {\n SpreadsheetApp.getUi()\n .createMenu('User Form')\n .addItem('Show Form', 'showForm')\n .addToUi();\n }\n function showForm() {\n var html = HtmlService.createHtmlOutputFromFile('userform_module_name')\n .setWidth(100)\n .setTitle('Sign-up for Email Updates');\n SpreadsheetApp.getUi().showSidebar(html);\n }\n \n ```\n7. Replace `userform_module_name` with the name of the HTML file you added.\n8. At the top, click Save project save.\n9. Switch to the Google Sheet and reload the page.\n10. At the top of the Google Sheet, click **User Form** \\\u003e **Show Form**.\n\n### Named ranges\n\nIn Excel, named ranges are names given to a single cell or range of cells.\n\nWhen you convert your Excel file to Google Sheets, some types of named ranges\nwon't be converted because they're not supported. Below are two examples:\n\n| Unsupported named ranges | Description |\n|--------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|\n| Tables | Not supported in Google Sheets, but has a workaround. \u003cbr /\u003e 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. |\n| List of ranges | Not supported in Google Sheets. There isn't a workaround. |\n\nRelated articles\n----------------\n\n- [Macro Converter add-on overview](/apps-script/guides/macro-converter/overview)\n- [Determine if VBA macros are compatible](/apps-script/guides/macro-converter/compatibility-report)\n- [Convert VBA macros to Apps Script](/apps-script/guides/macro-converter/convert-files)\n- [Fix errors in your converted code](/apps-script/guides/macro-converter/fix-conversion-errors)\n- [Watch Macro Converter tutorials](/apps-script/guides/macro-converter/tutorials)\n- [List of compatible VBA APIs](/apps-script/guides/macro-converter/compatible-vba-apis)"]]