Learning from my Errors

There’s an annoying bug in VBA whereby if you’re trying to change the .visible status of a PivotItem, and if the PivotField had a number format set to General, and if you live in New Zealand, then you’re out of luck:

Unable to set visible property

If you live in the US however, you’ll be fine. No error for you.

Don’t believe me? Either change your Windows region to New Zealand and run this code, or swing down to my place and see it for your own eyes. Here’s where you’ll find me:

247 Rintoul Street

(Aside: Check out those awesome ocean views. Why if it wasn’t for that annoying continent-sized lump of Uranium and Gold Ore off to the West, we’d pretty much have 365 degree views of the entire Pacific. Fortunately they’re busy bulldozing that annoying outcrop and shipping it off to uranium reactors and jewelery stores across the globe. So we should have a completely unfettered view in 2 billion years or so).

Ok, so this issue isn’t just an issue for New Zealanders…it actually affects any place where you haven’t got your Windows ‘region’ set to US, with New Zealand being the only place where I’ve actually encountered such egocentric behavior to date. (I don’t get out much. Or rather, they don’t let me out much. Or rather they make it clear that I can go out, but I can’t come back in.)

According to IronyAaron in the comments at this thread:

When VBA recognizes the dates in the pivot cache, it reads the US version after parsing although the item is read as a locally formatted string. This therefore causes VBA to fail when recognizing Date variables.

Bummer! So write some code that filters PivotItems, and you might find that non-US users have issues, unless they change their regional settings in Windows to US beforehand. Good luck with that.

This nasty bug caused quite a bit of workaround in my FilterPivot routine. I used to do this horrible check on every single item in a potentially exhaustively long list of PivotItems in order to avoid the possibility of an error caused by this unlikely combination occurring:

If Not IsNumeric(Pi.Value) Then
    'We need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
    If IsDate(Pi.Value) Then
        If Not bDateWarning Then
            On Error GoTo ErrHandler
            Err.Raise Number:=997, Description:="Can't filter dates"
            On Error Resume Next
        End If
    Else: Pi.Visible = True
    End If
Else: Pi.Visible = True
End If

But prompted by Jerry Sullivan’s comment I found that this was only an issue for non-US regional settings, and that this issue is now fixed in Excel 2013. (Thank you, Microsoft. But why the heck didn’t you tell me you’d fixed it?)

So now I can just do this:

pi.Visible = True

Or rather, I could just do that if everyone had Excel 2013. But they don’t. So I can’t. I still have to somehow catch this error. And as written above, my code rather inefficiently looks for possible trouble caused by a combination of things that is probably unlikely to occur. (I mean, how many people would dare to have their Windows region set to a non-US region while trying to filter a PivotItem that happens to be a date in a PivotField that happens to have a General format?) All that preemptive error checking can’t be good for business.

The error of my ways?

I’m sure you’ve already seen what looks to be like the error of my ways… why bother checking for errors just so I can avoid them? Why not embrase them: just plow ahead, and if the s#!t hits the fan, just deal with it. Something like this:

On Error Goto Errhandler
pi.Visible = True

'some other code

ErrHandler:
If Err.Number <> 0 Then
    Select Case Err.Number
    Case 1004 'Error likely due to bug outlined at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/
        If Not IsNumeric(pi.Value) And IsDate(pi.Value) And pfOriginal.NumberFormat = "General" Then 'Yep, definately that 'Bug
            'Note that we need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
            i = i + 1
            ReDim Preserve strDateItems(1 To i) 'Record the offending things to an array, so we can warn the user about the specific items
            strDateItems(i) = pi.Value
        End If
    Case Else 'Some other error code here
    End

Great: now that bug fix code in the Errhandler only gets fired up in the rare event that it is actually triggered. Sure beats neurotically checking each and every PivotItem to see if it might cause an issue.

Unfortunately in this particular case the code snippet in question sits within a larger loop, and the code immediately before needs to have an On Error Resume Next statement applied. That’s because in order to work out whether a PivotItem should be hidden, I’m adding it to a Dictionary object that also contains my desired filter terms, in order to see if it matches any of those filter terms. Which looks something like this:

On Error Resume Next
For Each pi In pfOriginal.PivotItems
    dic.Add pi.Value, 1 'The 1 does nothing
    If Err.Number <> 0 Then
    pi.visible = true
    ...

