Error -2147417848: Method 'NumberOfColumns' of object 'Slicer' failed

[codeliftsleep]

This is a very strange error...Whats happening is I have various users looking at information. I have pivot tables that are updated daily and then filtered to display only the information for that user. However, the pivot tables were causing all kinds of issues refreshing properly so I scrapped this and now Delete and rebuild the pivot tables and slicers each time a user logs in.

It appears it is happening due to a timing issue. I have the following code:

ThisWorkbook.SlicerCaches("Slicer_AGING_BUCKET").Delete
    
    'Create a new slicer
    Dim sc As SlicerCaches
    Dim sl As Slicers
    Dim s As Slicer
    
    Set sc = ThisWorkbook.SlicerCaches
    Set sl = sc.Add2(CurDayIP.PivotTables(1), "AGING BUCKET", "Slicer_AGING_BUCKET").Slicers
    Set s = sl.Add(CurDayIP, , "Slicer_AGING_BUCKET", "Choose Aging Buckets to View", 0, 0, 800, 50)
    s.NumberOfColumns = 8

the first time through, it throws the error in the Title if I run the code by itself. However, once it goes through the error handler, logs it and pops up the error in a message box to the users, and resumes, if I go down to the s.NumberOfColumns = 8 line of code and run it, it works without any issues. As it does it if I step through the code line by line using F8.

The only time it throws an error is when it runs by itself. Any ideas how to make sure this doesn't happen? Do I add a 3 second pause in there or something before I set the number of columns?

Seems really weird and it's these type of things that are really frustrating in VBA...no reason for that to be popping up other than the code is running before s is set in code.

 

[codeliftsleep]

Ok, now this is even weirder.

I put an Application.Wait in there for 3 seconds, but it still throws the same error.

However, I noticed the number of columns were actually set to the right value even though it errored out.

So then I put an On Error Resume Next line above that and it threw a:

Error 1004: Application Defined or Object-Defined Error


What is going on with the automation error then converting into a Application error??

 

[codeliftsleep]

UPDATE: Fixed the issue...

I found this in my search:

"From Foxden: I had this problem too and what you need to do is change the setting of your data connections. By default, Excel will "Enable background refresh". This must be turned off and it will force the macro to complete the refresh task before it moves on. If using Excel 2007, bring up the "Connection Properties" and uncheck the box that enables the background refresh. You can get there by clicking on the table that gets refreshed and where you would click to actually refresh the data you should see an arrow for more options."

So apparently the issue was The pivot table was not refreshing in time, and had no data before it was calling the Slicer which would then fail.

I simply added:

ThisWorkbook.RefreshAll
DoEvents

And it now works perfectly!