excel - VBA循环遍历工作簿中的所有工作表

Question

我尝试遵循 VBA 代码，我想在事件工作簿中为所有可用工作表运行此代码，我想我犯了一个小错误，因为我是初学者，所以我无法找到它，请帮助修复它

 Sub ProtectFormulas()


 Dim strPassword As String

 Dim ws As Worksheet

 For Each ws In Sheets

 ws.Activate

 .Unprotect

 .Cells.Locked = False

 .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
 .Cells.SpecialCells(xlCellTypeFormulas).FormulaHidden = True

 .Protect AllowDeletingRows:=True

 strPassword = 123456
 ActiveSheet.Protect Password:=strPassword

 Next ws

 End With

 End Sub

任何帮助都会得到感谢。

Answer 1

您的代码有 3 个问题:

没有 With block 。

如果其中一张表中没有公式，则以下 2 行将出错:

.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Cells.SpecialCells(xlCellTypeFormulas).FormulaHidden = True

因为如果没有公式，那么 .Cells.SpecialCells(xlCellTypeFormulas) 就是 Nothing，因此没有任何东西没有 .Locked 和 .FormulaHidden 方法。

你混合使用 Sheets 和 Worksheets 。请注意，这些不一样!

Sheets 是所有类型工作表(工作表、图表等)的集合

Worksheets 是唯一类型工作表的集合

如果您声明 Dim ws As Worksheet 并且文件中有例如图表，则 For Each ws In Sheets 将出错，因为您尝试将图表推送到定义为 ws 且不能包含图表的变量 Worksheet 中。尽可能具体并尽可能使用 Worksheets 来支持 Sheets 。

以下应该有效:

Option Explicit

'if this is not variable make it a constant and global so you can use it in any procedure
Const strPassword As String = "123456" 

Sub ProtectFormulas()
    'Dim strPassword As String
    'strPassword = "123456"  'remove this here if you made it global

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws 
            .Activate 'I think this is not needed
            .Unprotect Password:=strPassword 'unprotect probably needs your password too or this will not work once the worksheet was protected.
            .Cells.Locked = False
            
            Dim FormulaCells As Range
            Set FormulaCells = Nothing 'initialize (because we are in a loop!)
            On Error Resume Next 'hide error messages (next line throws an error if no forumla is on the worksheet
            Set FormulaCells = .Cells.SpecialCells(xlCellTypeFormulas)
            On Error Goto 0 ' re-enable error reporting! Otherwise you won't see errors if they occur!
            If Not FormulaCells Is Nothing Then 'check if there were formulas to prevent errors if not
                FormulaCells.Locked = True
                FormulaCells.FormulaHidden = True
            End If

            .Protect AllowDeletingRows:=True, Password:=strPassword 
        End With   
    Next ws
 End Sub