excel - 如何使我的 VBA 代码运行得更快而不使屏幕闪烁？

Question

我写了下面的代码，它按预期工作。但我可能可以让它更有效率，我相信我已经避免使用“选择”，但还有什么我应该尽量避免的吗？
宏需要几秒钟才能运行，有什么方法可以让它更快？当我运行它时，屏幕“闪烁”很多，我能做些什么吗？该文件很大，所以也许引入一些停止计算的东西？

Sub ActivateUser(User As String)
    Call LoadVariables
    
    Call UnProtectSheet("Master")
    LoadedUser = User
    
        Dim Cell As Range
        On Error Resume Next
        For Each Cell In ShowSheets
            Call UnProtectSheet(Cell.Value)
            Worksheets(Cell.Value).Visible = IIf(Cell.Offset(1, 0) = "YES", True, False) 'moving down one row to check if it should be showing or not,
                If Cell.Value = "Currencies" Then ' This is done to avoid hiding column A in the "currencies" sheet
                    Else
                    Worksheets(Cell.Value).Range("A:A").EntireColumn.Hidden = IIf(Cell.Offset(1, 0) = "YES", True, False) 'Showing or hiding column A
                End If
            Worksheets(Cell.Value).Range(FurthestColumn).EntireColumn.Hidden = IIf(Cell.Offset(1, 0) = "YES", False, True) 'Showing or hiding all other columns
                If (User = "AdminEdit") Or (User = "adminedit") Or (User = "Adminedit") Or (User = "adminEdit") Then
                    Else
                    Call HideSpecificColumns(Cell.Value)
                    Call ProtectSheet(Cell.Value)
                End If
        Next Cell
    

        Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
            ws.Activate
            ActiveWindow.ScrollColumn = 1
            ActiveWindow.ScrollRow = 1
        Next ws
    
    
        Call UnProtectSheet("Basic Data")
        FirstTimeOpenToday = "Yes"
        FirstTimeOpen = "Yes"
        Call ProtectSheet("Basic Data")
        
        Call HideSheet("LoginPage")
        
        
    Sheets("Quotation").Select
    Range("B1").Select
End Sub

Sub HideSpecificColumns(ws As String)
    Dim Fixed1Sub As String
    Dim Fixed2Sub As String
    Dim SubToCall As String
    
    'Can't contain any special characters, i'm only taking "-" and " " into account now.
    Fixed1Sub = WorksheetFunction.Substitute(ws, " ", "")
    Fixed2Sub = WorksheetFunction.Substitute(Fixed1Sub, "-", "")
    SubToCall = Fixed2Sub & "Columns"
    
    Application.Run SubToCall
End Sub

这就是我设置“全局变量”的方式。所以我只需要在一个地方改变这些东西。

Global ShowSheets As Range
Global LoadedUser As Range
Global UsertypeListBoxNamesFirstValue As Range
Global UsertypeListBoxNamesCount As Integer
Global FurthestColumn As String
Global LockPassword As String
Global CombinationRange As Range
Global CustomerPlace As Range
Global ProjectNamePlace As Range
Global DesignNumberPlace As Range
Global SalesPersonPlace As Range
Global YourNamePlace As Range
Global FirstTimeOpen As Range
Global FirstTimeOpenToday As Range
Global BuildNumber As Range


Sub LoadVariables()

'General
'======================================================================================================================================================
    LockPassword = "Admin123"
    Set CombinationRange = Worksheets("Master").Range("HS8:HS20") ' Range whereas all correct login are to activate users.
    Set FirstTimeOpen = Worksheets("Basic Data").Range("S4") ' Displays "Yes" or "No" depending on if this is the first time the files is opened
    Set FirstTimeOpenToday = Worksheets("Basic Data").Range("S7") ' Displays "Yes" or "No" depending on if this is the first time the files is opened today.
    Set BuildNumber = Worksheets("Basic Data").Range("N193")
