Excel VBA:使用循环时文件非常大

Question

目前我有一个包含60多张工作表的excel电子表格，我使用了一些Vba代码来保护每个工作表，使其不受某些列的影响，这对文件大小没有影响。

Private Sub ProtectWorksheets_Click()
'Protects the totals and summary sheets and sets password

Worksheets("Project Totals").Unprotect Password:="BIM"
Worksheets("Project Totals").Range("A1:Z10000").Locked = True
Worksheets("Project Totals").Protect Password:="BIM"

Worksheets("Initial Discipline Totals").Unprotect Password:="BIM"
Worksheets("Initial Discipline Totals").Range("A1:Z10000").Locked = True
Worksheets("Initial Discipline Totals").Protect Password:="BIM"

Worksheets("Discipline Totals").Unprotect Password:="BIM"
Worksheets("Discipline Totals").Range("A1:Z10000").Locked = True
Worksheets("Discipline Totals").Protect Password:="BIM"

Worksheets("Initial Summary Chart").Unprotect Password:="BIM"
Worksheets("Initial Summary Chart").Range("A1:Z10000").Locked = True
Worksheets("Initial Summary Chart").Protect Password:="BIM"

Worksheets("Summary Chart").Unprotect Password:="BIM"
Worksheets("Summary Chart").Range("A1:Z10000").Locked = True
Worksheets("Summary Chart").Protect Password:="BIM"

Worksheets("Summary Table").Unprotect Password:="BIM"
Worksheets("Summary Table").Range("A1:Z10000").Locked = True
Worksheets("Summary Table").Protect Password:="BIM"


'Protects remaining worksheets while unlocking the comments and discpiline fields

Worksheets("1").Unprotect Password:="BIM"
Worksheets("1").Range("D5:D10000").Locked = False
Worksheets("1").Range("I5:I10000").Locked = False
Worksheets("1").Protect Password:="BIM"

Worksheets("2").Unprotect Password:="BIM"
Worksheets("2").Range("D5:D10000").Locked = False
Worksheets("2").Range("I5:I10000").Locked = False
Worksheets("2").Protect Password:="BIM"


**This is repeated for the next 60 sheeets


End Sub

当运行上面的程序时，我最终得到了一个完全可以接受的文件大小，大约为8mb。

但是，如果将上面的代码替换为下面的代码，我将得到一个超过45mb的文件。

Private Sub ProtectWorksheets_Click()


Dim ws As Worksheet




'Protects remaining worksheets while unlocking the comments and discpiline fields


For Each ws In Sheets
ws.Unprotect Password:="BIM"
ws.Range("A1:Z10000").Locked = True
ws.Protect Password:="BIM"
Next



For Each ws In Sheets
If ws.Name <> "Front Page" And ws.Name <> "Admin" And ws.Name <> "Project 
Totals" And ws.Name <> "Initial Discipline Totals" And ws.Name <> 
"Discipline Totals" And ws.Name <> "Initial Summary Chart" And ws.Name <> 
"Summary Chart" And ws.Name <> "Summary Chart Table" Then
ws.Unprotect Password:="BIM"
ws.Range("D5:D10000").Locked = False
ws.Range("I5:I10000").Locked = False
ws.Protect Password:="BIM"
End If
Next


MsgBox ("All Sheets protected successfully")


End Sub

当我使用比第一个代码更好的代码时，为什么我最终会得到如此大的文件大小，任何帮助都将不胜感激。

Answer 1

我重新创建(并简化)了下面的代码，并尝试了两种方法：

使用Intersect

• Saving作为.xlsb (2MB)而不是.xlsm (18MB)

，

1. 将锁定的单元格限制为UsedRange中的单元格

Private Sub ProtectWorksheets_Click()
    Dim ws As Worksheet

    For Each ws In Sheets
        ws.Unprotect Password:="BIM"
        Intersect(ws.Range("A:Z"), ws.UsedRange).Locked = True
        Select Case ws.Name
            Case "Front Page", "Admin", "Project Totals", "Initial Discipline Totals", "Discipline Totals", "Initial Summary Chart", "Summary Chart", "Summary Chart Table"
                ws.Protect Password:="BIM"
            Case Else
                Intersect(ws.Range("D5:D10000"), ws.UsedRange).Locked = False
                Intersect(ws.Range("I5:I10000"), ws.UsedRange).Locked = False
                ws.Protect Password:="BIM"
        End Select
    Next ws

    MsgBox ("All Sheets protected successfully")
End Sub

我还使用了以下subs进行测试：

Public Sub MakeSheets()
    Dim i As Long
    For i = 1 To 59
        ThisWorkbook.Worksheets.Add
    Next i
End Sub

Public Sub InsertData()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Range("A1:I10000").Value = 1
    Next ws
End Sub