比较日期范围 Excel VBA

Comparing Date Range Excel VBA

提问by Trevor Eyre

UPDATED WITH ANSWER:I found an answer to my question. I'll place a simplified version below the original question here, just to make it easy, with a more detailed version below as the accepted answer.

更新答案：我找到了我的问题的答案。为了方便起见，我将在原始问题下方放置一个简化版本，下面提供更详细的版本作为已接受的答案。

I'm writing a VBA function to filter a pivot table by a date range entered by the user. For some reason, it is not returning the results expected.

我正在编写一个 VBA 函数来按用户输入的日期范围过滤数据透视表。出于某种原因，它没有返回预期的结果。

The dates being compared are in two different date formats. The user-entered dates are in mmmm yyyy format (October 2013). When this value is pulled into the macro for comparison, it is translated correctly as 10/1/2013. The pivot table dates are in mmm yy format (Oct 13). When I call on this date with the code PivotItem.Valueit seems to be translating the date as a string "Oct 13."

被比较的日期采用两种不同的日期格式。用户输入的日期采用 mmmm yyyy 格式（2013 年 10 月）。当这个值被拉入宏进行比较时，它被正确地转换为 10/1/2013。数据透视表日期采用 mmm yy 格式（10 月 13 日）。当我用代码调用这个日期时，PivotItem.Value它似乎将日期转换为字符串“Oct 13”。

I can't quite figure out what the macro is doing, as it behaves somewhat erratically. If I run it for October 2011 to October 2013, it returns all months from Jan to September for every year, 2008, 2009, 2010, etc. If I run it for June 2013 to October 2013, it returns June to September for every year. Furthermore, in each example, the macro continues to run past the maximum range of data in the pivot table and gets an error. When I debug, the macro is trying to set the visibility to 'true' for a date that doesn't even exist in the pivot table (IE for Jan 2014 when the data only goes through Oct 2013). No idea why that's happening.

我不太清楚宏在做什么，因为它的行为有些不规律。如果我在 2011 年 10 月到 2013 年 10 月运行它，它会返回每年 1 月到 9 月的所有月份，2008、2009、2010 年等。如果我在 2013 年 6 月到 2013 年 10 月运行它，它会返回每年的 6 月到 9 月. 此外，在每个示例中，宏继续运行超过数据透视表中的最大数据范围并出现错误。当我调试时，宏试图将数据透视表中甚至不存在的日期的可见性设置为“true”（即 2014 年 1 月的 IE，当数据仅通过 2013 年 10 月时）。不知道为什么会这样。

Below is the code. Any insight would be greatly appreciated.

下面是代码。任何见解将不胜感激。

UPDATE:

更新：

So the problem is definitely the date format. If I change the field settings in the pivot table to the date format mm/dd/yyyy (10/1/2013), then the macro works exactly as expected. This would be a simple fix to the problem exceptthat the table is feeding a chart seen in the user dashboard, which I would reallylike to be in the format mmm yy, since it looks much cleaner. Is there a simple way to convert the format to mm/dd/yyyy inside the macro for the comparison, then back to the desired format once complete?

所以问题肯定是日期格式。如果我将数据透视表中的字段设置更改为日期格式 mm/dd/yyyy (10/1/2013)，则宏完全按预期工作。这将是对该问题的一个简单解决方案，除了表格正在提供用户仪表板中看到的图表，我真的希望采用 mmm yy 格式，因为它看起来更干净。是否有一种简单的方法可以在宏内部将格式转换为 mm/dd/yyyy 以进行比较，然后在完成后返回所需的格式？

And I would still like to understand why a different date format is returning such different results, when the raw data being compared is the same, and both are formatted as dates, not like date vs text or something.

而且我仍然想了解为什么不同的日期格式会返回如此不同的结果，当被比较的原始数据是相同的，并且两者都被格式化为date，而不是 date vs text 或其他东西。

Sub filterPivotDate(pt As PivotTable, strtDate As Date, endDate As Date)

Dim pf As PivotField
Dim pi As PivotItem

'Clear current date filter
Set pf = pt.PivotFields("Date")
pf.ClearAllFilters

'Set new date filter
For Each pi In pf.PivotItems
    If pi.Value >= strtDate And pi.Value <= endDate Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next pi

end sub

ANSWER UPDATE:I replaced the loop I was using to set the filter with the following line of code:

答案更新：我用以下代码行替换了我用来设置过滤器的循环：

pf.PivotFilters.Add Type:=xlDateBetween, Value1:=strtDate, Value2:=endDate

This solved the issue I was having with the date format. More information in the accepted answer below, as well as at this website

这解决了我在日期格式方面的问题。下面接受的答案以及本网站中的更多信息

采纳答案by Trevor Eyre

I found some additional information about setting pivot table filters via VBA that improves upon the original code and solves the issue I was having. I decided to post it, as I found the information extremely helpful.

我发现了一些有关通过 VBA 设置数据透视表过滤器的附加信息，这些信息改进了原始代码并解决了我遇到的问题。我决定发布它，因为我发现这些信息非常有用。

There are a lot of different filters you can set with simple, one line commands, rather than complicated loops which parse all the data and set visibility manually based on a condition as I was doing before. Below is the updated code, which also solved the issue I was having with the date formatting.

您可以使用简单的单行命令设置许多不同的过滤器，而不是像我之前所做的那样解析所有数据并根据条件手动设置可见性的复杂循环。下面是更新后的代码，它也解决了我在日期格式方面遇到的问题。

There is some really useful information at this link to the globaliconnect websiteabout different pivot table filter settings you can set using VBA.

在 globaliconnect 网站的这个链接上有一些非常有用的信息，关于您可以使用 VBA 设置的不同数据透视表过滤器设置。

I'm still not certain why the date was behaving weird before when doing the comparison in a loop, but dates are just kinda like that I guess...

我仍然不确定为什么在循环中进行比较时日期表现得很奇怪，但我想日期有点像......

Sub filterPivotDate(pt As PivotTable, strtDate As Date, endDate As Date)
   Dim pf As PivotField
   Application.ScreenUpdating = False

   'Clear date filter and set new filter
   Set pf = pt.PivotFields("Date")
   pf.ClearAllFilters

   'I REPLACED THE LOOP WITH A SINGLE LINE TO SET A FILTER BETWEEN TWO DATES
   pf.PivotFilters.Add Type:=xlDateBetween, Value1:=strtDate, Value2:=endDate

   Application.ScreenUpdating = True
End Sub

回答by craig.white

I might be wrong, but one of your < characters looks backwards- don't you want start dates Greater than the variable?

我可能错了，但是您的 < 字符之一向后看-您不希望开始日期大于变量吗？

回答by spojam

You could try converting the string date value first:

您可以先尝试转换字符串日期值：

Dim dateValue as Date

'Set new date filter on all pivot tables
For Each pt In ws.PivotTables
    Set pf = pt.PivotFields("Date")

    For Each pi In pf.PivotItems

        If IsDate(pi.Value) Then
            dateValue = CDate(pi.Value)
            If dateValue < strtDate Or dateValue > endDate Then
                pi.Visible = False
            Else
                pi.Visible = True
            End If
        End If
    Next pi

Next pt

'call this function
Function IsDate(byval thisDateString as String) As Boolean
    On Error Goto ErrorHandler

    Dim d as Date
    d = CDate(thisDateString)
    IsDate = true
    Exit Function

ErrorHandler:
    IsDate = false
End Function