excel - 优化 VBA 宏

Question

宏的工作原理如下:

1. 我们有一个程序以 1-120 秒的间隔记录值，其中的数据位于 Sheet2 上，动态数据范围 B:W 列
2. 用户正在 Sheet3 上输入目标和偏差值
3. 在 Sheet2 上，如果一行中的最大值大于“目标值减去偏差值”，它将开始将行复制到 Sheet1 上的表中
4. 在 Sheet1 的表格中，值需要每 2 分钟显示一次，因此宏将复制每 X 行(取决于间隔)

宏工作正常，但我需要在 8 个不同的目标值上运行它 8 次。想知道是否有人有任何想法来加快速度

'Cell address with target value"  
target = Sheets(3).Cells(93, 2).Value   
'Cell address with deviation value"  
deviation = Sheets(3).Cells(95, 2).Value 

'Calculate time between measurements'  
lngRowMultiplier = 120 / Sheets(3).Cells(81, "B").Value 



'First row to copy into'  
pasteRow = 34  
'Row number to search through'  
For i = 2 To 8000 

    'Range to search through'
    s_max_value_range = "B" & i & ":W" & i

    'Max value in a row'
    max_value = Application.WorksheetFunction.Max(Sheets(2).Range(s_max_value_range))

    If (Abs(target - max_value) <= deviation) Then

        'Copy up to 5 hours or until lowest value in a row will be bigger than target value + deviation'
        For j = 1 To 150

            'Minimum value in a row'
            min_value = Application.WorksheetFunction.Min(Sheets(2).Range("B" & i + (j - 1) * lngRowMultiplier & ":W" & i + (j - 1) * lngRowMultiplier))

            If (min_value <= target + deviation) Then


            s_copyRange = "B" & i + (j - 1) * lngRowMultiplier & ":W" & i + (j - 1) * lngRowMultiplier
            s_pasteRange = "C" & pasteRow & ":V" & pasteRow

            'Copy to Sheet1'
            Sheets(2).Range(s_copyRange).Copy Destination:=Sheets(1).Range(s_pasteRange)
            Sheets(1).Range("B" & pasteRow) = Sheets(2).Range("B" & i + (j - 1) * lngRowMultiplier)
            pasteRow = pasteRow + 1
            End If
        Next j
        i = 8001
    End If
Next i

感谢所有帮助

Answer 1

有一些 VBA 规则可以使您的代码更快。

<小时/>

规则#1。不要复制和粘贴

复制和粘贴(或PasteSpecial)功能很慢。使用以下方法复制和粘贴值大约快 25 倍。

Range("A1:Z100").value = Range("A101:Z200").value

如果你这样做，你的代码可能会工作。如果您对许多行执行此操作，则 Mamory 可能存在问题。

<小时/>

规则#2。计算

通常，当单元格或单元格区域的先例发生更改时，Excel 会重新计算该单元格或单元格区域。这可能会导致您的工作簿过于频繁地重新计算，从而降低性能。您可以使用以下语句阻止 Excel 重新计算工作簿:

Application.Calculation = xlCalculationManual

在代码末尾，您可以使用以下语句将计算模式设置回自动:

Application.Calculation = xlCalculationAutomatic

但请记住，当计算模式为xlCalculationManual时，Excel 不会更新单元格中的值。如果您的宏依赖于更新的单元格值，则必须使用 .Calculate 方法(如 Worksheets(1).Calculate)强制执行计算事件。

<小时/>

规则#3。屏幕更新

VBA 的另一个速度问题是，每次 VBA 将数据写入工作表时，它都会刷新您看到的屏幕图像。刷新图像对性能有相当大的拖累。以下命令关闭屏幕更新。

Application.ScreenUpdating = FALSE

在宏末尾，使用以下命令重新打开屏幕更新。

Application.ScreenUpdating = TRUE

<小时/>

规则 #4 忽略事件

如果您为工作簿的 Sheet1 实现了 Worksheet_Change 事件。每当 Sheet1 上的单元格或区域发生更改时，Worksheet_Change 事件就会运行。因此，如果您有一个可操作 Sheet1 上多个单元格的标准宏，则每次更改该工作表上的单元格时，您的宏都必须在 Worksheet_Change 事件运行时暂停。您可以想象这种行为会如何减慢您的宏速度。

Application.EnableEvents = False

在代码末尾，您可以使用以下语句将 EnableEvents 模式设置回 True:

Application.EnableEvents = True

<小时/>

规则 #5 带声明

录制宏时，您经常会多次操作同一对象。您可以使用 With 语句一次性对给定对象执行多项操作，从而节省时间并提高性能。

以下示例中使用的 With 语句告诉 Excel 一次性应用所有格式更改:

With Range("A1").Font
.Bold = True
.Italic = True
.Underline = xlUnderlineStyleSingle
End With

养成将操作分块到 With 语句中的习惯不仅可以使宏运行得更快，而且还可以更轻松地阅读宏代码。

<小时/>