Archive for the ‘Bugs’ Category.

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.

Wonky Keyboard Shortcut

Check out the keyboard shortcut for the Reapply button.

I don’t think I’ve ever used that button. If I need it, I think I’ll just use the mouse. Even I have limits.

Indecent Add-in Exposure

Every so often, for reasons I can’t explain, an add-in shows up in my Excel UI.

addinshowingup

Today it was BlogHelpers.xla, but other times it’s different. It’s always in a non-maximized window view. That is, the workbook name is in the window’s title bar and not the application’s title bar. You can see that the IsAddin property is still True. If I close the UI window, the Add-in remains in the VBE projects window. But I’m too chicken to leave it in that state for long, so I always reboot.

Have you ever seen that before?

A date with PivotItems

Howdy, folks. Jeff here again. I’ve been sharpening up some code to manually filter a PivotField based on an external list in a range outside the PivotTable. It works blazingly fast. Unless there’s dates as well as non-dates in my PivotField. In which case it errors out.

Try this:
Put “Pivot” in A1
Put =TODAY() in A2
Put =VALUE(TODAY()) in A3

Now make a PivotTable out of that data.

Pivot_DDOE

Now put this code into the VBE and step through it:

Sub WhatThe()

Dim pf As PivotField
Set pf = ActiveSheet.PivotTables(1).PivotFields(1)
With pf
    .NumberFormat = "d/mm/yyyy"
    .PivotItems(1).Visible = True
    .PivotItems(2).Visible = True
    .NumberFormat = "General"
    .PivotItems(1).Visible = True
    .PivotItems(2).Visible = True
End With
End Sub

If the same thing happens to you as happens to me, you will either be speaking aloud the title of this routine, or you will be speaking aloud this:
Unable to get the PivotItems property of the PivotField class.

Go type these in the immediate pane:

? .PivotItems(1).name
41588
? .PivotItems(1).visible
True
? .PivotItems(2).name
10/11/2013
? .PivotItems(2).visible
Error 2042

What the…?

Now try these:

? ActiveSheet.PivotTables(1).PivotFields(1).numberformat
General
ActiveSheet.PivotTables(1).PivotFields(1).numberformat = "d/mm/yyyy"
? .PivotItems(2).name
10/11/2013
? .PivotItems(2).visible
True

So it seems can’t do certain stuff to a PivotItem if that PivotItem is a date but your PivotField number format is set to General.

That’s weird.

Equally weird, try this:
Select the PivotTable, and record a macro as you change it’s number format back to General.

Sub WhatThe_Part2()  
 ActiveSheet.PivotTables("PivotTable14").PivotFields("Pivot").Name = "General"
End Sub

What the …? Change the PivotField Number Format, and you get a macro that tells you that you changed the PivotField name!

So what happens if you run that macro? Well, it changes the name of the PivotField:
PivotField_20131110

It does nothing to the number format.

Strangely enough, I found some info on this problem at one of my most revisited blogposts that I had somehow missed: Jon Peltier’s Referencing Pivot Table Ranges in VBA

Stranger still, the answer was by Jon Peltier back in 2009 in relation to a question asked by….wait for it…me. Don’t know how I missed that. Must have been sleeping.
So I’ve come across this problem before, found an answer, completely forgotten about it, and then wasted 2 days trying to work out what the problem was, purely so I could Google my own answered question.

I’m going to read through all 238 ( and counting) comments in that thread and see what else Jon has told me over the years I’ve been learning VBA.
There’s also something on this at stackoverflow

Jeff

–edit–
Jon’s method was to loop through the pivot items, and compare the pivot item caption to what he was looking for:

For Each pi In pt.PivotFields("Order Month/Year").PivotItems
  If pi.Caption = Format(TheDate, "m/dd/yyyy") Then
  ' or If DateValue(pi.Caption) = TheDate Then
    '' FOUND IT
  End If
Next

But now I know it’s probably easier just to change the format of the PivotField.

—edit 30 May 2014 —
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?)

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.

— edit over —

PivotItems Are Wrong when Calculated Field Is Present

I have a procedure where I loop through a pivot table and create a general journal entry to be imported. I used the same procedure to create another journal entry, but with one change. For this new journal entry, I wanted to round all of the amounts to the nearest $10. They were estimates and I don’t like to post estimates to the penny because it implies a precision that just isn’t there.

To accomplish this seemingly simple task, I created a calculated field and adjusted the procedure to pull from that field. There was just one problem: my debits didn’t equal my credits! I know, I gasped too.

