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.

Excel, VBA, Pivot Tables, and MDX Oh My!

Marco Russo on SQL Blog.com details a really simple way to pull the MDX for a pivot table using VBA when you are connected to an Analysis Services cube. Below is the cut down version that I added to my data analysis toolbox in Excel (the full version can be found on Marco's blog here):

Sub ShowPvtMDX()

Dim mdxQuery As String

Dim pvt As PivotTable

Dim ws As Worksheet

Dim x As Integer

Set pvt = ActiveCell.PivotTable

mdxQuery = pvt.MDX

Set ws = Worksheets.Add

ws.Range("A1") = mdxQuery

End Sub

That's it. If you connect this code to a key command in Excel all you have to do is select the pivot table and execute the key command and it will automatically create a new tab and put the MDX in cell A1 of that tab. I have tested this code in Excel 2010 and it does work, but it only works with pivot tables linked directly to a cube. Also, while it will work as long as you have a cell within the pivot table selected it returns the MDX used to create the entire pivot table, not just the cell you have selected.

VBA and Error Handling

I almost put this in the post on "Understanding VBA" because it is really weird. Anyone familiar with classic BASIC or QBASIC will remember the GOTO function. I actually thought they had eliminated this in most modern programming languages due to the code structure you could end up with when using too many GOTO statements (spaghetti code was what I remember it being called). Even though I really wish VBA would adopt the Try… Catch… Error… model, it hasn't and we're stuck with using the GOTO statement.

There are several things to remember when handling errors in VBA. These should be familiar to anyone that programs on a regular basis:

First, never, ever let your code have a section that could error out unhandled. VBA is not forgiving. It will dump your user right into the code AND ALLOW THEM TO EDIT IT. You can put a password on your code if you have to and I would recommend it, but you should still never allow any user to even see the VBA window asking for a password. Always error out gracefully

Second, make sure everything has a general error handler around it. I've seen users do weird things so it is always better to have more error catching than needed.

Third, try and make the errors specific to certain code parts. Imagine that you create an automation tool for Excel that gets sent to 20 users and you are going to be the recipient of each and every error that is raised from each user. You don't want to have them emailing a screenshot of your generic error window with "Sorry, the program experienced an error. Please try again." Think about what information you will need to resolve an error. Where could your code error later that it's not erring now? Database connection time outs are a good example. Give yourself as much information as possible in the event your program does error out.

Fourth, refer to the first thing. This will be crucial to your tools adoption. The user needs to feel like they are getting a good resolution rate on any bugs they run across. Good error handling is critical. Just think of any error window you have seen that didn't make any sense to you. Now imagine you are the guy on the other end trying to resolve it from a screenshot.

Fifth, this was also part of the first item, but the first item was important to I am going to reiterate both points again. Once you have tested your code and you think it's working great protect your code (in the VBA menu click "Tools" then click "Project Properties" then the "Protection" tab). This is a quick and simple step that will prevent you from having someone else create "version b" while you were on vacation (thanks to Google everyone thinks they can become a programmer).

Now for the error handling example:

On Error GoTo <Label>

'Code

Exit Sub

<Label>:

'Error Code

Typically I'll label my general error catch code as "errHandler" so the code will look like this:

On Error GoTo errHandler

'Code

Exit Sub

errHandler:

'Error Code

Note the "Exit Sub" in the above code. Make sure you put this statement right before your error label. VBA doesn't care if a label is used for error handling. It will continue processing your code until it reaches the end, including any code that you have in your error handling section. It's very disturbing see error messages pop up and not find any errors in your programs.

You can have multiple On Error statements in the code, but the thing to remember that if an error is raised it will call the last On Error statement so if you have a database connection that you want to have different error output you could do something like this:

On Error GoTo errHandler

'Code

On Error GoTo DBerrHandler

'DB Code

On Error GoTo errHandler

'More Code

Exit Sub

errHandler:

'Error Code

Exit Sub

DBerrHandler:

'Error Code

Notice the "Exit Sub" in the errHandler code? I call this the "Nice try, but try again" error style. Setting up your error coding like this force the program to exit whatever it was doing and requires the user to retry the same thing they just did. This may not be a big deal if you don't have many steps or the code doesn't take long to execute, but in most cases you only want to use this if the error is critical and you want to quit the program (i.e. the program was unable to connect to a DB and pull data). This goes back to providing the user with a good experience. If the user didn't enter something in a form that causes an error, then maybe you'll want to do some form validation before you begin the next bit of code.

There are other options for error handling in VBA, "On Error Resume Next" is a good example. I haven't used this unless I know an error could be raised and I want to completely ignore it.

What I have showed here are extremely simple examples of error handling code. When you are building something that needs error handling you will want to make sure that anything you load is unloaded prior to you exiting the code. You may end up eating a lot of your computer's resources and not realize it, especially if you use Excel all day long. I once built an application that opened excel doc but didn't make it visible. However, when it erred out it wouldn't close the excel program and I had to go into the task manager to close the excel instances. During one intense testing session I managed to open over 10 instances of Excel and had to close them all manually. Handle the errors as gracefully as possible and unload the resources you have opened, it will save your project.

Understanding VBA

If you work with any of the Microsoft Office products then you probably have some interaction with VBA even if you haven't programmed it directly. VBA is short for Visual Basic for Applications, and is the code generated in the background when recording a macro in most of the Microsoft Office products. VBA has its advantages and disadvantages, but if you need to automate something in Excel, Word, Access, or Outlook then VBA is your quickest route to get there.

If you are familiar with programming than VBA will seem a bit off. Even if you are familiar with programming in Visual Basic VBA will have some oddities that may frustrate you to no end. It is defiantly possible to program Office functionality using another language, but this will require the additional step of creating a COM Add-In. VBA allows you to skip this step and reference your code directly from the application via either a custom Ribbon Tab, or shortcut combination.

As for some of the oddities, here are the ones that I have run across so far:

Functions return a value based on the name of the function not a variable values set within the function. This one threw me the first time I ran into it, but essentially if you have a function called "Foo" then you set "Foo" equal to the results of you function and call return. Here's a quick code sample:

Function Foo(ByVal x as Integer) as Integer

x = x + 1

Foo = x

Return

End Function

Variable based array dimensioning has to be done using the ReDim command and cannot be done during the initial dimensioning. This one took me some time to resolve as well because it doesn't make any sense. VBA allows you to set the array length with a static value easily:

Dim MyArray(10) as string

However you cannot use a variable:

Dim ArrayLen as Integer

ArrayLen = 10

Dim MyArray(ArrayLen) as String

If you do want to set the length with a variable you have to use the ReDim to assign the value:

Dim ArrayLen as Integer

ArrayLen = 10

Dim MyArray() as String

ReDim MyArray(ArrayLen) as String

Another one that has caught me several times is the switching back and forth between 0 and 1 as the initial count start. If I want to do something similar to a substring search I would expect that the starting location within the string would be 0 if I wanted to start the search from the beginning of the string, not the case. 1 is the value used for the start of the string in the InStr function in VBA.

InStr(0, MyString, MySearchString) ' Will Error out

InStr(1, MyString, MySearchString) ' Works

Thankfully Arrays start their count at 0, so it should match other programming languages you may have used. This may be prevalent in other functions within VBA, but this one catches me all the time.

Getting the variable length in VBA is also different. For the longest time I was using .NET form of variable.Length, and there are certain functions or variable types that can use that. However, if you want to get the length of a regular string variable you need to use the Len() function.

I'm sure there are many more of these, and I will continue to update this list as I run into them, please feel free to add any of the quirks you have found in the comments.

Rapid Tool Development Process

At some point we've all been to build a custom tool to do some specific task, or you end up in a situation where a small bit of automation will help reduce the number of hours spent on a task significantly. Now there are many flavors of software development processes and what I discuss here is not a new team process, if I was going to advocate a team process I'd recommend Scrum with a good Scrum Master, but each situation is different. Now, for a quick automation tool you typically need to deliver working software as quickly as possible with minimal impact to the "regular" work you have to do.

The best process I have come up with to date uses some of the processes from XP and Scrum programing processes, but focuses on project breakout and functionality building (there may be an actual process like this already, but I haven't read about it yet). The analogy I would use is building a fence.

When you build a fence you start with the general idea of where you want the fence to go. It's similar to knowing what you want the tool to do in the end. For example, you want to create a tool that automates the testing of an excel report by comparing the values in the report to an Analysis services cube. Now you can start from scratch and try to code all the way to the end, and some people may be able to do this, but for the vast majority of us mortals it's best to break this up into multiple tools.

This is the second step in the fence analogy, planning where you want the fence posts. When thinking of the whole tool try and break it out into smaller tools that you can automate easily that will either allow you to have several small steps or several pieces of reusable code when finishing the automation. For our Excel test automation tool this could be broken out into: Reading a report, determining how a cell value is generated, Reading a cell formula, and pulling data from Analysis Services. There are probably more, but this is a good start.

So now what we have is a list of ideas or smaller tools that comprise our overall goal. One thing to keep in mind while determining the breakout of the tool is to focus on something that you can use when you're done, it may not result in the automation tool you wanted when you started, but you can show progress on a regular basis if you need to, plus this allows for multiple break points during your development process allowing you to work on your everyday tasks.

Once you have built your smaller tools you can focus on the final part of the project. To go back to our fence analogy this is connecting the posts. This task is not going to be an easy one, but it with the tools you've already built a good 80% to 90% of your code should already be developed, all you are doing is linking the individual parts together. This could take some tweaking based on how you put coded each tool, but in my experience the modification required here is minimal.

One critical rule to follow throughout this process is to stop often and think about what you are trying to accomplish and what the time to impact ratio is. If you get stuck repeatedly maybe it's time to bring a contractor in for a short time and get version one out the door for you. Really keep focused on what your efforts are saving you or your team/company. If you have to spend 40 hours a week for 5 weeks and this is only going to save you a few minutes a day then maybe you should rethink what you are trying to do. I will be the first to advocate that learning something new is cool and fun, but you probably have a regular job that needs attention, plus family and friends. So pouring 80 hours a week into something that will take several years to see ROI may not be the best use of your time. I have seen VBA tools have significant impact though. I've reduced testing time on Excel reports by 50% just by creating a few tools that helped do some of the repeated tasks done during each test period (the gain was measured in days and hours, not minutes, these were huge reports).

Also, don't give up on your project. It may seem like a huge mountain to climb at first, but just focus on creating that first fence post, then the second. Even if each individual piece of code never get joined you could still end up with some simple tools that save you a lot of time.

Don't get stuck in a rat hole either. If you find yourself spending days trying to solve one problem, maybe it's time to move to the next piece. Sometimes stepping away from the problem helps you solve it.