• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need some people to test some code to track down possible regional issue

jeffreyweir

jeffreyweir

Active Member
Howdy folks.
I wrote a post some time back at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems highlighting a problem I had whereby if I tried to change the .visible status of a PivotItem that happenned to be a date - and that if the PivotField had a number format set to General rather than a date format - then I was getting the following error:
Run-time error ’1004′:
Unable to set the Visible property of the PivotItem class

But someone just commented at that thread that they don't have the same issue on either Excel 2010 or 2013. Whereas I find on my own machine that it works fine in Excel 2013 but not Excel 2010.

I’m wondering if it’s some kind of regional issue and version issue.

I'm looking for a few kind souls to run the following code on a blank workbook, and see what happens. It just sets up a pivot with two items in it, then tries to make both those items visible - first with the number format set to a date format, and then with the number format set to 'General'

I just need to know whether the code runs fine, or (if not) what line it throws an error on. I also need to know what version(s) of Excel you ran it on, and what international version of Excel you have.

Many thanks

Jeff

Code: 
Sub Test()

Dim pf As PivotField
Dim pc As PivotCache
Dim pt As PivotTable
Dim pi As PivotItem
Dim rng As Range

[A1].Value = "Data"
[A2].Value = "=TODAY()"
[A3].Value = "=VALUE(TODAY())"

Set rng = Cells(ActiveSheet.UsedRange.Row, ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column + 1)

Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=[A1].CurrentRegion)
Set pt = pc.CreatePivotTable(TableDestination:=rng)
Set pf = ActiveSheet.PivotTables(1).PivotFields(1)


pf.NumberFormat = "d/mm/yyyy"
For Each pi In pf.PivotItems
pi.Visible = True
Next pi

pf.NumberFormat = "General"
For Each pi In pf.PivotItems
pi.Visible = True 'Code errors out for me here using Excel 2010
Next pi

End Sub


For me, in Excel 2013 this runs fine, but in Excel 2010 errors out where indicated in the comment.

Cheers

Jeff
 
Hi Jeff,

When I tested your code in Excel 2010 with English (India) regional setting it gave error 1004. Then I switched to English (US) and code executed fine.

Regards,
 
Thanks Somendra.

Narayan - my understanding is that this bug also affects Excel 2007. IF you're feeling particularly generous, is there any chance you could change your windows regional setting to UK or NZ and rerun the code?
 
Hi Jeff ,

I tried it out by changing my regional settings to UK , and the problem comes up.

I see that the problem is only with the date pivotitem ; A2 has a value which is of type DATE , while A3 has a value which is of type DOUBLE ; it is the former which is giving the problem , and this is independent of the format statement ; I commented out both the following statements :

pf.NumberFormat = "d/mm/yyyy"

pf.NumberFormat = "General"

and the problem comes up ; when I use the statement :

?pi.Visible

in the Immediate window , I get a Type Mismatch error.

The first NumberFormat ( which is highlighted ) is essential ; the second is unnecessary , and if present , gives problems.

With my Windows regional settings as English ( US ) , there is no problem at all.

Narayan
 
Thanks Narayan. Note that while the problem rears it's head when the PivotItem is a date and the PivotField format is 'General', the problem isn't the use of the General format itself, but rather is a bad bug in Excel. I was just trying to confirm under what circumstances that bug happens. So my code was designed to show that the combination of General format on a PivotField and date for a PivotItem and non-US regional setting and either Excel 2007 or Excel 2010 causes this bug to express itself.

Regarding your comment: The first NumberFormat ( which is highlighted ) is essential ; the second is unnecessary , and if present , gives problems.

The 'General' number format itself isn't 'unneccessary' in the wild...in many cases it's actually mandatory. For instance, if you create a pivot out of some data that has some blank cells in a Dates column, then Excel forces the number format to be General, and users can't change it. At which time if users try to use code to change (or even read) the .Visible status, they get a Type Mismatch error.

My testing shows that this appears to now be fixed in Excel 2013.

Thanks again, pal.
 
Hi Jeff ,

Sorry but I am not able to understand the explanation ; you say :
if you create a pivot out of some data that has some blank cells in a Dates column, then Excel forces the number format to be General, and users can't change it.
Click to expand...
I can understand that Excel forces the number format to be General , and users cannot change it ; what I don't understand is what is the connection between this statement , and the one which precedes it viz.
if you create a pivot out of some data that has some blank cells in a Dates column
Click to expand...
Your test code is not creating a pivot out of data that has some blank cells in a Dates column , or is it ?

Second , you say :
At which time if users try to use code to change (or even read) the .Visible status, they get a Type Mismatch error.
Click to expand...
Do you mean that without the statement :

pf.NumberFormat = "General"

I should get an error on the statement :

pi.Visible = True

If so , I don't get any error.

Can you clarify why exactly the statement :

pf.NumberFormat = "General"

is necessary ?
 
