VBA实战：提升Excel代码运行速度的10种方法

VBA实战：提升Excel代码运行速度的10种方法

引用

CSDN

1.

https://blog.csdn.net/nianfen/article/details/138742775

在使用VBA进行Excel开发时，提升代码运行效率是每个开发者都关心的问题。本文总结了10种实用的优化技巧，从关闭屏幕刷新到使用数组处理数据，从减少循环判断到使用WithValue属性，每种方法都配有详细的代码示例，帮助你写出更高效、更优雅的VBA代码。

1. 关闭屏幕刷新

关闭屏幕刷新能让客户不必看到过程，有时候能提升用户体验，有时候是降低的。低代码时提升作用不明显，酌情使用。

Application.ScreenUpdating = False '关闭屏幕更新
'此处放置你的程序
Application.ScreenUpdating = True '恢复屏幕更新

2. 关闭自动计算

如果工作表有很多函数，在代码运行过程中，关闭公式计算可以显著提升运行速度。

Application.Calculation = xlCalculationManual '手动重算
ActiveSheet.Calculate '计算当前工作表
Calculate '计算这个工作簿
Application.Calculation = xlCalculationAutomatic '自动重算

3. 关闭警告提示

防止你的程序被警告打断，应只在某些弹警告的工况下使用，测试时不要用。

Application.DisplayAlerts = False '关闭提示
'你的程序
Application.DisplayAlerts = True '恢复提示

4. 关闭事件

人工操作数据的时候会设置一些响应，当程序操作变更数据的时候也会触发一些响应例如change、select，当此处程序会触发响应时，关闭所有响应不仅能提高程序运行速度，也能避免一些Bug。

Application.EnableEvents = False '关闭事件
'你的程序
Application.EnableEvents = True '启动事件

5. 定义数组

将range赋值给数组，判断和处理数据时使用数组，输出时再操作单元格，可以极大加快程序运行速度。注意三点：

1. 数组是存储在内存中，避免赋值过大的range给数组，否则很占内存；
2. 逻辑运行完之后不影响其他函数情况下应清除数组释放缓存，否侧会反复占用内存。
3. 函数外的定义数组是长期占内存的，应在函数内定义和释放。

电脑内存不足会报错：内存溢出！

Dim datamp0 As Variant
datamp0 = Sheets(sht_name).Range("A1:Z20000")
'你的程序
Erase datamp0

6. 加进度条

加进度条虽然不能提高代码运行效率甚至稍微降低，但是如果程序需要运行较长时间，进度条能提振用户信心，避免用户多次点按误操作，造成excel异常卡死。

Sub test()
    For i = 1 To 10000
        Debug.Print "i=" & i
        Call ProcessBarUpdater(i, 10000, "正在处理") '显示进度
    Next
End Sub

Sub ProcessBarUpdater(intCurrent As Long, intLast As Long, strTopic As String) '进度条
    Dim intCurrentStatus As Integer
    Dim intNumberOfBars As Integer
    Dim intPercentDone As Integer
    intNumberOfBars = 50
    intCurrentStatus = Int((intCurrent / intLast) * intNumberOfBars)
    intPercentDone = Round(intCurrentStatus / intNumberOfBars * 100, 0)
    Application.StatusBar = strTopic & " [" & String(intCurrentStatus, "|") & _
                            Space(intNumberOfBars - intCurrentStatus) & "]" & _
                            " " & intPercentDone & "% Complete"
    If intCurrent = intLast Then Application.StatusBar = ""
End Sub

7. 少用循环判断

1. 在原有数据后面加数据，避免使用for循环找空值，用VBA自带函数

ni = Sheets("扭矩查询").Range("a" & Rows.Count).End(xlUp).Row + 1

2. 查找数据，避免for循环判断，用find函数

Set rng = Sheet1.Range("A1:C10").Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
    MsgBox "找到值在: " & rng.Address
Else
    MsgBox "未找到值。"
End If

8. 使用 .Value2

从 Excel 区域读取数据时使用 .Value2 ，.Text 和.Value 返回速度较慢。

9. 使用With

对某个表格，区域，或者单元格进行一系列的操作，比如内容，颜色，格式等，可以用with来提高代码运行效率，同时让代码结构更清晰。

With ActiveSheet
     .Range("P56") = Page_Num + (I - 2) * 2
     .Range("R56") = Total_page
     .Range("M55") = Sheets("封面").Range("d7")
     .Name = I - 2
End With

10. 其他

Application.DisplayStatusBar = False '关闭状态栏
ActiveSheet.DisplayPageBreaks = False '禁用分页符