Excel VBA：性能(Excel VBA Performance)-iaspnetcore.com

Table of Content

使用高版本 office

代码运行时禁用除必要功能之外的所有其他功能

Minimize data exchange between Worksheet and VBA code

1.使用高版本 office

在 Excel 2013 中，复制/粘贴包含条件格式的单元格可能速度很慢。 Excel 2016 内部版本 16.0.8229.0 中已显著改进了这一点。

一项对包含共 386,000 个条件格式规则的 44,000 个单元格进行复制的示例测试展现出了明显的改进：

Excel 2010：70 秒
Excel 2013：68 秒
Excel 2016：7 秒

2.代码运行时禁用关闭所有Excel功能（除必要功能之外的所有其他功能）

Here’s some sample code that shows how and what to shut off while your code runs. Doing this should help improve the performance of your code:

‘Get current state of various Excel settings; put this at the beginning of your code

screenUpdateState = Application.ScreenUpdating

statusBarState = Application.DisplayStatusBar

calcState = Application.Calculation

eventsState = Application.EnableEvents

displayPageBreakState = ActiveSheet.DisplayPageBreaks ‘note this is a sheet-level setting

‘turn off some Excel functionality so your code runs faster

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False ‘note this is a sheet-level setting

‘>>your code goes here<<

‘after your code runs, restore state; put this at the end of your code

Application.ScreenUpdating = screenUpdateState

Application.DisplayStatusBar = statusBarState

Application.Calculation = calcState

Application.EnableEvents = eventsState

ActiveSheet.DisplayPageBreaks = displayPageBreaksState ‘note this is a sheet-level setting

3.Minimize data exchange between Worksheet and VBA code

3.1 Read/Write Large Blocks of Cells in a Single Operation

Slower

Here’s a slow, looping method:

Dim DataRange as Range
Dim Irow as Long
Dim Icol as Integer
Dim MyVar as Double
Set DataRange=Range(“A1:C10000”)

For Irow=1 to 10000
  For icol=1 to 3
    MyVar=DataRange(Irow,Icol)  ‘Read values from the Excel grid 30K times
    If MyVar > 0 then 
      MyVar=MyVar*Myvar ‘ Change the value 
      DataRange(Irow,Icol)=MyVar  ‘Write values back into the Excel grid 30K times
    End If 
  Next Icol
Next Irow

faster

Here’s the fast version of that code:

Here’s the fast version of that code:

Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As Double
DataRange = Range(“A1:C10000”).Value ‘ read all the values at once from the Excel grid, put into an array

For Irow = 1 To 10000
  For Icol = 1 To 3
  MyVar = DataRange(Irow, Icol)
  If MyVar > 0 Then
    MyVar=MyVar*Myvar ‘ Change the values in the array
    DataRange(Irow, Icol) = MyVar
  End If
Next Icol
Next Irow
Range(“A1:C10000”).Value = DataRange ‘ writes all the results back to the range at once

3.2 Avoid Selecting / Activating Objects

Using the slower “selection” method, the code looks like this:

For i = 0 To ActiveSheet.Shapes.Count

   ActiveSheet.Shapes(i).Select

   Selection.Text = “Hello”

Next i

The much faster method is to avoid selection completely and directly reference the shape:

For i = 0 To ActiveSheet.Shapes.Count

   ActiveSheet.Shapes(i).TextEffect.Text = “Hello”

Next i

This example copies data from cells C1:C5 on Sheet1 to cells D1:D5 on Sheet1.

Worksheets("Sheet1").Range("C1:C5").Copy 
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("D1:D5")

Useful links

1.Excel VBA Performance - 1 million rows - Delete rows containing a value, in less than 1 min
Ask Question

https://code-examples.net/zh-CN/q/1d866d3（cn）

https://stackoverflow.com/questions/30959315/excel-vba-performance-1-million-rows-delete-rows-containing-a-value-in-less （en）

25 Optimize VBA Code for performance improvement

https://vbacompiler.com/optimize-vba-code/