将新工作表添加到已启用XLSM Macro的Excel文件时出错 [英] Error adding a new sheet to a XLSM Macro Enabled Excel File
问题描述
我想使用VBA在启用宏的Excel文件中添加新工作表.
I want to add a new sheet in a Macro Enabled Excel File using VBA.
我使用的方法都没关系:
It doesn't matter the method that I use:
- Sheets.add
- 复制并粘贴工作表以创建新工作表
新工作表已创建(或复制)，但宏立即停止，没有任何消息错误，并且其余代码未执行.
The new sheet is created (or copied) but the macro stops immediately, without any message error, and the rest of the code is not executed.
我将文件转换为XLSX，然后在运行代码时创建了工作表，并且代码执行得以完美地继续.
I converted the file to XLSX and when I run the code, the sheet is created and the code execution continues perfectly.
这就是为什么我认为它与启用宏的Excel"文件类型有关.
That's why I think that it is related to the Macro Enabled Excel type of file.
更新:
我在另一台PC和Excel安装中测试了代码，然后创建了新工作表，代码继续进行.
I tested the code in another PC and Excel installation and the new sheet is created and the code goes on.
两台计算机之间的唯一区别是Excel 2016版本:
一个标准，另一个专业.
The only difference between the two computers is the Excel 2016 version:
Standard in one and Professional in the other.
推荐答案
我在使用32位Excel版本16.0.10730.20264的Windows 7计算机上遇到了相同的问题，代码运行正常，没有问题.但是，在具有相同Excel安装版本的Windows 10计算机上，该宏将立即在Sheets.Add或Worksheets.Add行之后停止执行.我发现这只是在尝试向包含VBA代码的工作簿中添加工作表的情况下发生的.该问题是由计算机上的宏安全设置引起的.如果在打开工作簿之前将自动化安全性"设置为低"，则不应再收到错误:Application.AutomationSecurity = msoAutomationSecurityLow
I was experiencing the same issue, on a Windows 7 computer with Excel version 16.0.10730.20264 32-bit, the code ran fine without issue. However, on a Windows 10 computer with the same Excel install version, the macro would immediately stop execution following the Sheets.Add or Worksheets.Add line. I found that this was only happening where I was attempting to add a sheet to a workbook that contained VBA code. The issue is caused by the macro security settings on the computer. If you set Automation Security to Low before opening the workbook, you should no longer get the error: Application.AutomationSecurity = msoAutomationSecurityLow
这篇关于将新工作表添加到已启用XLSM Macro的Excel文件时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
