Excel VBA解读（144）： 使用Application事件和缓存将更快地获取已使用单元格区域
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倍！