好的Excel宏出现问题时

Why do good Excel macros suddenly go bad? Does that ever happen to you? Here's my latest adventure, and maybe it will help you prevent (or solve) a similar problem.

为什么好的Excel宏突然变坏？ 那会发生在你身上吗？ 这是我最近的一次冒险，也许它将帮助您预防(或解决)类似的问题。

用宏取消保护工作表 (Unprotect Sheets With a Macro)

When making changes to client files, I use macros to quickly and easily protect or unprotect all the sheets in an Excel file, like the examples shown below.

更改客户端文件时，我使用宏来快速轻松地保护或取消保护Excel文件中的所有工作表 ，如下例所示。

• This macro protects all the worksheets in the active workbook, with no password.
  此宏不使用密码来保护活动工作簿中的所有工作表。

Sub ProtectAllSheetsNoPwd()
Dim ws As Worksheet
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
  ws.Protect DrawingObjects:=True, _
    Contents:=True, Password:=""
Next ws
End Sub

• And this macro unprotects all the worksheets, with no password.
  而且该宏取消了对所有工作表的保护，没有密码。

Sub UnProtectAllSheetsNoPwd()
Dim ws As Worksheet
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
  ws.Unprotect
Next ws
End Sub

保护宏失败 (The Protect Macro Fails)

However, even a simple macro can run into problems, as I'm sure you've already discovered with your own macros. It worked yesterday, but the macro won't run correctly today, in the same file. And you're sure that nothing was changed in the file – so what's the problem?

但是，即使是一个简单的宏也会遇到问题，因为我敢肯定您已经发现了自己的宏。 该宏昨天运行了，但是该宏今天无法在同一文件中正确运行。 而且您确定文件中没有任何更改–那么问题是什么？

That's what happened to me recently, while trying to unprotect a file, so I could make some changes. The macro ran, but the sheet's weren't unprotected.

这就是最近我在尝试取消保护文件时发生的事情，因此我可以进行一些更改。 宏已运行，但工作表并非不受保护。

I commented out the macro's error handling, to try and solve the problem, and this run-time error 1004 popped up:

我注释掉了宏的错误处理，以尝试解决问题，并弹出了运行时错误1004：

Method 'Unprotect' of object '_Worksheet' failed.

对象“ _Worksheet”的方法“取消保护”失败。

防止宏观问题 (Prevent the Macro Problem)

After a bit of head scratching, I realized what the problem was – I had grouped some of the sheets, because they all needed a formatting change. Oops! Because the sheets were grouped, they couldn't be unprotected.

挠了一下头之后，我意识到了问题所在–我将一些工作表归为一组，因为它们都需要更改格式。 糟糕！ 由于工作表已分组，因此无法不受保护。

I added a line of code to the macro, to ungroup the sheets, if necessary, and the problem is solved. You could do something fancier, like identifying the active sheet, and selecting it, instead of the first sheet.

我向宏添加了一行代码，以在必要时取消工作表的分组，从而解决了问题。 您可以做一些更奇特的事情，例如识别活动工作表并选择它，而不是第一张工作表。

Sub ProtectAllSheetsNoPwd()
Dim ws As Worksheet
On Error Resume Next
Sheets(1).Select '<====== selects first sheet in workbook
For Each ws In ActiveWorkbook.Worksheets
  ws.Protect DrawingObjects:=True, _
    Contents:=True, Password:=""
Next ws
End Sub

The same line was added to the Unprotect macro.

将同一行添加到“取消保护”宏。

Sub UnProtectAllSheetsNoPwd()
Dim ws As Worksheet
On Error Resume Next
Sheets(1).Select '<====== selects first sheet in workbook
For Each ws In ActiveWorkbook.Worksheets
  ws.Unprotect
Next ws
End Sub

观看保护宏失败视频 (Watch the Protect Macro Fails Video)

To see the problem that occurs when you try to protect or unprotect group sheets, and the code change that fixes it, you can watch this short Excel video tutorial.

若要查看在尝试保护或取消保护组工作表以及修复该问题的代码更改时发生的问题，可以观看此简短的Excel视频教程。

演示地址

翻译自: https://contexturesblog.com/archives/2012/04/26/when-good-excel-macros-go-bad/