I work in an office where we have a number of amateur Excel "experts", who are constantly trying to "improve" (i.e. shortcut, change the logic, rewrite to make "faster") the spreadsheets. As I work in a very regulated world, where macros need to be approved by the company, the clients, and the regulator, changing the macros is very much a no-no.
我在一间办公室里工作，那里有许多业余的Excel“专家”，他们不断地试图“改进”(例如，捷径、改变逻辑、重写以使电子表格“更快”)。由于我在一个监管非常严格的世界中工作，在这个世界里，宏需要得到公司、客户和监管机构的批准，因此修改宏是非常不允许的。
One of the issues is that we have is that the people are able to get into the code, even after we have put in password protection to set the sheets to Read Only, and also set a password in the VBA Properties screen. The users seem to be able to bypass the passwords, and get into the main code, without the passwords that have been set.
其中一个问题是，即使我们输入了密码保护，将表设置为只读，并且在VBA Properties屏幕上设置了密码，人们仍然能够进入代码。用户似乎能够绕过密码，进入主代码，而不需要设置密码。
Is there anything else that can be done to stop them getting into the code? An idea that was mentioned, but I can't find a reference to it was to prevent the VBA screen from opening. Can this be done? Any other advice would be gratefully accepted.
还有什么可以阻止他们进入代码的吗?我提到过一个想法，但我找不到它的参考，那就是防止VBA屏幕打开。这个可以做吗?任何其他的建议都会被感激地接受。
1 个解决方案
#1
5
Once you give your users physical access to a PC, you can't guarantee that your security won't be defeated. The whole industry even says as much:
一旦你给你的用户物理访问一台PC，你不能保证你的安全不会被打败。整个行业甚至都这么说:
If a bad guy has unrestricted physical access to your computer, it's not your computer anymore
如果一个坏人可以不受限制地访问你的电脑，那就不是你的电脑了
Password protection is at best a deterrent. If your users are already circumventing your protections, then there's nothing you can do to absolutely block them from the VBA, other than removing VBA altogether.
密码保护充其量是一种威慑。如果您的用户已经绕过了您的保护，那么您将无法完全阻止VBA，而不是完全删除VBA。
Is is possible to install Office without the VBA capabilities, but then you can't run any Macros whatsoever, so you're probably losing more than you gain in locking it down.
可以在没有VBA功能的情况下安装Office，但是您不能运行任何宏，所以您可能会损失超过锁定它的收益。
You could try obfuscating your code, but that's not really stopping your users from writing more of their own.
您可以尝试混淆代码，但这并不能真正阻止用户编写更多自己的代码。
You can, however, use managed add-ins and VSTO, whereby the code is moved out of the Excel workbooks and VBA modules, and into .NET source. The assemblies are deployed and/or installed, and can't easily be changed by users.
但是，您可以使用托管外接程序和VSTO，这样代码就可以从Excel工作簿和VBA模块中转移到。net源代码中。这些程序集是部署和/或安装的，用户不能轻易地更改它们。
Most regulators that I've worked with are aware of the limitations of VBA and password protection, but they're also aware of the business benefits of allowing users to work with the features that Excel and VBA provide. They've usually taken the view that as long as you've implemented reasonable passwords and protections, then any circumvention of those protections is beyond the capability of the typical user.
与我共事过的大多数监管机构都意识到VBA和密码保护的局限性，但他们也意识到允许用户使用Excel和VBA提供的功能的商业好处。他们通常认为，只要你实现了合理的密码和保护，那么任何对这些保护的规避都超出了典型用户的能力。
You have to put reasonable protections in place, and distribute and enforce robust policies and procedures. Yes, you'll get people who violate policy, but you'll have a clear case, should regulators question your approaches.
你必须采取合理的保护措施，发布和实施强有力的政策和程序。是的，你会遇到违反政策的人，但如果监管机构质疑你的做法，你会有一个明确的理由。
On the other hand, rather than assuming that some/all of your "experts" are malicious, maybe organize a series of sessions to discuss how things can be improved. You never know, they might have real optimizations that reduce your costs and/or improve your performance and resiliency.
另一方面，与其假设你所有的“专家”都是恶意的，不如组织一系列的会议来讨论如何改进。你永远不知道，他们可能会有真正的优化来降低你的成本和/或提高你的表现和弹性。
#1
5
Once you give your users physical access to a PC, you can't guarantee that your security won't be defeated. The whole industry even says as much:
一旦你给你的用户物理访问一台PC，你不能保证你的安全不会被打败。整个行业甚至都这么说:
If a bad guy has unrestricted physical access to your computer, it's not your computer anymore
如果一个坏人可以不受限制地访问你的电脑，那就不是你的电脑了
Password protection is at best a deterrent. If your users are already circumventing your protections, then there's nothing you can do to absolutely block them from the VBA, other than removing VBA altogether.
密码保护充其量是一种威慑。如果您的用户已经绕过了您的保护，那么您将无法完全阻止VBA，而不是完全删除VBA。
Is is possible to install Office without the VBA capabilities, but then you can't run any Macros whatsoever, so you're probably losing more than you gain in locking it down.
可以在没有VBA功能的情况下安装Office，但是您不能运行任何宏，所以您可能会损失超过锁定它的收益。
You could try obfuscating your code, but that's not really stopping your users from writing more of their own.
您可以尝试混淆代码，但这并不能真正阻止用户编写更多自己的代码。
You can, however, use managed add-ins and VSTO, whereby the code is moved out of the Excel workbooks and VBA modules, and into .NET source. The assemblies are deployed and/or installed, and can't easily be changed by users.
但是，您可以使用托管外接程序和VSTO，这样代码就可以从Excel工作簿和VBA模块中转移到。net源代码中。这些程序集是部署和/或安装的，用户不能轻易地更改它们。
Most regulators that I've worked with are aware of the limitations of VBA and password protection, but they're also aware of the business benefits of allowing users to work with the features that Excel and VBA provide. They've usually taken the view that as long as you've implemented reasonable passwords and protections, then any circumvention of those protections is beyond the capability of the typical user.
与我共事过的大多数监管机构都意识到VBA和密码保护的局限性，但他们也意识到允许用户使用Excel和VBA提供的功能的商业好处。他们通常认为，只要你实现了合理的密码和保护，那么任何对这些保护的规避都超出了典型用户的能力。
You have to put reasonable protections in place, and distribute and enforce robust policies and procedures. Yes, you'll get people who violate policy, but you'll have a clear case, should regulators question your approaches.
你必须采取合理的保护措施，发布和实施强有力的政策和程序。是的，你会遇到违反政策的人，但如果监管机构质疑你的做法，你会有一个明确的理由。
On the other hand, rather than assuming that some/all of your "experts" are malicious, maybe organize a series of sessions to discuss how things can be improved. You never know, they might have real optimizations that reduce your costs and/or improve your performance and resiliency.
另一方面，与其假设你所有的“专家”都是恶意的，不如组织一系列的会议来讨论如何改进。你永远不知道，他们可能会有真正的优化来降低你的成本和/或提高你的表现和弹性。