'======================================================================================================================================================




'Macro to show/hide sheets based on User
'======================================================================================================================================================
    Set ShowSheets = Worksheets("Master").Range("GR19:HL19") 'Pick the range with the name of the sheets, the range above the yes or no. (For the macro to hide and show sheets based on User)
    Set LoadedUser = Worksheets("Master").Range("HN22") ' Cell with the name of the loaded user.
    FurthestColumn = "B:KE" ' When not open, this field is what is hidden on every sheet, must be larger than the largest one.
'======================================================================================================================================================





'Loginsheet
'======================================================================================================================================================
    Set UsertypeListBoxNamesFirstValue = Sheets("Master").Range("HO10") ' This should be the first cell that has a value of the list of 'User' to login with.
    UsertypeListBoxNamesCount = Sheets("Master").Range("HO19") ' This should be the cell with the count of how many users there are.
'======================================================================================================================================================





'StartPage
'======================================================================================================================================================
Set CustomerPlace = Sheets("Quotation").Range("D5")
Set ProjectNamePlace = Sheets("Quotation").Range("I2")
Set DesignNumberPlace = Sheets("Quotation").Range("I4")
Set SalesPersonPlace = Sheets("Quotation").Range("I5")
Set YourNamePlace = Sheets("Quotation").Range("I6")
'======================================================================================================================================================

End Sub

这是我使用的一些“通用”公式。

'Unprotecting and protecting sheets
'===================================================================
Sub UnProtectSheet(Sheet As String)
    Call LoadVariables
    Dim ws As Worksheet
    Set ws = Sheets(Sheet)
    ws.Unprotect LockPassword
End Sub

Sub ProtectSheet(Sheet As String)
    Call LoadVariables
    Dim ws As Worksheet
    Set ws = Sheets(Sheet)
    ws.Protect LockPassword, AllowFiltering:=True
End Sub
'===================================================================


'Hiding and unhiding sheets
'===================================================================

Sub UnHideSheet(Sheet As String)
    Worksheets(Sheet).Visible = True
End Sub

Sub HideSheet(Sheet As String)
    Worksheets(Sheet).Visible = False
End Sub
'===================================================================



'Opening StandardUserforms
'===================================================================
Sub OpenLoginForm()
    Loginform.show
End Sub
Sub OpenStartForm()
    Startpage.show
End Sub
'===================================================================


'Testing Attempts to login
'===================================================================
Function UnlockWall(Combination As String) As Boolean
    Dim Key As Integer
    Call LoadVariables
    UnlockWall = False
    
    If Combination = "/" Then
        Exit Function
    End If

    On Error Resume Next
    Key = WorksheetFunction.Match(Combination, CombinationRange, 0)
    
    If Key = 0 Then
        Else
        UnlockWall = True
    End If
End Function
'===================================================================


'First time opening and logging in to the workbook, do this:
'===================================================================
Sub PopulateWithStartPage(A As String, B As String, C As String, D As String, E As String)
Call LoadVariables

    Call UnHideSheet("Quotation")
    Call UnProtectSheet("Quotation")
    YourNamePlace = A
    ProjectNamePlace = B
    DesignNumberPlace = C
    SalesPersonPlace = D
    CustomerPlace = E

    
    Call UnHideSheet("Basic Data")
    Call UnProtectSheet("Basic Data")
    FirstTimeOpen = "Yes"
    FirstTimeOpenToday = "Yes"
    Call ProtectSheet("Basic Data")
    
End Sub
'===================================================================

我也有这些潜艇，我用它们来隐藏每张纸上的特定列。我没有包括所有这些，但是我在“activateuser sub”中循环的每张表都存在一个子。

