Excel VBA 代码的密码保护如何工作？

How password protection of Excel VBA code works?

提问by axk

This question is related to my previous one.

这个问题与我之前的问题有关。

Can you explain or provide a link to an explanation of how Excel VBA code password protection actually works in versions prior to 2007? What is the difference in Excel 2007 and previous versions in terms of password protection?

您能否解释或提供有关 Excel VBA 代码密码保护在 2007 年之前的版本中实际如何工作的解释的链接？Excel 2007 和以前版本在密码保护方面有何不同？

Also does Excel's password protection actually encrypt the code? How does Excel execute the code if it is encrypted?

Excel 的密码保护实际上是否对代码进行了加密？如果代码是加密的，Excel 如何执行？

Lastly, how does password removal software for excel work?

最后，excel的密码删除软件如何工作？

回答by Phil.Wheeler

VBA security is widely considered to be pretty poor. The VBA code isn't compiled, and the source is available in the excel file. The password protection is pretty easy to circumvent.

VBA 安全性被广泛认为非常差。VBA 代码未编译，源代码在 excel 文件中可用。密码保护很容易绕过。

As I understand it, Office 2003 and earlier saves the vba code as part of the binary format of the worksheet (or document / presentation). When you fire up the VBA IDE, it simply looks to see whether the VBA code has been "protected" or not. This doesn't mean it's encrypted - just unavailable for viewing. The theory is that this stops your users from meddling with your code, but a hard-core coder would be able to get around the password.

据我了解，Office 2003 及更早版本将 vba 代码保存为工作表（或文档/演示文稿）二进制格式的一部分。当您启动 VBA IDE 时，它只是查看 VBA 代码是否已被“保护”。这并不意味着它已加密 - 只是无法查看。理论上，这会阻止您的用户干预您的代码，但核心编码人员将能够绕过密码。

So Excel doesn't need to unencrypt any code - it just needs to stop people from viewing it.

所以 Excel 不需要解密任何代码——它只需要阻止人们查看它。

