Excel VBA解读（144）： 使用Application事件和缓存将更快地获取已使用单元格区域

学习Excel技术，关注微信公众号：

excelperfect

在上一篇文章中，建议加速检索已使用单元格区域最后一行方法之一是使用缓存和Application对象的AfterCalculate事件。

下面是演示代码：

'为缓存创建模块级数组

Dim UsedRows(1 To 1000, 1 To 2) AsVariant

Public Function GetUsedRows3(theRngAs Range)

'对于Excel 2007及以后的版本,存储并获取已使用的单元格行数

Dim strBookSheet As String

Dim j As Long

Dim nFilled As Long

Dim nRows As Long

'为此工作簿和工作表创建标签

strBookSheet = Application.Caller.Parent.Parent.Name & "_"& _

Application.Caller.Parent.Name

If Val(Application.Version) >= 12 Then

'查看缓存

For j = LBound(UsedRows) ToUBound(UsedRows)

If Len(UsedRows(j, 1)) > 0 Then

nFilled = nFilled + 1

If UsedRows(j, 1) =strBookSheet Then

'找到了

GetUsedRows3 = UsedRows(j,2)

Exit Function

End If

Else

'如果第一行是空行则退出循环

Exit For

End If

Next j

End If

'找到已使用的行数

nRows = theRng.Parent.UsedRange.Rows.Count

If Val(Application.Version) >= 12 Then

'在缓存中存储

nFilled = nFilled + 1

If nFilled <= UBound(UsedRows) Then

UsedRows(nFilled, 1) = strBookSheet

UsedRows(nFilled, 2) = nRows

End If

End If

GetUsedRows3 = nRows

End Function

Sub ClearCache()

'清空已使用单元格区域缓存的第一行

UsedRows(1, 1) = ""

End Sub

注意，在此代码中包含错误处理语句！

首先声明一个包含1000行和2列的模块级别数组（UsedRows）。每行将在第1列（工作簿名称和工作表名称）中保存一个键，并在第2列中保存该工作簿中该工作表已使用单元格区域中的行数。假设只缓存包含这些用户自定义函数的前1000个工作表！

键或标签是通过将调用单元格的父级名称（即工作表）与调用单元格的父级名称的父级名称（包含该工作表的工作簿）连接而创建的。

然后循环该UsedRows数组查找键，但在第一个空行时退出循环。

如果找到键，则从第2列检索已使用单元格区域内的行数，将其作为函数的结果返回并退出该函数。

否则，查找已使用单元格区域中的行数，将其存储在UsedRange高速缓存的下一行中，并将其作为函数的结果返回。

该函数仅可在Excel 2007及更高版本中操作缓存。有两个原因：

1.Excel 2003及更早版本最多有65536行，因此无论如何找到已使用单元格区域相对较快。

2.只有Excel 2007及更高版本有AfterCalculate事件，该事件将在每次计算后用于清空缓存。

需要在每次计算后清空缓存，因为用户可能会改变已使用的单元格区域，因此安全的做法是在每次计算时重新创建缓存。AfterCalculate是一个应用程序级事件，在完成计算和相关查询和刷新后触发。（BeforeCalculate事件会更有用，但不存在！）

使用Application对象的AfterCalculate事件

首先，我添加了一个名为AppEvents的类模块，代码如下：

Private WithEvents App As Application

Private Sub Class_Initialize()

Set App = Application

End Sub

Private Sub App_AfterCalculate()

ClearCache

End Sub

在ThisWorkbook模块中添加代码：

Private XLAppEvents As AppEvents

Private Sub Workbook_Open()

Set XLAppEvents = New AppEvents

End Sub

这将设置应用程序级事件所需的挂钩。这么多的代码只是为了在每次计算后运行ClearCache过程！ClearCache只清空缓存中的第一个键，以便GetUsedRows3中的查找循环立即退出。

注意，Excel 2003及更早版本会忽略此代码：由于AfterCalculate事件不存在，因此它永远不会被调用，但仍然可以通过编译。

GetUsedRows3的执行效率

对于640K行数据，对GetUsedRows3的1000次调用只需要66毫秒，而上一篇文章中原始的CountUsedRows函数要花33秒。快500倍！