VBA中ScreenUpdating对速度提升的局限性和效果

概述

虽然可以通过ScreenUpdating来关闭屏幕更新，以达到加快宏代码的速度，但并不是任何情况下都会加快。

测试环境

将excel最大化，可显示1到47行、A到Z列。

一共要测试八种情况：

1. 在屏幕范围外的单元格中写入数据，范围是A列、1到10万行。
   1. 普通窗口
   2. 最小化窗口
   3. 普通窗口+ScreenUpdating
   4. 最小化窗口+ScreenUpdating
2. 在屏幕范围内的单元格中写入数据，范围是A到Z列，1到47行。
   1. 普通窗口
   2. 最小化窗口
   3. 普通窗口+ScreenUpdating
   4. 最小化窗口+ScreenUpdating

测试速度的代码

Private Declare Function QueryPerformanceCounter Lib "KERNEL32" (lpPerformanceCount As Currency) As Long
Private Declare Function QueryPerformanceFrequency Lib "KERNEL32" (lpFrequency As Currency) As Long

Private m_Frequency   As Currency
Private m_Start       As Currency
Private m_Now         As Currency
Private m_Available   As Boolean

Sub TestSpeet()

m_Available = (QueryPerformanceFrequency(m_Frequency) <> 0)
If Not m_Available Then
Debug.Print "Performance Counter not available"
End If

Dim i As Long, j As Long, m As Long

QueryPerformanceCounter m_Start

' 以下四个代码块中选一个，配合窗口是否最小化分别执行。

' A列、1到10万行：普通窗口、最小化窗口
For i = 1 To 100000 Step 1
    Cells(i, 1) = CStr(i)
Next i

' A列、1到10万行：普通窗口+ScreenUpdating、最小化窗口+ScreenUpdating
Application.ScreenUpdating = False
For i = 1 To 100000 Step 1
    Cells(i, 1) = CStr(i)
Next i
Application.ScreenUpdating = True

' A到Z列，1到47行：普通窗口、最小化窗口
For i = 1 To 10 Step 1
    For j = 1 To 47 Step 1
    For m = 1 To 26 Step 1
    Cells(j, m) = CStr(i)
    Next m
    Next j
Next i

' A到Z列，1到47行：普通窗口+ScreenUpdating、最小化窗口+ScreenUpdating
Application.ScreenUpdating = False
For i = 1 To 10 Step 1
    For j = 1 To 47 Step 1
    For m = 1 To 26 Step 1
    Cells(j, m) = CStr(i)
    Next m
    Next j
Next i
Application.ScreenUpdating = True

QueryPerformanceCounter m_Now

Elapsed = 1000 * (m_Now - m_Start) / m_Frequency
Debug.Print Format(Elapsed, "#.0000")
End Sub

测试结果

测试结果的时间单位是毫秒。

1. A列、1到10万行：

测试序号	普通窗口	最小化窗口	普通窗口+ScreenUpdating	最小化窗+ScreenUpdating
1	4432.7730	4400.6657	4379.5873	4379.3404
2	4432.2036	4396.2837	4366.7072	4374.3768
3	4430.9882	4397.5675	4357.7575	4375.4174
4	4432.9870	4406.4095	4376.3656	4369.2126
5	4433.9692	4407.4812	4359.0507	4380.2650
6	4443.2979	4444.7491	4365.8772	4385.9202
7	4434.1442	4396.5863	4386.3379	4393.0480
8	4436.1888	4418.3591	4378.6586	4383.0011
9	4432.1038	4401.5464	4366.8590	4376.4804
10	4443.4500	4411.1933	4353.5939	4377.3599

2. A到Z列，1到47行：

测试序号	普通窗口	最小化窗口	普通窗口+ScreenUpdating	最小化窗+ScreenUpdating
1	4055.8265	778.5692	550.5343	548.2441
2	4099.1295	775.6368	549.9552	551.3519
3	4075.3943	772.7159	548.8944	548.6186
4	4075.6114	775.3457	549.6685	547.8429
5	4081.8920	771.8189	548.7364	548.4509
6	4097.6187	773.4570	551.5706	547.0464
7	4056.6997	773.3298	549.6368	548.7333
8	4076.8709	770.2947	550.1524	550.8938
9	4083.8638	774.5747	548.9687	547.5058
10	4071.2810	774.3275	552.3702	549.2266

结论

在对A列、1到10万行的单元格写入时，ScreenUpdating的效果微乎其微，10万次写入才减少了66ms。

A到Z列，1到47行的单元格写入时，ScreenUpdating的效果极为明显，仅仅1222次（47*26）写入就减少了3527ms。

普通窗口和最小化窗口相比，最小化也有着略次于ScreenUpdating的效果，1222次仅比ScreenUpdating慢220ms。

综上所述，ScreenUpdating有其局限性：仅对屏幕正在显示的单元格有效，但其效果显著。