It turns out that looping through the pivot table with For Each pi In pf.PivotItems was starting on the row below the first pivot item and ending on the row below the last pivot item. It was offset one row.

I was puzzled for quite a while. The old procedure worked fine. Then it dawned on me that I had added a calculated field. When I removed the calculated field, it worked as expected. So I modified the procedure to do the rounding in VBA.

Next, I wanted to see if this was a fluke. I generated some sample data, namely First Name, Last Name, and City. Then I created a pivot table with City in the Row Labels section and Count of Last in the Value section. Finally, I created a calculated field expertly named Field1.

The calculated field is set to zero. It’s not really calculating anything. I added that to the Values section.

Now to test. I wrote some code to demonstrate that the PivotItems were pointing to the wrong cells. As part of my code, I wanted to remove the calculated field. I kept getting errors trying to remove the calculated field so I recorded a macro to see how it’s supposed to be done.

ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Field1").Orientation = xlHidden

Yep, that’s what I was trying to do. Only that doesn’t work. So I found a work around. I love finding bugs when I’m investigating other bugs.

Here’s the code:

Sub TestPivotItems()
   
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
   
    Set pt = ActiveCell.PivotTable
    Set pf = pt.PivotFields("City")
    Set pi = pf.PivotItems(1)
   
    Debug.Print pi.LabelRange.Address, pi.LabelRange.Value
    Debug.Print ActiveSheet.Cells.Find(pi.Value).Address, pi.Value
    Debug.Print String(40, "-")
   
    pt.PivotFields("Sum of Field1").DataRange.Cells(1).PivotItem.Visible = False
   
    Debug.Print pi.LabelRange.Address, pi.LabelRange.Value
    Debug.Print ActiveSheet.Cells.Find(pi.Value).Address, pi.Value
   
End Sub

And here’s the result:

Above the dashed line is what happens when calculated field is present. The PivotItem, which is in A4, shows A5 as its LabelRange. When I search for its Value, however, I get A4. When I removed the calculated field, it all works as expected.

WorksheetFunction.Index Limitations

Did you know that there is a limit to the array that can be passed into WorksheetFunction.Index? I read a really big range into an array and I wanted to split out a row. I used the INDEX(array, rownum, 0) feature, but I kept getting a Type Mismatch error. I couldn’t figure out what the heck was going on. I ended up testing my code on a smaller range and it worked as expected.

Sub IndexTest()
   
    Dim vaValues As Variant
    Dim i As Long
    Dim vaNew As Variant

    vaValues = Sheet1.UsedRange.Resize(2 ^ 16 + 1).Value
   
    For i = LBound(vaValues, 1) To UBound(vaValues, 1)
       
        vaNew = Application.WorksheetFunction.Index(vaValues, i, 0)
       
        Stop
    Next i
   
End Sub

A little experimenting and I discovered that if my range is 65,537 rows long, I can’t pass it into Index. By the way, there does not appear to be any such limitation when the function is called from a worksheet cell, e.g. =COUNTA(INDEX(A2:G1007425,1,0)).

You may have noticed that the limit is the same number of rows in a 97-2003 file format. I wonder if that’s related. Am I missing something? Am I doing something wrong?

Excel 2010 64-bit Problems

I upgraded to Office 2010 64-bit this morning and it’s not working out for me. I was offered the upgrade at work and of course I took it. Why wouldn’t I? Here are the problems I’ve encountered so far.

I can’t add menu items to the VBE. I have this line of code

Set MenuEvent.EvtHandler = Application.VBE.Events.CommandBarEvents(CmdBarItem)

that I got from Chip Pearson. Someone asked the question on the MSDN forum, but did not get an answer. That didn’t stop some Microsoft douche-nozzle from marking a response as “the answer”. Those forums suck for that reason.

frustrated

Next, MZ Tools doesn’t work on 64-bit.

Next, CodeCleaner doesn’t work on 64-bit.

Nothing important, just three things I use every single day. Dang.

My plan is to search for updates to Office 2010 and hope that it was fixed. It was a recent clean install, so I expect that it’s up-to-date, but we’ll see. If that doesn’t work, then I don’t know. I guess I’ll have to put the menu on a userform and futz around with the window handles to make it a child of the VBE. Ugh.

Part two of my plan is to rewrite CodeCleaner for 64-bit. How hard could that be? It was only written by two of the best VBA programmers in history. Part three is replicating my favorite parts of MZ Tools in 64 bit.

I’ll be shoe-horning this three part plan into year-end, a technical review of a book, development of an add-in for sale, a consulting gig, and college football season. End rant.

Pegged

Can’t a guy sort a couple hundred rows?