Sub MasterColumns() 'Can't contain any special characters, i'm only taking "-" and " " into account now.
    'exit sub 'Turn on/off depending on if the sheets includes any columns that should be hidden
    
    Dim ColumnArray(6) As String 'The number within the "()" is the maximum amount of column that can be hidden. May not be lower then used below.
    
    ColumnArray(0) = "D:G" ' Change as per columns you want hidden, note that it has to be in the "X:X" format.
    ColumnArray(1) = "J:M"
    ColumnArray(2) = "P:S"
    ColumnArray(3) = "W:AJ"
    ColumnArray(4) = "AM:AP"
    ColumnArray(5) = "AS:AW"
    'ColumnArray(6) = ""
    'ColumnArray(7) = ""
    'ColumnArray(8) = ""
    'ColumnArray(9) = ""
    'ColumnArray(10) = ""
    'ColumnArray(11) = ""
    'ColumnArray(12) = ""
    'ColumnArray(13) = ""
    'ColumnArray(14) = ""
    'ColumnArray(15) = ""
    'ColumnArray(16) = ""
    'ColumnArray(17) = ""
    'ColumnArray(18) = ""
    'ColumnArray(19) = ""
    'ColumnArray(20) = ""
    
    Dim i As Integer
    For i = 0 To 5 ' ================== This has to be one less than above number
        Sheets("Master").Range(ColumnArray(i)).EntireColumn.Hidden = True '=========== Change to correct sheet
    Next i
End Sub


Sub BasicDataColumns() 'Can't contain any special characters, i'm only taking "-" and " " into account now.
    Exit Sub 'Turn on/off depending on if the sheets includes any columns that should be hidden
End Sub
Sub ErrorFinderColumns() 'Can't contain any special characters, i'm only taking "-" and " " into account now.
    Exit Sub 'Turn on/off depending on if the sheets includes any columns that should be hidden
End Sub

Answer 1

The macro takes a couple of seconds to run, is there any way to make it quicker?

在这种情况下，我的建议是不要触摸你的宏。 If it ain't broke, don't fix it.我们中的大多数人实际上很容易达到这种“欣喜若狂的状态”:D
如果这只是一个学习练习，那么可以，您可以进行这些少量更改，它将优化您的代码。不是在性能方面，而是在编写简洁的代码方面。您可以明显地注意一些小的调整，如闪烁等。
以下是我的建议

避免鲁莽使用 On Error Resume Next .这就像告诉 Excel 闭嘴 :)。而是进行主动编程。先发制人并处理错误。

关闭事件。但是，每当您关闭事件时，请使用错误处理将其重新打开，否则如果出现错误，代码将不会在下次运行。还要确保不要修改用户设置。根据需要进行更改，然后按照@PaulOgilvie 的建议完成后重置它们。为避免闪烁，您可以使用 Application.ScreenUpdating = False
这是一个例子

Sub Sample()
    On Error GoTo Whoa

    Dim PrevCalcMode As Integer
    Dim PrevScrUp As Boolean

    '~~> Get previous settings
    PrevCalcMode = Application.Calculation
    PrevScrUp = Application.ScreenUpdating

    '~~> Set your new settings
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    '
    '~~> Your code
    '

Letscontinue:
    '~~> Reset to original settings
    Application.EnableEvents = True
    Application.ScreenUpdating = PrevScrUp
    Application.Calculation = PrevCalcMode
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

不要使用全局变量，然后将范围加载到这些变量中，而是使用 Named Ranges
例如，您不需要 Set CombinationRange = Worksheets("Master").Range("HS8:HS20") .您可以将范围命名为 HS8:HS20在 Master如CombinationRange如上面的链接所示，然后使用 Range("CombinationRange")在你的代码中。

而不是创建全局变量 Global LockPassword As String然后设置它的值 LockPassword = "Admin123" ，直接声明为Public LockPassword As String = "Admin123"在最顶端。

避免不必要地调用过程。它增加了执行时间。例如，您调用 Call LoadVariables在几乎每个子。

使用With End With .我不会在这里详细介绍。 @Moosli 已经涵盖了这一点。