Excel VBA自动过滤器阵列 [英] Excel VBA AutoFilter Array

问题描述

我正在使用带有Microsoft Excel的VBA中的AutoFilter。我有一个问题，它如何处理数组进行过滤;我已经将我的原始环境浇灌到一个简化版本（原本是希望了解这个问题）：

在范围A1：A5的工作表中，假设我们有水果， Apple ，香蕉，橙色和 Pear 。自动过滤器已被应用，以便 Fruit 是列标题。

运行以下代码返回预期结果 Apple ，香蕉和橙色但不是 Pear ）：

 范围（A1）选择
 ActiveSheet .Range（A1：A5）。AutoFilter字段：= 1，_ 
 Criteria1：= Array（= * an *，= * app *），运算符：= xlFilterValues 
  

在我正在处理的项目中，过滤条件以String变量的形式传递，其作用如上。问题是每次都不应用每个标准，所以其中一些应该没有效果。

例如：

  Dim A As String，B As String，C As String 
 A == * an *
 B = Empty 
 C = = * ap *
 
范围（A1）。选择
 ActiveSheet.Range（A1：A5）。AutoFilter字段：= 1，_ 
条件1： = Array（A，B，C），运算符：= xlFilterValues 
  

With B 抛出混合，过滤不返回任何记录（无论是否为空，设置为空，或使用通配符，如 = * ）。然而，在实际条件数组中替换 B 与空（硬编码）返回预期结果。 >

我以前使用过类似的代码（并且它有效），尽管是一个属于ListObject的AutoFilter。在这一点上，我唯一可以想到的是将过滤器连接到一个带有分隔符的String中，并将其分解成一个Array变量（因此它是精确的大小，因为集合中的一个未设置的项目以相同的方式混淆一个标准变量）。但是这似乎是不直观和麻烦的。

我在这里缺少一些明显的东西？

解决方案>

我不能想到这样做，不涉及测试空参数，这里有一种可能适用于您的方法，也可以防止重复的表达式。

  Sub Main（）
 Dim a As String 
 Dim B As String 
 Dim C As String 
 Dim filterCriteria as变量
a == * an *
 B =空
 C == * ap *
 filterCriteria = CombineArrays（Array（a，B，C））
 
如果不是uBound（filterCriteria）= -1然后
 
范围（A1）。选择
 ActiveSheet.Range（A1：A5）。AutoFilter字段： = 1，_ 
 Criteria1：= filterCriteria，运算符：= xlFilterValues 
 
 End If 
 End Sub 
 
函数CombineArrays（arr As Variant）As Variant 
 Dim a As Variant 
 Dim filterDic As Object'Scripting.Dictionary 
设置filterDic = CreateObject（Scripting.Dictionary）
 
对于每个a在arr 
如果不是filterDic.Exists（a）而不是a = vbNullString然后
 filterDic.Add a，a 
 End If 
 Next 
 
 CombineArrays = filterDic.Keys 
 
设置filterDic = Nothing 
结束函数
  

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):

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 Fruit is a column header.

Running the below code returns the expected results (Apple, Banana, and Orange but not 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 B thrown into the mix, filtering returns no records (whether it's left null, set to Empty, or uses wildcards like =*). Replacing B with Empty (hard-coded) in the actual criteria array returns the expected result, however.

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.

Am I missing something obvious here?

解决方案

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

这篇关于Excel VBA自动过滤器阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！