Update

I realized that a few people have actually been visiting this site so I wanted to provide a brief update. My last publish was back in 2011 and soon there after I was hired on at Microsoft full time. What I thought was going to be a great experience and adventure turned into one of the the most difficult and trying experiences of my life. I may create a new post covering the topic, but for now I will say that I escaped in December to a small consulting company in Redmond called Brightlight Consulting.

Part of this move was to become a more well rounded BI developer and learn Linux and Netezza. I have been working in the Microsoft BI stack for 6 years and in Excel for 10 but after my Microsoft experience I just didn't like them anymore and had no desire to work with Microsoft products again. Hopefully with time I'll get the interest back, but right now I'm excited to be learning something new.

I will be continuing to update this site here and there as I develop Excel reports and tools in my new position. Thanks to those that are viewing this and hopefully you have found something useful.

More Excel Notes

I realized that I haven't posed since November. I'm not doing as well as I had planned. I'm also in the final stages of releasing a global reporting tool for my current client so I've been spending a lot of time getting things polished and working with the test team. However, I do have some new things to add. Simple stuff, but really helpful.

The tool I have been working on for the past two months uses a text box for a report name, that name is dropped into the Excel file as the tab name as well so I've had to do some checking of the name to make it tab compatible. Here is what to look for.

Excel only allows 31 characters for a tab name. This can easily be limited in the "MaxLenght" property of the text box. However, based on how you are using this users may need some additional information. In the tool I built I added a simple counter at the end of the text box that shows the user the current number of characters against the max allowed i.e. "0/31". Then used the text boxes "Change" event to update the first number based on the user input with this code:

charAvailable.Caption = CStr(Len(reportName.Text)) & "/31"

Another thing to check is any invalid tab characters in the text box. Excel does not allow :, \, /, ?, *, [, or ] to be in a tab name. There may be an easier way to do this but I simply use the Replace function to replace an invalid character with whatever other acceptable character I want (in most cases either an empty string "" or a space " "). Here is the code:

RptName = txtRptName.Text

RptName = Replace(RptName, ":", " ")

{same code as above for remaining invalid characters}

txtRptName.Text = RptName

Again, there may be a simpler way, but this way works and it's relatively fast since there is a max of 31 characters for the string.

Two more checks that I run are checking for blanks and checking for completely numeric names. Both of these are done via if statements. Here is the code:

If txtRptName = "" Then

End If

If isNumeric(txtRptName) Then

End If

I built a function called UserForm_Validation that returns True or False where all of this code runs, each section will exit the function with a setting of False when a validation error is found. The call on the main source line will exit the code completely if a value of "False" is returned.

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