KB0070：为什么启用 thinkcell 后，我的 Excel 宏运行缓慢？

  1. Home
  2. 资源
  3. 知识库
  4. KB0070

VBA 宏中一个常见的性能问题来源是使用 .Select 函数。每次在 Excel 中选择一个单元格时，每个 Excel 加载项（包括 thinkcell）都会收到此选择更改事件的通知，这会显著降低宏的运行速度。使用宏录制器创建的宏尤其容易出现这类问题。

Microsoft 也建议在 VBA 代码中避免使用 .Select 语句，以提升性能：

  • MSDN：Improving Performance in Excel 2007："Reference Excel objects such as Range objects directly, without selecting or activating them"（位于 "Faster VBA Macros" 部分）。

示例：如何避免使用 .Select 语句

我们来看下面这个简单的宏 AutoFillTable：

Sub AutoFillTable()
    Dim iRange As Excel.Range
    Set iRange = Application.InputBox(prompt:="输入范围", Type:=8)
    Dim nCount As Integer
    nCount = iRange.Cells.Count
    For i = 1 To nCount
        Selection.Copy
        If iRange.Cells.Item(i).Value = "" Then
            iRange.Cells.Item(i).Range("A1").Select
            ActiveSheet.Paste
        Else
            iRange.Cells.Item(i).Range("A1").Select
        End If
    Next
End Sub

此函数会打开一个输入框，要求用户指定单元格范围。 该函数会遍历该范围内的所有单元格。 如果找到非空单元格，它会将该单元格内容复制到剪贴板。 该函数会将剪贴板内容粘贴到后续每个空单元格中。

AutoFillTable 使用剪贴板复制单元格内容。 因此，该函数需要选择每个被操作的单元格，以便 Excel 知道从哪个单元格复制，以及应粘贴到哪个单元格。 推荐的解决方案如下方函数 AutoFillTable2 所示：

Sub AutoFillTable2()
    Dim iRange As Excel.Range
    Set iRange = Application.InputBox(prompt:="输入范围", Type:=8)
    Dim iCellMaster As Excel.Range
    For Each iCell In iRange.Cells
        If iCell.Value = "" Then
            If Not iCellMaster Is Nothing Then
                iCellMaster.Copy (iCell)
            End If
        Else
            Set iCellMaster = iCell
        End If
    Next iCell
End Sub

AutoFillTable2 与前一个函数有两个重要区别：

  1. 最重要的是，它不再需要选择单元格。 相反，当找到非空单元格时，该单元格会被存储在变量 iCellMaster 中。 之后，每当找到空单元格时，iCellMaster 的所有内容都会被复制到 iCell 中。
  2. 它使用 Visual Basic 语言功能 For Each … Next 来访问该范围内的每个单元格。 这本身已经提升了可读性。