在VBA中筛选数据范围

Question

我在使用VBA中的筛选范围时遇到了问题。如果我使用宏来筛选范围，然后再使用另一个来调用Subtotal工作表函数，在第一列中找到最小值，我会得到期望的筛选范围最小值；这正是我想要的。如下所示：

Sub TestFilterTrans()
    Call FilterTrans("111Wall")
End Sub

Sub TestMinDate()
    MsgBox MinTransDate()
End Sub

Sub FilterTrans(Inv As String)
    Range("TransLabels").Select
    Selection.AutoFilter
    ActiveSheet.Range("Trans").AutoFilter Field:=2, Criteria1:=Inv
End Sub

Function MinTransDate()
    MinTransDate = Application.WorksheetFunction.Subtotal(5,     Worksheets("Transactions").Range("TransDates"))
End Function

然而，如果我从这张表中调用一个函数来首先调用筛选过程，然后再调用一个函数来查找最小值，它会选择整个范围内的最小值，就好像没有进行筛选一样，如下所示：

Function IncepDate(Inv As String)
    Dim d As String
    Call FilterTrans(Inv)
    d = MinTransDate()
    Call FilterOff
    IncepDate = d
End Function

有人能告诉我为什么在第二种情况下过滤器似乎不起作用吗？

- user1787425

---

通常，函数只返回一个值，并不对工作表进行其他更改。您可能需要使用子程序而不是函数来操作筛选器。 - Gary's Student

我认为“from a sheet”是这个问题的根源。请看下面我的答案 :) - David Zemens

Answer 1

一个函数不应该操作工作表对象。这个话题在这里有几个问题，但共识是从工作表函数中调用子例程来操纵工作表对象是一个坏主意。

我通过一些类似的代码进行了步骤，试图复制这个问题。

代码执行时没有错误，但如果你跟踪，你会发现自动筛选器实际上从未应用。

我相当确定这是设计上的，尽管一个错误/警告消息会很好。

我在类似问题的以前评论中所说的: Excel VBA 2010功能中单元格引用无法工作 不可能从工作表调用的UDF中操纵工作表单元格。 更多信息在这里:

https://stackoverflow.com/a/15647054/1467082

这里是：

http://www.excel-it.com/UDF.htm

通常情况下，子程序可以操作工作表，而函数则不能。

唯一的例外是，有时从子程序中调用的函数可以这样做。然而，将函数用于除返回值到子程序之外的任何其他目的都可能是一种不好的习惯。

此外，微软提供了大量关于此事的文档资料。

http://support.microsoft.com/kb/170787

在工作表单元格中由公式调用的用户定义函数无法更改 Microsoft Excel 的环境。这意味着这样的函数不能执行以下任何操作：

- 在电子表格上插入、删除或格式化单元格。 - 更改其他单元格的值。 - 移动、重命名、删除或添加工作簿中的工作表。 - 更改任何环境选项，例如计算模式或屏幕视图。 - 向工作簿添加名称。 - 设置属性或执行大多数方法。

用户定义函数的目的是允许用户创建一个未包含在 Microsoft Excel 中的自定义函数。Microsoft Excel 中包含的函数也不能更改环境。函数可以执行计算并将其返回到输入它们的单元格中的值或文本。任何环境更改都应通过使用 Visual Basic 子例程来完成。

在计算过程中，Excel 会检查包含用户定义函数的单元格的前导项。如果在计算过程中尚未计算所有前导项，则最终会调用用户定义函数并向函数传递 Null 或空单元格。然后，Excel 确保进行足够的计算传递，以便计算所有前导项。在最终计算传递期间，用户定义函数被传递单元格的当前值。这可能会导致比预期更频繁地调用用户定义函数，并出现意外的参数。因此，用户定义函数可能返回意外的值。

为了正确计算，所有用于计算的区域都应作为参数传递给函数。如果您不将计算区域作为参数传递，而是在函数的 VBA 代码中引用这些区域，则 Excel 无法在计算引擎内考虑它们。因此，Excel 可能无法充分计算工作簿以确保在计算用户定义函数之前计算所有前导项。

- David Zemens

---

根据上面的答案，我将调用从函数改为子程序，这解决了问题。 - user1787425

请考虑将此答案标记为“已接受”，因为它已经解决了您的问题。 - David Zemens