AOB
Well-known Member
- Joined
- Dec 15, 2010
- Messages
- 669
- Office Version
- 365
- 2016
- 2013
- Platform
- 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
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
Here is the early-bound version of the code (this works perfectly) :
And called thus :
Here is the late-bound version (only difference is the removal of the Excel reference and switching the declarations to generic objects) :
Any ideas as to why I can't use late-binding to perform this particular method?
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?