Excel VBA：启用宏设置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9421843/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: Enabling Macro Settings
提问by user569125
I wrote a macro method to auto-size the columns in an Excel sheet upon opening the sheet. However, this solution does not work in the case that the user has not enabled macros in Excel.
我编写了一个宏方法来在打开工作表时自动调整 Excel 工作表中列的大小。但是，在用户未在 Excel 中启用宏的情况下，此解决方案不起作用。
Is there any way to check if the user has VBA macros disabled in Excel, and then enable the macros with Excel VBA code?
有什么方法可以检查用户是否在 Excel 中禁用了 VBA 宏，然后使用 Excel VBA 代码启用宏？
回答by brettdj
The standard way of doing this is to force the user to enable macros by use of a splash screen.
执行此操作的标准方法是通过使用闪屏强制用户启用宏。
- All the sheets in the workbook except for a spash screen are made very hidden(which can only be changed via VBA or the VBA editor)
- if macros are enabled:
1) When the workbook opens, the code unhides all these very hiddensheets
2) When the workbook closes then all these sheets are made very hiddenagain - If macros are not enabled then the user can only see the Splash Screen saying "Please enable macros, close and then re-open this file"
- 工作簿中除闪屏之外的所有工作表都 非常隐藏（只能通过 VBA 或 VBA 编辑器更改）
- 如果启用宏：
1) 当工作簿打开时，代码取消隐藏所有这些非常隐藏
的工作表2) 当工作簿关闭时，所有这些工作表再次变得非常隐藏 - 如果未启用宏，则用户只能看到启动画面说“请启用宏，关闭然后重新打开此文件”
Two links with full code for this technique are listed below
下面列出了带有此技术完整代码的两个链接
- Brad Yundt covers this here at TekTips
- Jonske at VBAeXpress
- Brad Yundt在 TekTips 上对此进行了介绍
- VBAeXpress的 Jonske
回答by assylias
This is a security feature. If you could disable it within Excel in vba it would defeat the purpose. I'm afraid the answer is no, the user will need to adapt his security settings...
这是一项安全功能。如果您可以在 vba 中的 Excel 中禁用它，它将无法达到目的。恐怕答案是否定的，用户需要调整他的安全设置......
You could change the settings from another application by modifying the registry
您可以通过修改注册表来更改其他应用程序的设置
回答by nhinkle
Here is an alternate method to check if VBA is enabled without having to hide/show sheets. The problem with hiding/unhiding sheets is that the worksheet could be saved in an "enabled" state, be closed without saving, and then be re-opened and not display the warning.
这是一种无需隐藏/显示工作表即可检查是否启用 VBA 的替代方法。隐藏/取消隐藏工作表的问题是工作表可以保存在“启用”状态，关闭而不保存，然后重新打开而不显示警告。
Create the following function in a Module (not a worksheet or workbook macro):
在模块（不是工作表或工作簿宏）中创建以下函数：
Public Function MacrosEnabled()
MacrosEnabled = True
End Function
Now, in whatever cell you want to display a notice regarding whether macros are enabled or not, enter the following equation:
现在，在您想要显示有关是否启用宏的通知的任何单元格中，输入以下等式：
=IF(ISERROR(MacrosEnabled()&NOW()),"Macros are disabled","Macros are enabled")
If you just use ISERROR(MacrosEnabled())the value is not recalculated every time the spreadsheet is opened. By concatenating NOW(), the current time is read every time the spreadsheet is opened, and so the value of the cell is recalculated. I tested this method extensively, and it seems to very reliably indicate whether macros are enabled or not.
如果您只是使用ISERROR(MacrosEnabled())该值，则不会在每次打开电子表格时重新计算。通过连接NOW()，每次打开电子表格时都会读取当前时间，因此会重新计算单元格的值。我广泛测试了这种方法，它似乎非常可靠地指示是否启用了宏。
You can then apply conditional formatting to do a number of things if macros are disabled:
如果宏被禁用，你可以应用条件格式来做一些事情：
Highlight the check-cell in a bright color, and perhaps change the "macros are disabled" text to include instructions on how to enable macros.
"Hide" cells whose values depend on macros being enabled by setting their text color and background color to be the same.
以明亮的颜色突出显示检查单元格，并可能更改“禁用宏”文本以包含有关如何启用宏的说明。
通过将文本颜色和背景颜色设置为相同来“隐藏”其值取决于启用宏的单元格。
回答by Tomamais
There is a way to automate the macro enabling, but you need to have full control on client machine at least once. Using digital certificates and sign your macros with it, the first time you run macros signed, Excel will offer to you an option to always trust macros signed by this, or not. When you trust it, all macros signed with your certificate will be automatically enabled.
有一种方法可以自动启用宏，但您至少需要对客户端计算机进行一次完全控制。使用数字证书并用它签署您的宏，当您第一次运行已签名的宏时，Excel 将为您提供一个选项，以始终信任由此签名的宏。当您信任它时，所有使用您的证书签名的宏都将自动启用。
This is the best option to develop and manage macros on your corporate network.
这是在公司网络上开发和管理宏的最佳选择。
Sorry about mistakes. My english is not perfect, yet. ;)
对错误感到抱歉。我的英语还不完美。;)
Regards
问候
