Screw up Excel by setting a PivotFilter via VBA

Today I did the following:

  • Created a new workbook
  • Added a pivot table, based on an Access database connection
  • Created it using the following setup

After doing that, I grouped my months by year, and dropped in the following code to the Sheet1 module:

[vb]Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With ActiveSheet
.PivotTables("PivotTable1").PivotFields("Account").ClearAllFilters
.PivotTables("PivotTable1").PivotFields("Account").PivotFilters.Add _
Type:=xlCaptionEquals, _
Value1:=.Range("B1").Value
End With
Application.EnableEvents = True
End Sub[/vb]Then I changed the value of B1 to one of my account numbers and… BOOM!

And when it says the Object invoked has disconnected from its clients, it really means it! What's really bizarre is that hitting end leaves me able to move around the worksheet using the mouse, as evident in the formula bar, but the active cell on the worksheet never changes. Closing and re-opening the workbook has no effect, as the issue persists. The only way to cure it is to shut down Excel and restart.

As it turns out, the problem is that I passed the pivotfilter a number, not a text string. An easy fix with CStr to convert it, and now I can quickly check the balances in my accounts over the last few years:

What really strikes me as really bizarre is that passing a number to a string variable in VBA will not cause an error. The value is accepted just fine. If I'd had to guess about this, I would have expected it to work the same way or, at the very worst, throw a trappable error. Passing a string to a numeric field shouldn't cause such as drastic automation error that forces you to close Excel to cure it.

Suspicious that this might be something to do with my setup, I also tested this on a pivot table created on data from within the workbook (I mocked up a simple three line table) and the same thing happens.

2 thoughts on “Screw up Excel by setting a PivotFilter via VBA

  1. Ken,

    Hi - just found your blog from googling pivotfilters - and what you've applied here is very close to the solution I am trying to deliver.

    However, have you had success with applying the pivotfilter to the page fields instead of the row field? This method works for rows, but in the case of having too many row fields and wanting to filter for strings that contain certain parts of text - can you still use the pivotfilter function?

    Thanks,

    Nate

  2. Hi Nate,

    I don't see that you should have any issue using the page fields (just maybe make sure that you convert your parameters to strings if your data may contain numbers).

    If you need any help implementing your specific solution, don't hesitate to post in the forums at http://www.excelguru.ca/forums

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *