Why is GETPIVOTDATA so cumbersome to work with! I'm always having to do stuff like:
=if(iserror(GETPIVOTDATA([parameter set])),0,GETPIVOTDATA([parameter set]))
It gets to be a real pain as I have to edit both [parameter set]s to have it work properly and there's always the possibility of making a mistake that is not immediately apparent.
I finally decided I'd create my own function that returned 0 instead of erroring:
Function GETMYPIVOTDATA(data_field, pivot_table, _
Optional field1, Optional item1, _
Optional field2, Optional item2, _
Optional field3, Optional item3, _
Optional field4, Optional item4, _
Optional field5, Optional item5, _
Optional field6, Optional item6, _
Optional field7, Optional item7, _
Optional field8, Optional item8, _
Optional field9, Optional item9, _
Optional field10, Optional item10, _
Optional field11, Optional item11, _
Optional field12, Optional item12, _
Optional field13, Optional item13, _
Optional field14, Optional item14)
Dim retval
Err.Clear
On Error Resume Next
retval = pivot_table.pivottable.GetPivotData(data_field, _
field1, item1, field2, item2, field3, item3, field4, item4, _
field5, item5, field6, item6, field7, item7, field8, item8, _
field9, item9, field10, item10, field11, item11, field12, item12, _
field13, item13, field14, item14 _
)
On Error GoTo 0
GETMYPIVOTDATA = IIf(IsError(retval), 0, retval)
End Function
Now I just use =GETMYPIVOTDATA([parameter set])
Thursday, 20 June 2013
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment