Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Why does this cause error 40040

Status
Not open for further replies.

Gavona

Technical User
Aug 27, 2002
1,771
GB
Code:
Function PivotRefreshDate(CellWithinPivot As Range)
PivotRefreshDate = CellWithinPivot.PivotTable.RefreshDate
End Function
Manually opening the workbook and refreshing the pivot causes the UDF to update correctly. However using this code the UDF returns an error (it does not halt, the cell just reports #value!). (The error is then picked up in my error trapping/reporting code).
Code:
     With wbkPivots
        .Sheets("General").PivotTables("PvtSCF").PivotCache.Refresh
        .Save
        .Close SaveChanges:=True
End With
Calculation is automatic, DisplayAlerts is True

If I re-open the workbook, select the cell with the UDF, press F2, press enter. Then it calculates perfectly.




Gavin
 
Well I still have the error so if anyone has any ideas?

Gavin
 
It's not clear what you mean.

It updates correctly, it returns an error.

???
 
I have the UDF in a cell in the worksheet. It reports the date/time when the pivottable was last refreshed.

If I refresh the pivottable without using vba then the UDF calculates and returns the date/time that the pivot was refreshed. So all seems good.

But if I use VBA to perform the refresh two problems occur:
i) the cell containing the UDF has #value! in it.
ii) an error is raised

I just ran this code on a different workbook:
Code:
Sub test()
[COLOR=red]Debug.Print Err.Number[/color]
With ThisWorkbook
        .Sheets("DashData").PivotTables("Pivottable4").PivotCache.Refresh
        [COLOR=red]Debug.Print Err.Number[/color]
        .Save
End With
End Sub
The immediate window shows:
0
40040

Gavin
 
Thanks for looking Skip. Not often you say that!
I think it must be a bug in excel, somehow related to the calculation engine.

This works as expected and no errors are raised:
Code:
Sub test()
[COLOR=#A40000]Application.Calculation = xlCalculationManual[/color]
With ThisWorkbook
        Debug.Print "x1: " & Err.Number
        .Sheets("DashData").PivotTables("pvtfin7").PivotCache.Refresh
        Debug.Print "x2: " & Err.Number
        .Save
End With
[COLOR=#CC0000]Application.Calculation = xlCalculationAutomatic[/color]
Debug.Print "x3: " & Err.Number
End Sub




Gavin
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top