Excel VBA 自动筛选数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18649275/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA AutoFilter Array
提问by Cameron Sumpter
I'm working with AutoFilters in VBA with Microsoft Excel. I'm having an issue with how it handles arrays for filtering; I've watered down my original context to a simplified version (originally in hopes of understanding the issue):
我正在使用 Microsoft Excel 在 VBA 中使用自动筛选器。我在处理用于过滤的数组时遇到了问题；我已经将我的原始上下文淡化为一个简化版本（最初是为了理解这个问题）：
In a worksheet in Range A1:A5, let's say we have Fruit, Apple, Banana, Orange, and Pear, respectively. An AutoFilter has been applied such that Fruitis a column header.
在范围A1工作表：A5，让我们说我们有Fruit，Apple，Banana，Orange，和Pear分别。已应用自动筛选器，Fruit即列标题。
Running the below code returns the expected results (Apple, Banana, and Orangebut not Pear):
运行下面的代码返回预期的结果（Apple，Banana，和Orange而不是Pear）：
Range("A1").Select
ActiveSheet.Range("A1:A5").AutoFilter Field:=1, _
Criteria1:=Array("=*an*", "=*app*"), Operator:=xlFilterValues
In the project I'm working on, the filter criteria are passed in as String variables, which works as the above. The issue is that not every criterion is applied every time, so some of them should have no effect.
在我正在处理的项目中，过滤条件作为字符串变量传入，其工作方式与上述相同。问题是并非每次都应用所有标准，因此其中一些标准应该无效。
For example:
例如：
Dim A As String, B As String, C As String
A = "=*an*"
B = Empty
C = "=*ap*"
Range("A1").Select
ActiveSheet.Range("A1:A5").AutoFilter Field:=1, _
Criteria1:=Array(A, B, C), Operator:=xlFilterValues
With Bthrown into the mix, filtering returns no records (whether it's left null, set to Empty, or uses wildcards like =*). Replacing Bwith Empty(hard-coded) in the actual criteria array returns the expected result, however.
随着B扔进组合，筛选返回任何记录（无论是左零，设置为Empty，或使用通配符类似=*）。然而，在实际标准数组中替换B为Empty(hard-coded) 会返回预期的结果。
I've used similar code in the past (and had it work), albeit with an AutoFilter that was part of a ListObject. At this point, the only thing I can think of is concatenating the filters into a String with delimiters and splitting it into an Array variable (so that it's the precise size, since an un-set item in the collection messes that up the same way a standard variable does). But that seems unintuitive and cumbersome.
我过去使用过类似的代码（并让它工作），尽管使用的是 ListObject 的一部分的 AutoFilter。在这一点上，我唯一能想到的就是将过滤器连接成一个带有分隔符的字符串并将其拆分成一个数组变量（这样它就是精确的大小，因为集合中的一个未设置的项目会以同样的方式弄乱它）标准变量确实如此）。但这似乎不直观且麻烦。
Am I missing something obvious here?
我在这里遗漏了一些明显的东西吗？
回答by David Zemens
I can't think of a way to do this that doesn't involve testing for empty parameters, here is one approach that might work for you, and also prevents duplicate expressions.
我想不出一种不涉及测试空参数的方法，这是一种可能对您有用的方法，并且还可以防止重复表达式。
Sub Main()
Dim a As String
Dim B As String
Dim C As String
Dim filterCriteria as Variant
a = "=*an*"
B = Empty
C = "=*ap*"
filterCriteria = CombineArrays(Array(a, B, C))
If Not uBound(filterCriteria) = -1 Then
Range("A1").Select
ActiveSheet.Range("A1:A5").AutoFilter Field:=1, _
Criteria1:=filterCriteria, Operator:=xlFilterValues
End If
End Sub
Function CombineArrays(arr As Variant) As Variant
Dim a As Variant
Dim filterDic As Object 'Scripting.Dictionary
Set filterDic = CreateObject("Scripting.Dictionary")
For Each a In arr
If Not filterDic.Exists(a) And Not a = vbNullString Then
filterDic.Add a, a
End If
Next
CombineArrays = filterDic.Keys
Set filterDic = Nothing
End Function
