Excel VBA解读（143）： 在自定义函数中使用整列引用时，如何更有效率？

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

excelperfect

Excel用户经常发现在公式中使用整列的引用很方便，这样可避免每次添加新数据时都必须调整公式。因此，当编写用户自定义函数时，可能会使用：

=MyUDF(A:A,42)

当Excel 2007引入了超过100万行的“大网格”时，有效处理这些整列引用变得更加重要。在VBA用户自定义函数中处理此问题的标准方法是获取整列引用和已使用单元格区域的交叉区域，以便用户自定义函数只需处理实际使用的整列的一部分。下面的示例VBA代码处理交叉区域，然后返回输入区域中的行数和已使用区域中的行数的较小者。

Public Function GetUsedRows(theRngAs Range)
   Dim oRng As Range
   Set oRng = Intersect(theRng, theRng.Parent.UsedRange)
   GetUsedRows = oRng.Rows.Count
End Function

代码中，变量theRng的Parent属性返回包含其指向的单元格的工作表，因此，theRng.Parent.UsedRange获取工作表已使用的单元格区域。

这里存在两个问题：

• 获取已使用单元格区域可能很慢。
• XLL接口没有直接访问已使用单元格区域的方法，因此必须通过单线程锁定的COM调用来获取它。

获取已使用单元格区域的速度有多慢？试试下面的代码：

Public Function CountUsedRows()
   CountUsedRows = ActiveSheet.UsedRange.Rows.Count
End Function

当已使用的单元格区域很小时，不会注意到所花费的时间，但对于具有大量已使用单元格区域，肯定会感觉到其速度变量了。问题是自定义函数会对传递给其的每个单元格区域进行检查，即使它不是真正需要的。

影响运行时间的实际上是包含数据或格式（或以前包含数据或格式）的单元格数量，而不是已使用单元格区域中的最后一个单元格。

因此，当theRng参数具有大量行时，可以从仅执行已使用单元格区域的检查开始：

Public Function GetUseRows2(theRngAs Range)
   Dim oRng As Range
   If theRng.Rows.Count > 500000 Then
        Set oRng = Intersect(theRng,theRng.Parent.UsedRange)
        GetUseRows2 = oRng.Rows.Count
   Else
        GetUseRows2 = theRng.Rows.Count
   End If
End Function

代码仅检查用户是否为自定义函数指定了超过50万行的单元格区域。

另一种更复杂的最小化执行时间的方法是将已使用单元格区域内的行数存储在某个缓存中，并在需要时从缓存中检索它。其中最难的部分是确保已使用单元格区域行缓存总是为空（在这种情况下去获取数字）或包含最新数字。

执行此操作的一种方法是使用Application对象的AfterCalculate事件（在Excel 2007中引入）清空缓存。然后，只有为每个工作表请求已使用单元格区域的第一个用户自定义函数使用时间来查找已使用的单元格区域，并且（假设计算本身不会改变已使用的单元格区域）将总是检索正确的数字。

Excel 2007之前的Excel版本的等效方法是使用Application对象的SheetCalculate事件清空该特定工作表的缓存。这种技术的效率较低，因为在每个计算周期中工作表可能被计算几次。

如果想找到包含数据的最后一行，当有许多包含数据的单元格时，使用Range.Find会更快。注意，只能在Excel 2002及更高版本的用户自定义函数中使用Range.Find，并且除了命令宏或COM之外，不能在XLL中使用Find方法。

Public Function CountUsedRows2()
   CountUsedRows2 = ActiveSheet.Cells.Find(What:="*", _
              LookIn:=xlFormulas,SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
End Function