So I’d need to put an On Error Goto Errhandler before the pi.Visible = True bit so that my bug fix code in Errhandler would get triggerred, and an On Error Resume Next bit after it, so that the Dictionary test occurs for the very next item. And those will get executed for every single PivotItem – which kind of defeats the efficiency ‘dividend’ of putting my handling code within Errhandler. So I figure I might as well just do this:

pi.Visible = True
If Err.Number = 1004 Then 'Error likely due to bug outlined at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/
    If Not IsNumeric(pi.Value) And IsDate(pi.Value) And pfOriginal.NumberFormat = "General" Then 'Yep, definately that 'Bug
        'Note that we need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
        i = i + 1
        ReDim Preserve strDateItems(1 To i) 'Record the offending things to an array, so we can warn the user about the specific items
        strDateItems(i) = pi.Value
    End If
End If

Two steps forward, one step backwards.

Maybe I shouldn’t have that On Error Resume Next in there in the first place…maybe I should catch errors from the Dictionary.add in Errhandler too, or even do the dictionary check in another procedure – something that Dick mentions here. Anyone got any advice here?

All I know is that this is a lot of work-around for a combination that is pretty unlikely, but potentially fatal to someone’s analysis.

10 Comments

  1. Luke M says:

    Hi Jeff,

    Crazy little bug you found there! Not sure about everything else, but you could work around the Dictionary error by changing from using Add, to just assigning the value, like:
    dic(pi.Value) = 1

    If pi.Value doesn’t exist yet in Dictionary, it will add it. If it does exist, it would update the value (but since you’re just giving everything a 1, I don’t think this matters). Then you don’t need the On Error Resume Next.

  2. Jeff Weir says:

    Hi Luke. Yeah, I attract all the crazies! Think it’s my avatar ;-)
    I actually wanted the dictionary error, because it told me there was a duplicate. So what I’d have to do to ditch the On Error Resume Next is this:

    On Error GoTo ErrHandler
    For Each pi In pf.PivotItems
        With pi

            If Not dic.Exists(.Value) Then
                dic.Add .Value, 1
            Else
                .Visible = False
            End If
        End With
    Next pi

    …which adds a bit more processing to the code, but not much, and is probably the ‘correct’ way to do things in any case.

    Cheers, pal.

  3. Doug Glancy says:

    Jeff, this is a bit of a shot in the dark, as I don’t have a good grasp on your code, but…

    I’d consider creating the Dictionary object earlier in the code in a separate function and then cycling back through it later to do the actual work.

    That may seem more repetitive than you like, but it’s nicely encapsulated, and will help unsnarl some of these types of issues you’re addressing (although not the “problem” of living in New Zealand).

  4. Jeff Weir says:

    Hi Doug. I never thought of that. Was trying to do everything at once. That would work just fine. Mind you, so does explicitly checking to see if something exists using the .Exists method rather than being lazy and just catching the error, as per my response to Luke.

    In fact, I can simplify that code to just this, as I don’t actually need to add the PivotItems to the dictionary…I just need to know if they are already in there:

    On Error GoTo ErrHandler
    For Each pi In pf.PivotItems
        If dic.Exists(.Value) Then .Visible = False
    Next pi
  5. Jeff Weir says:

    I guess the learning here is that if your code needs both an On Error Resume Next and an On Error Goto Errhandler in the same For/Next block, your code is stupid!

  6. Dick Kusleika says:
    .Visible = Not  dic.Exists(.Value)
  7. Jeff Weir says:

    Australia.Visible = FALSE :-)

  8. Doug Jenkins says:

    “Why if it wasn’t for that annoying continent-sized lump of Uranium and Gold Ore off to the West, we’d pretty much have 365 degree views of the entire Pacific. Fortunately they’re busy bulldozing that annoying outcrop and shipping it off to uranium reactors and jewelery stores across the globe. So we should have a completely unfettered view in 2 billion years or so).”

    Don’t forget the coal and steel. But don’t worry, with the new government we’ll probably clear it out of your way in less than 1 billion years.

  9. Jeff Weir says:

    Perhaps sooner, what with all that wonderful coal-induced climate change eating away at your fringes. But then, it’s eating away out ours to. God forbid we should end up in the same liferaft of Tasmania…

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: