时间戳

首页 > 解决方案 > VBA循环遍历工作簿中的所有工作表

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

问题描述

我已尝试遵循 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

任何帮助都会得到感谢。

标签： excelvba

解决方案

您的代码有 3 个问题：

  1. 没有With块。

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

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

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

  3. 你混合使用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

推荐阅读