Error Handling for Pivot Tables in VBA

[kensandy1]

Hi All,

I'm not familiar enough with error handling in VBA, so I'm trying to figure out the best way to handle this. I did a little searching and found an On Error Goto that might work but I don't think that will be the best solution here.

I have 18 worksheets, each worksheet as a Pivot table. In the pivot table, there are 24 hours, one for each hour of the day (midnight - 11pm). I have a function that will scan through a all 18 pivot tables looking for a specific hour of the day and spit out how many calls were received for that hour. The problem I have is if there's no calls during that hour, it doesn't appear on my Pivot Table so it gives me an error when running the code.

Here's what I have:

Sub Analyze_Hour()

Dim time As Date
Dim pvformula_value As Integer
Dim test As String
Dim pT As PivotTable

time = Worksheets("Dashboard").Cells(3, "R").Value
pvformula_value = Round((time * 1440 / 60 + 1), 0)


Set pT = Sheets("Calls by Day MTD").PivotTables("PivotTable1")
test = pT.GetPivotData("[Measures].[Calls Offer incl Short Abn]", "[TimeHalfHour].[TimeHalfHour]", "[TimeHalfHour].[TimeHalfHour].[HOUR].&[3]&[" + pvformula_value + "]")


MsgBox (test)


End Sub

If the hour is in the pivot table for sheet 'Calls by Day MTD' for whatever hour is selected in Cell R3, it works. If the time isn't in the pivot table, I want it to return a 0.

Using pseudo code, I want:

test will equal pT.GetPivotData("[Measures].[Calls Offer incl Short Abn]", "[TimeHalfHour].[TimeHalfHour]", "[TimeHalfHour].[TimeHalfHour].[HOUR].&[3]&[" + pvformula_value + "]")

Or it will equal 0 if time pvformula_value doesn't exist in this pivot table

Is there an error function in VBA that I can use to accomplish this?

 

[kensandy1]

I guess I can use the GoTo in a function, I just would think there was something easier than writing a function specifically for this.

Function timeExists(value As Integer, pT As PivotTable) As Boolean


    On Error GoTo foo
    
    timeExists = False
    
    If pT.GetPivotData("[Measures].[Calls Offer incl Short Abn]", "[TimeHalfHour].[TimeHalfHour]", "[TimeHalfHour].[TimeHalfHour].[HOUR].&[3]&[" + CStr(value) + "]") Then
        timeExists = True
    End If
foo:
    
End Function