Office 2007 doesencrypt macros (don't ask me how or what algorithm). This is necessary presumably because XLSM files (or any Office 2007 file) are just zip files with a different extension. Anyone can get into those files and poke around.

Office 2007确实加密宏（不要问我如何或什么算法）。这可能是必要的，因为 XLSM 文件（或任何 Office 2007 文件）只是具有不同扩展名的 zip 文件。任何人都可以进入这些文件并四处查看。

To answer your last question - how does the password removal work on older Office formats, I'm not entirely sure. Different vendors will possibly approach the problem different ways, but I suspect the most common approach will be a brute-force attack on the passwords until a match is found.

要回答您的最后一个问题 - 密码删除如何在较旧的 Office 格式上工作，我不完全确定。不同的供应商可能会以不同的方式解决问题，但我怀疑最常见的方法是对密码进行暴力攻击，直到找到匹配项。

The Excel VBProject object has a Protection property which will return different enumerations depending on the protection status of the macro (vbext_pp_lockedif the macro is protected, for example). If you were to keep trying passwords programmatically until the vbext_pp_lockedevaluated to false, you would have found your password.

Excel VBProject 对象有一个 Protection 属性，它将根据宏的保护状态（vbext_pp_locked例如，如果宏受保护）返回不同的枚举。如果您继续以编程方式尝试密码，直到vbext_pp_locked评估为 false，您就会找到您的密码。

回答by DaveParillo

Phil is correct - the password prevent you from looking at the modules, they are not encrypted themselves. I know in excel 2007 a file is essentially a zipped collection of XML and other files, but I don't know the details of how encryption is handled. For earlier versions - excel 2, 3, 4, 5, 95, 97, 2000, XP, & 2003, there is the comprehensive OpenOffice.org's Documentation of the Microsoft Excel File Format:

Phil 是正确的 - 密码阻止您查看模块，它们本身没有加密。我知道在 excel 2007 中，文件本质上是 XML 和其他文件的压缩集合，但我不知道如何处理加密的详细信息。对于早期版本 - excel 2、3、4、5、95、97、2000、XP 和 2003，有全面的 OpenOffice.org 的 Microsoft Excel 文件格式文档：

The Excel file format is named BIFF (Binary Interchange File Format). It is used to store all types of documents: worksheet documents, workbook documents, and workspace documents. There are different versions of this file format, depending on the version of Excel that has written the file, and depending on the document type.

A workbook document with several sheets (BIFF5-BIFF8) is usually stored using the compound document file format (also known as “OLE2 storage file format” or “Microsoft Office compatible storage file format”). It contains several streams for different types of data. A complete documentation of the format of compound document files can be found here.

The Workbook Protection Block occurs just after the DEFINEDNAME block (i.e. Named Ranges) in most BIFF streams, although BIFF8 is a major departure from that pattern. The record protection block In Biff5 - Biff8 the structure of the Workbook Protection Block:

Excel 文件格式被命名为 BIFF（二进制交换文件格式）。它用于存储所有类型的文档：工作表文档、工作簿文档和工作区文档。此文件格式有不同版本，具体取决于写入文件的 Excel 版本以及文档类型。

多张工作簿文档 (BIFF5-BIFF8) 通常使用复合文档文件格式（也称为“OLE2 存储文件格式”或“Microsoft Office 兼容存储文件格式”）进行存储。它包含用于不同类型数据的多个流。可以在此处找到复合文档文件格式的完整文档。

在大多数 BIFF 流中，工作簿保护块紧跟在 DEFINEDNAME 块（即命名范围）之后，尽管 BIFF8 与该模式有很大不同。记录保护块 在 Biff5 - Biff8 中工作簿保护块的结构：

• WINDOWPROTECT Window settings: 1 = protected
• PROTECT Workbook contents: 1 = protected
• PASSWORD Hash value of the password; 0 = no password
• PROT4REV Shared workbook: 1 = protected
• PROT4REVPASS Hash value of the shared password; 0 = no password

• WINDOWPROTECT 窗口设置：1 = 受保护
• PROTECT 工作簿内容：1 = 受保护
• PASSWORD 密码的哈希值；0 = 无密码
• PROT4REV 共享工作簿：1 = 受保护
• PROT4REVPASS 共享密码的哈希值；0 = 无密码

The password block stores a 16-bit hash value, calculated from the worksheet or workbook protection password.

密码块存储一个 16 位哈希值，该值是根据工作表或工作簿保护密码计算得出的。

回答by Morosho

Someone made a working vba code that changes the vba protection password to "macro", for all excel files, including .xlsm (2007+ versions). You can see how it works by browsing his code.

有人制作了一个有效的 vba 代码，将 vba 保护密码更改为“宏”，适用于所有 excel 文件，包括 .xlsm（2007+ 版本）。你可以通过浏览他的代码来了解它是如何工作的。

Here's the guy blog: http://lbeliarl.blogspot.com/2014/03/excel-removing-password-from-vba.htmlHere's the file that does the work: https://docs.google.com/file/d/0B6sFi5sSqEKbLUIwUTVhY3lWZE0/edit

这是家伙博客：http: //lbeliarl.blogspot.com/2014/03/excel-removing-password-from-vba.html这是完成工作的文件：https: //docs.google.com/file/ d/0B6sFi5sSqEKbLUIwUTVhY3lWZE0/编辑

Pasted from a previous post from his blog:

粘贴自他博客的前一篇文章：

For Excel 2007/2010 (.xlsm) files do following steps:

对于 Excel 2007/2010 (.xlsm) 文件，请执行以下步骤：

1. Create a new .xlsm file.
2. In the VBA part, set a simple password (for instance 'macro').
3. Save the file and exit.
4. Change file extention to '.zip', open it by any archiver program.
5. Find the file: 'vbaProject.bin' (in 'xl' folder).
6. Extract it from archive.
7. Open the file you just extracted with a hex editor.

8. Find and copy the value from parameter DPB (value in quotation mark), example: DPB="282A84CBA1CBA1345FCCB154E20721DE77F7D2378D0EAC90427A22021A46E9CE6F17188A". (This value generated for 'macro' password. You can use this DPB value to skip steps 1-8)

9. Do steps 4-7 for file with unknown password (file you want to unlock).

10. Change DBP value in this file on value that you have copied in step 8.

    If copied value is shorter than in encrypted file you should populate missing characters with 0 (zero). If value is longer - that is not a problem (paste it as is).

11. Save the 'vbaProject.bin' file and exit from hex editor.

12. Replace existing 'vbaProject.bin' file with modified one.
13. Change extention from '.zip' back to '.xlsm'
14. Now, open the excel file you need to see the VBA code in. The password for the VBA code will simply be macro (as in the example I'm showing here).

1. 创建一个新的 .xlsm 文件。
2. 在 VBA 部分，设置一个简单的密码（例如“宏”）。
3. 保存文件并退出。
4. 将文件扩展名更改为“.zip”，通过任何存档程序打开它。
5. 找到文件：“vbaProject.bin”（在“xl”文件夹中）。
6. 从存档中提取它。
7. 使用十六进制编辑器打开刚刚解压缩的文件。

8. 查找并复制参数 DPB 中的值（引号中的值），例如：DPB="282A84CBA1CBA1345FCCB154E20721DE77F7D2378D0EAC90427A22021A46E9CE6F17188A"。（此值是为“宏”密码生成的。您可以使用此 DPB 值跳过步骤 1-8）

9. 对未知密码的文件（要解锁的文件）执行步骤 4-7。

10. 根据您在步骤 8 中复制的值更改此文件中的 DBP 值。

    如果复制的值比加密文件中的短，您应该用 0（零）填充缺失的字符。如果值更长 - 那不是问题（按原样粘贴）。

11. 保存“vbaProject.bin”文件并退出十六进制编辑器。

12. 用修改过的文件替换现有的“vbaProject.bin”文件。
13. 将扩展名从“.zip”改回“.xlsm”
14. 现在，打开您需要查看 VBA 代码的 excel 文件。 VBA 代码的密码将只是宏（如我在此处展示的示例）。