Excel Pivot Tables Stopping Re-calc in VBA

I'm not sure why this was so hard to find, but once I did it seemed obvious. Excel refreshes a pivot table after each change. This can become very time consuming if you are changing multiple field settings. The below code modifies the "ManualUpdate" property of a pivot table so any changes you make will not be calculated until you either turn off manual updating or force a refresh.

Dim ws As Worksheet
Dim PvtTbl As PivotTable
Set ws = ActiveWorkbook.ActiveSheet
Set PvtTbl = ws.PivotTables("MyPivotTable")

'Turn on manual updating
PvtTbl.ManualUpdate = True

' Enter your pivot table code here
' If you need to refresh the pivot table within this block
PvtTbl.RefreshTable

'Turn off manual updating
PvtTbl.ManualUpdate = False

Excel Pivot Tables in VBA Part 2 (Modifying Field Selections)

This uses information from Part 1 of the Pivot Table posts. It can be found here.

There are several ways to change the field selections in a Pivot Table. The two that I use most could be viewed as 1, changing the text of the selection directly, and 2, making the field multi select and selecting the fields you want. To the code (I am jumping right in so check out part 1 if you feel lost).

' Selecting everything in the field
' Current Page method
PvtFld.CurrentPage = "(All)"

' Individual Pivot Item method
For each PvtItem in PvtFld.PivotItems
PvtItem.Visible = True
Next PvtFld

Since these are not exactly clear methods, at least to me, I think a little additional explanation is needed. When using the Current Page method you can think of it as assigning a text value to the field itself, as long as the item exists within the field selections. If it doesn't it will throw an error. When using the Pivot Item Visible method you can think of this a turning the checkbox on and off for each item in the field. I'm not sure why they chose to use "Visible", but it does work . The nice thing about this is that it allows you to do a comparison between each item in the field and will not error out if the compared item doesn't match (assuming you have an 'Else' statement of some variety).

Excel Pivot Tables in VBA Part 1 (Selecting Pivot Table Parts)

Pivot tables in Excel are a life saver for many people, but moving from standard cell modification to modifying pivot table fields can be a bit more confusing. The first thing is to setup some VBA code to identify each part of the pivot table. I'm using the default names in the example below, but I always try and name everything, it makes things easier in the long run.

' Pivot Table
Dim PvtTbl as PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")

' Pivot Field (the pivot table filters)
Dim PvtFld as PivotField
Set PvtFld = PvtTbl.PivotFields("FieldName")

' Pivot Item (the selectable items within a Pivot Field these may be optional based on the items in your field)
For each PvtItem in PvtFld.PivotItems
If PvtItem = usrInput Then
PvtItem.Visible = True
Else
PvtItem.Visible = False
End If
Next PvtFld

Message Box User Input in VBA (I Always Forget This)

VBA isn't know for it's user interface ability, but sometimes it's nice to capture the user input from a message box. I believe that all buttons in a message box have values, but I typically use this when I offer the user a Yes/No option (i.e. "This is going to do something that may take some time. Do you want to continue?")

Simple Example (Excel 2010):

Dim userInput as Integer
userInput = MsgBox("What do you choose?", vbYesNo, "User Choice")
If userInput = 6 Then '6 = Yes
'Enter "Yes" code
ElseIf userInput = 7 Then '7 = No
'Enter "No" code
End If

A couple of notes. When you want to capture the selection from a message box you do need to use parenthesis around the message box properties. The integer values from the Yes/No responses may be different values based on the version of Excel being used. An easy way to check is to throw a break point right after assigning a value to the userInput variable.

Select Case in VBA

I keep forgetting to add things as I build my new project so I am going to make an attempt to add these as I use them. The posts will be smaller, and each will focus on a single item.

Select Case
Case
Case
...
Case Else
End Select

Very Simple Example:

Dim myval as string
myval = ActiveCell.Value
Select Case myval
Case "Blue"
MsgBox "Cell value is Blue"
Case "Red"
MsgBox "Cell value is Red"
Case "Green"
MsgBox "Cell value is Green"
Case Else
MsgBox "Cell value not recognized
End Select

Pulling An Excel Column Letter in VBA

The formula below will return just the column letter based on the active cell. This can be modified to return the column letter for any referenced cell. There are many ways to do this, but this was one of the more elegant solutions I came across in my search.

ColumnID = Mid(ActiveCelll.Address, 2, InStr(2, ActiveCell.Address, "$") - 2)

Speeding up VBA code Execution

I've seen this covered by many people, but I wanted to cover the eight lines of code to add that will allow you to see a very marked improvement in VBA code performance. If you do a lot of cell modification or tab changes using VBA then using this code will greatly improve your codes execution. This first set turns everything off

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

Your code section would go here

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True

The last section turns everything back on. I have seen several flavors of the above code. One was built to check the current state then shut everything off and then return to the original state at the end which would be a better user experience, but for the code I was building I wanted to keep everything simple and just shut everything down and turn it all back on. I did see a marked improvement in code performance using this (approximately 60% speed increase).

A few things to note regarding using these code segments. Think about how you want to utilize this, setting ScreenUpdating to "False" essentially freezes the Excel screen that the user sees until you return the status to "True". If you have a DB pull that takes 5 or 10 minutes to execute you may want to find some way to show the user that things are still working. I built a small tool that connected to an Analysis Services cube and pulled down dimension and hierarchy data into separate tabs and tables. I used this code on the table generation code so I could have an easy way for the user to see that things were still working, but it still cut out a lot of processing time by not showing the table being created and sorted.

Also, do yourself another favor and comment this code out until you are finished with the code segment you are working on that is going to get the speed boost. Being able to see each thing happen as it is called by the VBA code is a quick way to resolve potential bugs that may not be related to code.