Run Time Error '424' object required when calling function VBA

Question

I'm using this code to update the source of all my pivot tables but I always get

"Run Time Error '424' object required".

The weird part is that placing the code in the function inside the loop actually works without this error so I don't think it's the function's code but the way I'm calling it. I would like to know why it's not working.

Private Sub Workbook_Open()
    For Each st In ActiveWorkbook.Worksheets
        For Each pt In st.PivotTables
            UpdatePivotSource (pt)
        Next
    Next
End Sub

Sub UpdatePivotSource(pt As PivotTable)
    Dim StartPoint As Range
    Dim NewRange As String
    Dim LastCol As Long
    Dim lastRow As Long
    Dim Data_Sheet As Worksheet
    Dim DataRange As Range

    Set Data_Sheet = ThisWorkbook.Worksheets(Split(pt.PivotCache.SourceData, "!")(0))
    Set StartPoint = Data_Sheet.Range("A1")
    LastCol = StartPoint.End(xlToRight).Column
    lastRow = StartPoint.End(xlDown).Row

    Set DataRange = Data_Sheet.Range(StartPoint, Data_Sheet.Cells(lastRow, LastCol))
    NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)

    pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)
End Sub

Vityata · Accepted Answer · 2019-02-06T14:28:31.790

1

Change the sub to this one:

Private Sub Workbook_Open()
    Dim pt As Object 'or Dim pt As PivotTable
    For Each st In ActiveWorkbook.Worksheets
        For Each pt In st.PivotTables
            UpdatePivotSource pt
        Next
    Next
End Sub

The difference is that pt is declared as Object and the pt is not within parenthesis. The parenthesis in VBA parameters should be used with caution, only when one knows what is happenning. E.g., objects are passed by reference (ByRef) and the parenthesis force ByVal passing.

To avoid non-declared variables in the future use Option Explicit on the top of your module - What do Option Strict and Option Explicit do?

A nice explanation of Passing Variables ByRef and ByVal from CPearson.

edited Feb 06 '19 at 14:28

answered Feb 06 '19 at 14:11

Vityata

42,633
8
55
100

1

The issue is that `(pt)` [tries](https://stackoverflow.com/a/8257503/11683) to pass `pt.[_Default]`, which for `PivotTable` is a `String`. Another good thing on the way to fix it is using `ByVal pt As PivotTable`, and amusingly, in that case the call can be performed correctly even with `(pt)`, but only when `pt` is declared as `Variant`. Declaring it as `Object` or `PivotTable` requires removing the parentheses, which is the correct thing to do anyway. – GSerg Feb 06 '19 at 14:23

Run Time Error '424' object required when calling function VBA

1 Answers1