Workbook.SlicerCaches.Add : won't work with late-binding (Invalid procedure call or argument)

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
669
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
I have a function which I use to generate a set of slicers for all pivot tables on a worksheet. The function accepts as arguments, the worksheet containing the pivot tables and the fields to be used for each of the slicers.

This works perfectly fine when using early-binding; however, I want to switch from early-binding to late-binding (this code is in an Access database, not running "directly" in Excel itself) so as not to run into issues with Excel versions in the future.

But when I remove the reference to the Excel object (Microsoft Excel 16.0 Object Library) and convert the declarations to generic objects, the code falls over when I try to create a new slicer cache via Workbook.SlicerCaches.Add with an error :

Error 5 : Invalid procedure call or argument

I'm using late-bound Excel throughout the project (to generate the workbooks and pivot tables etc.) and they all run perfectly using generic objects. But for some reason this particular line won't work with late-binding? And the only difference with the code is that I've removed the reference and declared the objects generically.

I've also tried using Workbook.SlicerCaches.Add2 (in fact that was my original code) but the result was the same.

Here is the early-bound version of the code (this works perfectly) :

VBA Code:
Public Function CreatePivotSlicer(sht As Object, varSlicerFields As Variant, Optional dblSlicerTop As Double = 20, Optional dblSlicerLeft As Double = 20, Optional dblSlicerHeight As Double = 194, Optional dblSlicerWidth As Double = 150, Optional dblSlicerGap As Double = 20, Optional strSlicerStyle As String = "SlicerStyleLight4") As Boolean

    On Error GoTo ErrorHandler
    
    Dim wbk As Workbook
    Dim pvt As PivotTable
    Dim arrPivotTables() As PivotTable
    Dim arrSlicerCaches() As SlicerCache
    Dim arrSlicers() As Slicer
    
    Dim i As Integer
    Dim j As Integer
    
    ' Read all the pivot tables on the worksheet into an array
    ReDim arrPivotTables(0 To 0)
    For Each pvt In sht.PivotTables
        If Not arrPivotTables(UBound(arrPivotTables)) Is Nothing Then ReDim Preserve arrPivotTables(0 To UBound(arrPivotTables) + 1)
        Set arrPivotTables(UBound(arrPivotTables)) = pvt
    Next pvt
    
    If UBound(arrPivotTables) > 0 Then
        Set wbk = sht.Parent
        With wbk
            If IsArray(varSlicerFields) Then
                ReDim arrSlicers(LBound(varSlicerFields) To UBound(varSlicerFields))
                ReDim arrSlicerCaches(LBound(varSlicerFields) To UBound(varSlicerFields))
                For i = LBound(varSlicerFields) To UBound(varSlicerFields)
                    Set arrSlicerCaches(i) = .SlicerCaches.Add(arrPivotTables(LBound(arrPivotTables)), varSlicerFields(i))
                    Set arrSlicers(i) = arrSlicerCaches(i).Slicers.Add(sht, , , varSlicerFields(i), dblSlicerTop, dblSlicerLeft, dblSlicerWidth, dblSlicerHeight)
                Next i
                For i = LBound(arrSlicerCaches) To UBound(arrSlicerCaches)
                    For j = LBound(arrPivotTables) + 1 To UBound(arrPivotTables)
                        arrSlicerCaches(i).PivotTables.AddPivotTable arrPivotTables(j)
                    Next j
                    With sht.Shapes(arrSlicers(i).Name)
                        .IncrementLeft ((dblSlicerWidth + dblSlicerGap) * i)
                        .Placement = 3        ' xlFreeFloating
                    End With
                    arrSlicers(i).Style = strSlicerStyle
                Next i
            End If
        End With
    End If
    
    CreatePivotSlicer = True

Exit_CreatePivotSlicer:
    On Error Resume Next
    Exit Function

ErrorHandler:
    CreatePivotSlicer = False
    Call LogError(Err.Number, Err.Description, "CreatePivotSlicer", "modExcelFunctions")
    Resume Exit_CreatePivotSlicer

End Function

And called thus :

VBA Code:
If Not CreatePivotSlicer(sht, Array("Field1", "Field2", "Field3", "Field4", "Field5", "Field6", "Field7")) Then
    MsgBox "There was an error setting up the pivot slicer [" & sht.Name & "]", vbCritical + vbOKOnly, "Error Setting Up Pivot Slicer"
End If

Here is the late-bound version (only difference is the removal of the Excel reference and switching the declarations to generic objects) :

VBA Code:
Public Function CreatePivotSlicer(sht As Object, varSlicerFields As Variant, Optional dblSlicerTop As Double = 20, Optional dblSlicerLeft As Double = 20, Optional dblSlicerHeight As Double = 194, Optional dblSlicerWidth As Double = 150, Optional dblSlicerGap As Double = 20, Optional strSlicerStyle As String = "SlicerStyleLight4") As Boolean

    On Error GoTo ErrorHandler
    
    Dim wbk As Object
    Dim pvt As Object
    Dim arrPivotTables() As Object
    Dim arrSlicerCaches() As Object
    Dim arrSlicers() As Object
    
    Dim i As Integer
    Dim j As Integer
    
    ' Read all the pivot tables on the worksheet into an array
    ReDim arrPivotTables(0 To 0)
    For Each pvt In sht.PivotTables
        If Not arrPivotTables(UBound(arrPivotTables)) Is Nothing Then ReDim Preserve arrPivotTables(0 To UBound(arrPivotTables) + 1)
        Set arrPivotTables(UBound(arrPivotTables)) = pvt
    Next pvt
    
    If UBound(arrPivotTables) > 0 Then
        Set wbk = sht.Parent
        With wbk
            If IsArray(varSlicerFields) Then
                ReDim arrSlicers(LBound(varSlicerFields) To UBound(varSlicerFields))
                ReDim arrSlicerCaches(LBound(varSlicerFields) To UBound(varSlicerFields))
                For i = LBound(varSlicerFields) To UBound(varSlicerFields)
                    Set arrSlicerCaches(i) = .SlicerCaches.Add(arrPivotTables(LBound(arrPivotTables)), varSlicerFields(i))        ' <<<< This is where the code falls over - Invalid procedure call or argument
                    Set arrSlicers(i) = arrSlicerCaches(i).Slicers.Add(sht, , , varSlicerFields(i), dblSlicerTop, dblSlicerLeft, dblSlicerWidth, dblSlicerHeight)
                Next i
                For i = LBound(arrSlicerCaches) To UBound(arrSlicerCaches)
                    For j = LBound(arrPivotTables) + 1 To UBound(arrPivotTables)
                        arrSlicerCaches(i).PivotTables.AddPivotTable arrPivotTables(j)
                    Next j
                    With sht.Shapes(arrSlicers(i).Name)
                        .IncrementLeft ((dblSlicerWidth + dblSlicerGap) * i)
                        .Placement = 3        ' xlFreeFloating
                    End With
                    arrSlicers(i).Style = strSlicerStyle
                Next i
            End If
        End With
    End If
    
    CreatePivotSlicer = True

Exit_CreatePivotSlicer:
    On Error Resume Next
    Exit Function

ErrorHandler:
    CreatePivotSlicer = False
    Call LogError(Err.Number, Err.Description, "CreatePivotSlicer", "modExcelFunctions")
    Resume Exit_CreatePivotSlicer

End Function

Any ideas as to why I can't use late-binding to perform this particular method?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top