Your test code is not creating a pivot out of data that has some blank cells in a Dates column. Correct, it is not. I was just trying to emphasize the point that sometimes PivotFields with dates in them will be hard-coded to the General number format, and that this cannot be changed by the user or by VBA. Which is a problem if someone wants to change or even read the .Visible status of such a PivotItem if they are using Excel 2007 or Excel 2010 and have a non-US regional setting.

So while in this particular case I have the luxury of switching between Date and General number formats (and in fact are only doing so to prove that the problem is related to the General format implementation), at other times I don't. This code was test code designed to help prove what settings were causing my problem.

I should get an error on the statement : pi.Visible = True
Correct, if your regional settings are UK or NZ or some other non-US regions and if you have Excel 2007 or Excel 2010, then after the number format is set to General, it should error out on that first pivot item (but probably not the second). I was just testing that that was indeed the case for Excel 2007 and for other users.

THe statement pf.NumberFormat = "General" is neccessarry to prove that this problem only rears it's head if the PivotFIeld format is set to General (and you have non-US region and the pivotitem of interest is a date).

Confusing as heck, I know. THere's more indepth explanation of the problem in my post at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems

The crux of this matter is that until yesterday, I wasn't aware of the regional and the version dimensions of this problem. I knew it was a problem for me, but didn't know it was not a problem for others with different regional and version combinations.

Cheers Narayan.
 
@jeffreyweir / @NARAYANK991

I had a similar issue but not with dates with normal text. Recently I wrote a code as shown below to apply filter to a pivot table in Excel 2010 through cell values. The pivot table was constructed using normal pivot table feature and not through macro.

The issue is the filter works only if there is a case match, i.e. In pivot if field is BMW and in cell I put bmw it gave error 1004, but if I put BMW it do the task. Is this Normal or this is also a bug?

Code: 
Sub Macro1()

Dim pt As PivotTable
Dim pfb As PivotField
Dim pfa As PivotField
Dim pvtitem As PivotItem

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pfb = pt.PivotFields("Brand")
Set pfa = pt.PivotFields("Acc")

Application.ScreenUpdating = False
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Acc").ClearAllFilters
  
    For Each pvtitem In pfb.PivotItems
        If pvtitem.Value = Range("G1").Value Then
            pvtitem.Visible = True
          Else
            pvtitem.Visible = False
        End If
    Next pvtitem
      
    For Each pvtitem In pfa.PivotItems
        If pvtitem.Value = Range("G2") Then
            pvtitem.Visible = True
          Else
            pvtitem.Visible = False
        End If
    Next pvtitem
      
Application.ScreenUpdating = True

End Sub

Can you experts throw some light on the issue.

Regards,
 
Not only can I shed light on that, but I can provide you with code that will do this much much faster than your current approach.

In fact I wrote a blog post on this :
http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/

...and I strongly suggest you read this article, and then take a close look at the code itself.

In my code, note the following comments which may be related to your issue
' Excel stores dates differently between PivotItems and Variant Arrays.

' For instance:
' ? CStr(varFilterItems(i, 1))
' 1/01/2013
' ? pi.Value
' 1/1/2013
' ? CStr(varFilterItems(i, 1)) = pi.Value
' False

'So we 'll turn our FilterItems into a PivotTable to ensure formats are treated the same.

I spent a large part of the weekend rewriting this code, to make it cleaner and faster still.
I'll post the amended routine here shortly.
 
Hi again SM. I see I misread your question in that my comments from my code above have nothing to do with your issue. (Although the article of mine I linked to is still well worth a read and the code will end up filtering your pivots much faster than you currently are.)

Here's what's happening in your case. That check is case insensitive, so BMW is seen as something completely different than bmw. And because there is no PivotItem "BMW" in the pivot, your code ends up trying to hide every PivotItem in the table. But you can't do this...at least one PivotItem must remain visible at all times. Which is why you're getting that 'Unable to set the Visible property of the PivotItem class' error.
 
Here's my revised code for filtering large pivots quickly, SM.

Check the attached file.

I turned the routine into a function, which you call by a wrapper. There's two 'wrapper' functions that you use, depending on whether you want to filter everything in your 'Filter Terms' list in or everything out.

Sub FilterPivot_ShowItems()
FilterPivot
End Sub

Sub FilterPivot_HideItems()
FilterPivot bInverse:=True
End Sub

The workbook that has the code inside that helps you test how this performs on very large pivots, and that lets you generate random alphanumeric PivotFields and Search Terms easily. Just click the Create Sample Data button after changing the input parameters, and then click on the command button of choice. When you run the code from the command buttons, the times of each pass will be recorded in the workbook too, so you can compare different settings.
 

Attachments

  • Filter PivotTable 20140505 v3.xlsm
    282.5 KB · Views: 2
Hi Jeff,

Thanks for putting your time here. I will study all your code and write back If I will have any doubts on the same.

Regards,
 
You must log in or register to reply here.
Back
Top