Excel VBA保存带有日期的自动筛选器设置 [英] Excel VBA Saving autofilter settings with dates

问题描述

在清除过滤器之前，我有一部分宏用于存储自动过滤器设置，因此以后可以重新应用它们.代码如下:

I have a part of a macro that stores the autofilter settings before clearing the filter, so it can reapply them later. The code is as follows:

Dim FilterArray()
With FrontPage.AutoFilter.Filters
    ReDim FilterArray(1 To .Count, 1 To 3)
    For f = 1 To .Count
        With .Item(f)
            If .On Then
                FilterArray(f, 1) = .Criteria1
                If .Operator Then
                    FilterArray(f, 2) = .Operator
                    If .Operator = xlAnd Or .Operator = xlOr Then
                        FilterArray(f, 3) = .Criteria2
                    End If
                End If
            End If
        End With
    Next f
End With

在我遇到的每种情况下(日期字段除外)，此方法都可以正常工作.对于日期字段Criteria1和Criteria2，会导致错误(应用程序定义的错误或对象定义的错误)，而Operator会返回值7.这在Excel 2007中发生，在Excel 2013中仍然发生.

This works fine in every situation I have come across except for date fields. In the case of date fields, Criteria1 and Criteria2, cause errors (Application-defined or object-defined error), while Operator returns a value of 7. This happened in Excel 2007 and is still happening in Excel 2013.

我认为不起作用的原因是自动过滤器处理日期字段的嵌套方式，但是有没有办法使日期与日期一起使用呢?我还看到了将过滤器应用于日期的其他问题，但是确定将它们存储在首位应该更容易吗?

I assume the reason this doesn't work is due to the nested way the autofilters treat date fields, but is there a way to make this work with dates? I have seen other problems to do with applying filters to dates, but surely storing them in the first place should be easier?

第二个问题-运算符7的值是什么意思?我只能找到值0-2的转换.

And a secondary question - what does the value of 7 mean for Operator? I can only find the translation of values 0-2.

推荐答案

我发现，要在Array中启用过滤器Date.

I found out, to enable filter Date in Array.

以下设置必须禁用(默认情况下启用)

Below setting must be disable (Enable by default)

ActiveWindow.AutoFilterDateGrouping = False

这篇关于Excel VBA保存带有日期的自动筛选器设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！