时间戳

首页 > 解决方案 > 优化 VBA 程序

vba - 优化 VBA 程序

问题描述

我创建了几个 VBA 函数和宏来自动化我的工作，但是随着更多的数据进入，我注意到运行宏的延迟更大。我可以在代码中更改或更改任何内容以提高其效率吗？

程序前提： - 刷新按钮循环遍历所有工作表，根据完成情况更改颜色，并将“不完整/过期”表格的信息放在表格中（最慢）

'===============
'Refresh Button on MASTER PAGE
'Functions: Updates color of sheets, based on completion/incompletion
'           Removes inputs from MASTER page
'           Updates Expired Forms cells
'====================
Sub refresh_form()
Dim ws As Worksheet
Dim wb As Workbook
Dim wsMASTER, wsTEMP
Dim complete, incomplete, exp, default   'to store color index's
Dim expName, expDate, expGSA, expStatus  'to store values for expired forms
Dim lastRow As Long                      'to store row # for expired & incomplete form

'CLEARS DATA FROM MAIN SHEET
ThisWorkbook.Worksheets("MASTER").Range("C6").Value = ""    'Project name
ThisWorkbook.Worksheets("MASTER").Range("C7").Value = ""    'Address
ThisWorkbook.Worksheets("MASTER").Range("C8").Value = ""    'Date
ThisWorkbook.Worksheets("MASTER").Range("C9").Value = ""    'GSA #
ThisWorkbook.Worksheets("MASTER").Range("C10").Value = ""   'Exp Date

wsMASTER = "MASTER"             'Sets wsMASTER as MASTER worksheet
wsTEMP = "TEMPLATE"             'Sets wsTEMP as TEMPLATE worksheet

complete = 4        'Green
incomplete = 44     'Orange
default = 2         'White
exp = 3             'Red

lastRow = 5        'Expired & Incomplete row starts at 5

For Each ws In ThisWorkbook.Worksheets                                                'Loops through all worksheets on click
    If ws.Name = wsMASTER Or ws.Name = wsTEMP Then                                      'For MASTER and TEMPLATE sheet, skip
        ws.Tab.ColorIndex = default
    ElseIf ws.Range("$M12").Value = True And ws.Range("$M$15").Value = True Then        'Applies "Exp" tab color to expired/incomp forms
        ws.Tab.ColorIndex = exp
        expName = ws.Range("$C$5").Value    'Stores current form's project name
        expDate = ws.Range("$C$9").Value    '***expiration date
        expGSA = ws.Range("$C$8").Value     '***GSA number
        lastRow = lastRow + 1               'increments lastRow by a value of 1
                                                                                        'VALUES INPUTTED IN EXPIRED & INCOMPLETE FORM
        ThisWorkbook.Worksheets("MASTER").Range("K" & lastRow).Value = expGSA           '       GSA #
        ThisWorkbook.Worksheets("MASTER").Range("L" & lastRow).Value = expName          '       Project name
        ThisWorkbook.Worksheets("MASTER").Range("M" & lastRow).Value = expDate          '       Expiration date

    ElseIf ws.Range("$M$12").Value = True Then                                          'Applies "Incomplete" tab color to incomplete forms
        ws.Tab.ColorIndex = incomplete
    ElseIf ws.Range("$M$12").Value = False And ws.Range("$N$12").Value = True Then      'Applies "Complete" tab color to complete forms
        ws.Tab.ColorIndex = complete
    Else                                                                                'Applies "Default" tab color to any untouched forms
        ws.Tab.ColorIndex = default
    End If
Next ws                                                                               'End Loop

End Sub                                                                                   'End Sub

标签： vbaexcel

解决方案

这个问题可能在Code Review中得到最好的回答，但提高性能的一种简单方法是执行以下操作：

'===============
'Refresh Button on MASTER PAGE
'Functions: Updates color of sheets, based on completion/incompletion
'           Removes inputs from MASTER page
'           Updates Expired Forms cells
'====================
Sub refresh_form()
Dim ws As Worksheet
Dim wsMaster As Worksheet: Set wsMaster = Worksheets("MASTER")
Dim wb As Workbook
Dim wsTEMP As String
Dim complete As Integer, incomplete As Integer, exp As Integer, default As Integer       'to store color index's
Dim lastRow As Long                      'to store row # for expired & incomplete form

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

'CLEARS DATA FROM MAIN SHEET
 wsMaster.Range("C6:C10").ClearContents

complete = 4        'Green
incomplete = 44     'Orange
default = 2         'White
exp = 3             'Red
lastRow = 5        'Expired & Incomplete row starts at 5

For Each ws In ThisWorkbook.Worksheets                                                'Loops through all worksheets on click
    If ws.Name = wsMaster.Name Or ws.Name = "TEMPLATE" Then                           'For MASTER and TEMPLATE sheet, skip
        ws.Tab.ColorIndex = default
    ElseIf ws.Range("$M12").Value = True And ws.Range("$M$15").Value = True Then      'Applies "Exp" tab color to expired/incomp forms
        ws.Tab.ColorIndex = exp
        lastRow = lastRow + 1                                                         'increments lastRow by a value of 1
        wsMaster.Range("K" & lastRow).Value = ws.Range("$C$8").Value                  'GSA #
        wsMaster.Range("L" & lastRow).Value = ws.Range("$C$5").Value                  'Project name
        wsMaster.Range("M" & lastRow).Value = ws.Range("$C$9").Value                  'Expiration date
    ElseIf ws.Range("$M$12").Value = True Then                                        'Applies "Incomplete" tab color to incomplete forms
        ws.Tab.ColorIndex = incomplete
    ElseIf ws.Range("$M$12").Value = False And ws.Range("$N$12").Value = True Then    'Applies "Complete" tab color to complete forms
        ws.Tab.ColorIndex = complete
    Else                                                                              'Applies "Default" tab color to any untouched forms
        ws.Tab.ColorIndex = default
    End If
Next ws                                                                               'End Loop

Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

推荐阅读