MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Tables in VBA


Posted by Bill on December 28, 2000 10:05 AM

I am building my first set of pivot tables in a production application in VBA. In the past, I have had a (perhaps unfounded) fear that by using Pivot Tables I would lose some measure of control.

If you've had experience using pivot tables in VBA, I would appreciate any advice about any "gotchas" that I have to watch for.

Questions:
1) Creation of the pivot table takes several steps. First, there is the PivotCaches.add().CreatePivotTable, then I set some table settings, add fields, add data fields, and limit the PageField to a particular value. Is Excel re-calculating the pivot table after each command? Is there a way to turn this off until I get the whole pivot table defined in order to speed macro execution?

Thanks in advance for any advice.


Posted by Dave on December 28, 2000 9:23 PM


Hi Bill


I have done some Pivot Table VBA in the past and yes they do recalculate as you manipulate them. To stop this you need to set ManualUpdate to True then back to False on completion. Screenupdating should also be turned off.

Sub SpeedUp()
Dim Pt As PivotTable

Set Pt = Sheet4.PivotTables(1)
With Pt
Application.ScreenUpdating = False
.ManualUpdate = True
'< Your Code >
.ManualUpdate = False
Application.ScreenUpdating = True
End With
End Sub

Great tool though aren't they ?

Dave

OzGrid Business Applications

Posted by Bill on December 29, 2000 6:57 AM

Dave,
Thanks for the tip! I will add the manual updating logic to my code. The Pivot Tables worked out great. From a 10,000 row recordset, the code calculates 5 pivot tables in a blink.

Thanks for the info.

Bill


:: I am building my first set of pivot tables in a production application in VBA.
:: Questions:
:: 1) Creation of the pivot table takes several steps. First, there is the PivotCaches.add().CreatePivotTable, then I set some table settings, add fields, add data fields, and limit the PageField to a particular value. Is Excel re-calculating the pivot table after each command? Is there a way to turn this off until I get the whole pivot table defined in order to speed macro execution? I have done some Pivot Table VBA in the past and yes they do recalculate as you manipulate them. To stop this you need to set ManualUpdate to True then back to False on completion. Screenupdating should also be turned off. Dim Pt As PivotTable With Pt Application.ScreenUpdating = False .ManualUpdate = True '< Your Code > .ManualUpdate = False Application.ScreenUpdating = True End With End Sub

Great tool though aren't they ?