Archive for the ‘Uncategorized’ Category.

An Even More Better AutoFilter

You knew I wasn’t going to let this go, didn’t you?

I started with snb’s rewrite. I really don’t want to use the SelectionChange event. It runs whenever you move around the spreadsheet and that’s just wasteful. I like how snb did the heavy lifting on SheetActivate, then only burns processors when you change a cell. I probably still need some error checking (and by probably I mean definitely) but here’s what I have so far.

Public gclsApp As CApp

Public Sub Auto_Open()
    Set gclsApp = New CApp
End Sub

Why do I always create my event class and then set the App property equal to the Excel.Application? Why not just do that in the class Initialize event? Stay tuned.

Private WithEvents mclsApp As Application
Private Const msDELIM As String = "||"

Public Property Set App(ByVal clsApp As Application): Set mclsApp = clsApp: End Property
Public Property Get App() As Application: Set App = mclsApp: End Property

Private Sub Class_Initialize()
    Set mclsApp = Application
    If Not ActiveSheet Is Nothing Then
        mclsApp_SheetActivate ActiveSheet
    End If
End Sub

I got rid of the OldValue property as I’m using snb’s method. I added a constant delimeter that I’ll never use in a table header. Then I set my application right in the Initialize event, which I should have been doing all along. Finally, I need to load up the AlternativeText for any sheets just in case it’s not done yet.

Private Sub mclsApp_SheetActivate(ByVal Sh As Object)
    Dim lo As ListObject
    If Sh.Type = xlWorksheet Then
        For Each lo In Sh.ListObjects
            lo.AlternativeText = Join(Application.Index(lo.HeaderRowRange.Value, 1, 0), msDELIM)
        Next lo
    End If
End Sub

This is right out of snb’s code. Join the header into a big string separated by double pipe, then stick it in the AlternativeText property for safe keeping.

Private Sub mclsApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim lo As ListObject
    Dim rLoHeader As Range
    Dim sHeader As String
    'See if the target is in the header of a listobject
    On Error Resume Next
        Set rLoHeader = Nothing
        Set rLoHeader = Intersect(Target, Target.ListObject.HeaderRowRange)
    On Error GoTo 0
    Application.EnableEvents = False
    If Not rLoHeader Is Nothing Then
        Set lo = Target.ListObject
        'if the user starts the entry with two spaces, they want to change the header
        'so don't fire the code, just change the header sans the spaces
        If Left$(Target.Value, 2) = Space(2) Then
            sHeader = Mid$(Target.Value, 3, Len(Target.Value))
            'Filter based on the value typed
            sHeader = Split(lo.AlternativeText, msDELIM)(Target.Column - lo.Range.Columns(1).Column)
            'I ran into that code firing twice problem when I changed this line. I brute forced the
            'sucker by seeing if the Target.Value is the same as the header.
            If Target.Value <> sHeader Then
                'If the user enters more than one value separated by a space, it will filter on all those
                lo.Range.AutoFilter lo.ListColumns(Target.Value).Index, Split(Target.Value), xlFilterValues
            End If
        End If
        Target.Value = sHeader
    End If
    Application.EnableEvents = True
End Sub

First I make sure that the cell being changed is in the header of a ListObject (Excel Table in UI speak). This will disastrously fail (I assume) if you change two cells at once.

Next I added some code that will allow me to actually change the header if I want to. If I precede the entry by two spaces, the code will assume I want to change the header and not filter. Then it removes the two spaces and changes the header without filtering. If I type {Space}{Space}MyDate in the Date field, it will change the header to MyDate and not filter.

Joe commented that you could separate values with a comma to filter on more than on thing. Good idea. I like spaces better, so instead of filtering on Target.Value, I pass the AutoFilter method an array and use xlFilterValues. The Split function produces an array by splitting a String on space.

When I made this change to the AutoFilter method, I ran into my old friend double-event-trigger-for-damn-reason. I beat that problem over the head by checking if the search term was the same as the header – a characteristic of the second bullshit trigger. This introduces a bug when you want to filter the State field on the word “State”. Nothing will happen. I don’t care. I’m done with that problem.

It’s working awesomely and I’m about ready to put it in the PMW to give some real-life test.

One more thing. If you want to filter on partial names you have to include an asterisk. Entering Col* Ala* will give you Colorado and Alaska (from my Sample data – Alabama didn’t make the cut, I guess). If you type *hi*, you’ll get Ohio, New Hampshire, and Washington.

OK, really the last thing. If you want to filter on dates by typing multiple dates, you have to type the full year.

You can download

Back in the Saddle

And we’re back.

Leave your comments admonishing my server maintenance skills here. I promise I will never try to do the right thing again. KNOW YOUR LIMITS! That’s my new mantra.


Well here’s something that I probably learned once, but have subsequently forgotten and then rediscovered: You can filter a PivotTable Page Field just by typing the PivotItem that you want to filter it on. So if I start with a PivotTable like this:
Pivot Unfiltered
And say I don’t like the look of the mysterious substance that the kids have left smeared all over the mouse (probably just jam, but who the hell knows). I desperately want to filter that PivotField, but I desperately want to avoid the mouse. Well, watch what happens if I ust overtype the (All) in the PageField with the thing I want to filter by:
 Pivot Overtype PageField
Pivot Filtered
What’s more, if I type the name of a field that’s not already in the PivotTable over the existing PageField name:
Pivot New PageField
…then Excel does something else intelligent: it says “Oh, you want me to bring that PageField into the Pivottable for you.”
Pivot New PageField Inserted
The same thing in terms of adding new Fields goes for RowFields:
Pivot New RowField
Pivot New RowField Inserted
If you think about it, the adding of the fields is the same behavior as simply overtyping fields already in the PivotTable to rearrange them.

Regardless, now that this secret’s out, I don’t have to chip the sticky crap from my Mouse. As Phyllis Diller once said…Housework can’t kill you, but why take a chance? No, I don’t know who she is, either.

Two Times Table

So if you’ve played around with Tables a fair bit, then you probably would have noticed that you can merrily insert a row in the worksheet that intersects one table:
Insert OK

…but if you try the same thing on a row that intersects more than one table, the Insert, Delete, and Clear Contents options are grayed out (or greyed out, if you live where I live):
Insert Not OK

You may have tried to get around Excel’s veto by cutting or copying a row from somewhere else, then trying to insert it via the ungrayed (or ungreyed) Insert Copied Cells option:
Insert Copied Cells
…at which point Excel wiped that smirk of your face with this:
Not allowed

So I know when this happens. But I don’t understand why. Anyone have any ideas?

Formula Auditing – woes and arrows

I’ve been playing around with the Formula Auditing tools a fair bit recently. These things:
Formula Auditing Tools

In the course of this, I noticed a few things I haven’t before. Firstly, here’s my setup:
Formula Auditing Example_No Arrows
When I have cell D6 selected and click Trace Precedents, Excel is kind enough to draw just one arrow from the precedent range, while putting a box around the entire Precedent range so I can see where it is:
Formula Auditing Example_TracePrecedents
If I were to click on Trace Dependents for that cell, I’d like to see pretty much the same thing:
Formula Auditing Example_DesiredTraceDependents
…but here’s what I actually see:
Formula Auditing Example_TraceDependents
…which looks like something that Hippies were hanging on the walls in the late sixties:
String Picture
…when they weren’t out protesting, that is:
String Protest
Doing a Trace Precedents when there’s a much longer array of dependent cells involved looks even worse:
Formula Auditing Example_TraceDependents_Many
…and Excel becomes very sluggish as you scroll around, so presumably Excel is constantly redrawing these. Scrolling down doesn’t tell you much…that’s for sure:
Formula Auditing Example_TraceDependents_Many2
Let’s take a look at another setup, to better illustrate a couple of things I didn’t know until now:
Formula Auditing Example2_blank
Here’s what Trace Dependents has to say about cell B2:
Formula Auditing Example2_TraceDependents_Level1
One thing I didn’t realise until today, is that if you keep clicking that Trace Dependent button, Excel keeps drawing in additional levels of downstream dependents:
Formula Auditing Example2_TraceDependents_Level2
Formula Auditing Example2_TraceDependents_Level3
Formula Auditing Example2_TraceDependents_OffSheet
In case you didn’t know, you can double-click on any of the blue arrows, and you’ll be taken to the Precedent/Dependent cell concerned…particularly handy if it points somewhere off-screen. And you can double-click the arrow once you’re there to be magically transported back again. The dotted arrow pointing to a little sheet icon in the above screenshot tells you that there’s an off-sheet dependent that points at cell C19, which you can jump to if you double click on that dotted arrow and then select the reference from the Go To box:
Formula Auditing Example2_Goto
…although as you see above, the native dialog box is so narrow that you’re unable to actually see the cell addresses, and can’t be resized. In that case, you might want to download Jan Karel’s excellent RefTreeAnalyser, that fixes this and does a good deal more besides:
Formula Auditing Example_JK_UserForm
It also has a much better way of displaying precedents, by overlaying in the current window some little pictures of any precendents that happen to be out of view or on another sheet. (Would be really handy to have the same functionality for dependents too.):
Formula Auditing Example_JK_Visualise
Colin Legg has some great code that will also help you to determine all on-sheet and off-sheet precedent cells, using the .NavigateArrow method to actually travel up those blue arrows and thus find any precedents on other sheets. I imagine Jan Karel uses pretty much the same approach. [Edit: No, he doesn't ]. You’ve got to use the .NavigateArrow method, because the Range.Precedents property doesn’t return precedents on other sheets or other workbooks.

Now here’s something nasty I’ve just noticed about the native Formula Auditing tool: It doesn’t pick up on off-sheet references that involve any kind of Table Reference, although on-sheet references work just fine:
Formula Auditing Example_Tables offsheet
So it is well broken, in my opinion, because I often refer to Tables on other sheets. And both Colin’s code and Jan Karel’s addin won’t help you here, I’m afraid. [Edit: Jan Karel's code still catches these.] Seems to me the only way to get around this would be to search the worksheet for instances of a Table’s name occurring within formulas. That’s assuming there’s no way to actually read Excel’s dependency tree from wherever Excel maintains it. I seem to recall seeing a post a few years back about how you can extract information from the tree by extracting XML from the workbook file, but that might just be a flight of fancy. Anyone know whether it ispossible to interrogate the dependency tree directly somehow?

How ’bout colours instead of Arrows?

Given all those arrows can get pretty confusing, I thought I’d have a crack at coding up something that lets you use Conditional Formatting instead and/or arrows to highlight Dependents (Green) and Precedents (Blue). Here’s my starter for ten, with both Dependents and Precedents highlighted. Direct Dependents/Precedents get a darker colour and white bolded font so you can easily tell them from indirect:
Formula Auditing Example_Jeff1
…and you can restrict it to just showing direct:
Formula Auditing Example_Jeff2
…and overlay arrows if you want:
Formula Auditing Example_Jeff3
It automatically updates if you change your selection, too:
Formula Auditing Example_Jeff4

It doesn’t solve the Table issue mentioned above, but I’ve been finding it quite handy to get a quick feel for what’s going on in those crappy spreadsheets I inherited…err…designed recently.

Here’s a sample file:

Hardware Recommendations

It seems I have server-issue reprieve, so I’ll take advantage of it by throwing up the first post in weeks. Don’t worry, it’s not Excel related. I mean, who can expect that from this site. I do have a couple posts in the hopper, I just can’t seem to login when I want to write the up.

I got an email from an old friend asking for my recommendations for a new desktop computer. I wrote a longer reply than I expected and figured I might as well post it here. That way you can give your two cents in the comments. First, I’ll summarize his question/requirements:

I want “serious scientific/number-crunching/math-oriented”, and things labeled “home”, “office”, and “gaming” don’t seem like the right fit. I would like to use this machine primarily for compiled Python, S Plus, Mathematica, and C++ dll’s I am coding for Excel and OpenOffice. Specific points:

  1. Is 8 GB RAM enough? Speed-wise, will 16 or more make a “significant” difference?
  2. Which processor: i7?
  3. Will a hybrid drive make any difference? Incidentally, I keep all of my documents on my NAS; I would, of course, keep development versions on the local HD, but really don’t need more than 150 MB (if that!) on it.
  4. Dell looks good – highly configurable — what’s your take?
  5. Monitor/Video Card: One of my Macs is 27″, and is awesome for my wife’s photo/cine avocation. I am writing you from a 17″ Mac ProBook. Works for me. So I won’t need a particularly large monitor (which one?!?); what I should do re the video card?
  6. I/O: Several USB 3.0; Wired Ethernet, of course; Bluetooth; Thunderbolt. DVD and SD drives would be great.

Here’s my reply:

I don’t profess to be an expert, but I’m happy to give you my opinion. Just take it for what it’s worth. My last three desktops were 1) custom built 2) HP HPE-150f and 3) custom built.

My Last Three Computers

  1. I read about how he built his own home theater PC. I was cutting cable and needed something to run Windows Media Center for over the air TV. I somewhat followed his lead, but since mine would be out of sight, I didn’t worry about making it small or quiet. I wouldn’t recommend my particular build for your purposes because it was built with the new (at the time) Intel chipset that had HDMI out built-in.

    I was in a situation where I didn’t trust Dell anymore, didn’t know who to trust, and was interested in trying my hand at building hardware just for the experience. I made some errors along the way, but all-in-all it has been an outstanding machine. My biggest mistake was not buying a big enough power supply. I also had a hard drive go bad in the first year. Even with the missteps, the whole cost was about $1,400.

  2. I was ready to replace my Dell desktop because it was 7 years old. I had a reasonably good experience building a PC so I was going to go that route again. It was a different build, so I there was still some chances to make mistakes, but I priced it out on New Egg anyway. Then I went to HP to see what a comparable machine would cost. It was a few hundred dollars cheaper and it didn’t really make sense for me to build it myself. I got an i7 2.8Ghz with 8 GB.

    That was four years ago, or so, and this machine has been terrific. Zero problems and it does everything I want performance wise. My only problem with HP is that they have a lot of craziness going on – they announced they were getting out of the PC business, then retracted. It just gives me pause. We use HP exclusively at work (~1,000 desktops) and they are probably still my go-to manufacturer.

  3. The third desktop was for my son. It was still a better deal not to build it, but I wanted to give him the experience of building a PC from srcatch, so we went that route. It was a pretty good experience and it has been a good machine. If you’re interested in building your own, I’ll send you the component list.

Operating Systems

I run Win7 and my son runs Win8. He prefers Win8, but kids are stupid, so what are you going to do. On my HTPC, if I upgrade to Win8 I have to pay extra for Windows Media Center. In other words, I’m never upgrading. And when Win7 doesn’t work anymore, I’m going Linux/XMBC for the HTPC. Sorry Microsoft, but when you stopped building problems for guys like me you should have expected this.

Your Specific Questions

  1. I bought my HP with 4 GB (the stock amount) and bought another 4 from You can save some pretty good money that way, but price it both ways. Just make sure you get enough slots if you’re going to add after market RAM. If I were buying today, I wouldn’t get less than 12 GB because RAM is so cheap that even an incremental increase in performance is worth it. I’d look at 16 too. But really, I’ve been running 8 GB for a while, and I have no problems, so it might be a waste of money.
  2. I like my PCs to last 5-7 years, so I tend to over buy on CPU and RAM. I don’t what’s newer and better than i7, but I definitely would not go with i5.
  3. Another misstep on building my own was not having an SSD for a boot drive. If I were building again, I would get an SSD for a primary and a Western Digital for storage. SSDs are getting more reliable at a pretty quick pace.
  4. Six years ago, or so, I stopped buying Dell. I bought them exclusively for home and work for the 10 years prior, but the quality of the components was going down and I’d had enough. I’ve heard they’ve made a bit of a comeback, quality wise, but I’m not sold yet. Concurrent with them buying cheaper parts, they moved their customer service to India. Back in the day when I didn’t have to talk to Dell customer service because their shit just worked, I wouldn’t care where it was. But with failing parts, it was a problem. I’m not trying to denigrate an entire sub-continent. It was just that the company Dell hired really sucked. I’m sure the rest of India is very knowledgeable.
  5. I have two 22″ monitors side-by-side and love it. I can’t imagine coding in Excel without the VBE on one screen and Excel on the other. Even when I code in Ruby, Python, Java or whatever, I need to have stackoverflow on one screen and the IDE on the other. I will never have one monitor again – it is the single best productivity boost in my computing career (including my keyboard shortcut mania). Both my monitors are Acer that I got from Newegg. They’re slightly different heights, but I find that piano sheet music books level them up nicely. If you can get identical, that’s even better. My work monitors are identical 22″ ASUS and I’m very happy with those too.
  6. Don’t sacrifice on USB ports. They don’t all have to be 3.0, but make sure you have double what you think you need. Even if you don’t go with two monitors, you should consider a dual output video card in case you change your mind.

I’m a couple years away from replacing my desktop, but if I were forced to do it, I would price out HP, Lenovo, and Asus. And probably Dell for good measure, but I would have to be really convinced to go back to Dell. Lenovo and Asus have awesome reputations in laptops and I don’t know why that wouldn’t translate to desktops. I bought a used Asus convertible tablet several years ago and it still runs like a champ.

End of reply

If you have any opinions on desktop hardware, please leave them in the comments.

Servers Suck

Hey, did you notice that this page loaded in a few milliseconds instead of timing out? Me too!

I don’t know what caused that period of slow down because I don’t know much about servers. I know my CPU percentage has been higher lately. Here’s what it looks like over the last 30 days.

And over the last 24 hours.

Last night I created a support ticket with Digital Ocean. And today it miraculously works. It makes me suspicious, but I can’t imagine a business model where you throttle your customers until they call you on it. I mean except for the cable and cell phone industries.

So I’m going to enjoy it while it lasts. I’m also going to get in an Excel post today before I have more problems.

Sync Pivots from dropdown

Over at the Excel Guru forum, Yewee asks:

I have 3 sheets in my excel worksheet.

1. Org
2. DataSource
3. Pivots Table

My Pivot table will get the data from the DataSource sheet. I will like to have the filter of the Pivot Table from one of the cell in Org Sheet.

How can I do that?

Incredibly easily, if you have Excel 2010 or later…because:

  • a PivotTable with nothing but one field in the Filters pane looks and behaves pretty much exactly like a Data Validation dropdown does; and
  • that PivotTable can be hooked up to the other PivotTables via slicers, so that it controls them.

If you’re a long-time reader of this blog you probably already know that, and may want to skip to the end to find a bit of VBA that makes setting up Slicers slightly more easy. But if you came here via Google, then pull up a pew and read on.

So let’s say these are the two Pivots that you want to control via a dropdown, and you want to put the dropdown where the red rectangle is:

Two Pivots and target


First, create a new PivotTable from the datasource that the other pivots share (or make a copy of one of the existing Pivots) and in the PivotTable Fields pane add the field you want to filter the other Pivots by to the Filters pane. (If you created this Pivot by copying another, remove any other fields that might appear).

Faux DV and Fields List

Great: Now you have a PivotTable masquerading as a Data Validation Dropdown. From now on, I’ll call it the ‘Master Pivot’. So just drag that Master Pivot where you want it:

Faux DV and Pivots


From the ANALYZE tab of the PivotTable Tools contextual menu in the ribbon, click the Insert Slicer icon:

Insert Slicer


…and from the menu that comes up, choose the field name that matches the field you put in the Master Pivot:

Chosen field

…and your slicer will magically appear:

Slicer added


Now we connect that Slicer to the other PivotTables. To do that, right click on the Slicer that just appeared, and click the Report Connections option:

Right Click


You’ll see from the Report Connections box that comes up that currently it’s only connected to one PivotTable – which of course is the Master PivotTable that we used to insert the slicer in the first place:

Report Connections Master


What we want to do is connect it to the other PivotTables, by checking those other checkboxes:



(Optional) We might want to make it so that the user can only select one thing at a time by clicking on the Master Pivot filter dropdown, and unchecking Select Multiple Items, if that’s your intent:

Dont select multiple items


…and now all we need to do is move that Slicer somewhere out of sight (but don’t delete it):

Faux DV and Pivots


Now when we select a region from that Master Pivot dropdown…

Select Region

… all the other Pivots are filtered to match:



That’s it…job done. As simple as possible, and no simpler.

Actually that’s a lie…unless there’s a good reason not to, it’s much simpler just to use a Slicer in the first place, and not bother with setting up the Master Pivot dropdown at all:

Just Use Slicer


Of course, that Slicer takes up much more room than our Master Pivot dropdown. So maybe that’s a good reason to use the Master Pivot approach, and not a slicer. Especially if we might want more than one dropdown to control all the Pivots and space is at a premium:

Multiple Dropdowns

Or you can do away with the Master Pivot altogether, and just set the slicers up between the actual ‘output’ pivots themselves, so that as soon as they change a PivotFilter setting in one of the Pivots, the others get changed too. (Note that this also happens with the ‘Master Pivot’ approach…it’s just that we don’t actually need to have that Master Pivot sitting there taking up space at all).

Programatically add and connect Slicers

I’ve always found it annoying that there’s no right-click option to add a Slicer to the currently selected PivotField. Plus connecting Slicers to multiple PivotTables is a drag. And also, I hate it how it adds new Slicers over the top of old slicers. So here’s some code that remedies all that:

Sub AddSlicer()
Dim pt As PivotTable
Dim ptOther As PivotTable
Dim pf As PivotField
Dim pc As PivotCache
Dim rng As Range
Dim sc As SlicerCache
Dim varAnswer As Variant
Dim bFoundCache As Boolean
Dim rngDest As Range

Set rng = ActiveCell

On Error Resume Next 'in case user has not selected a PivotField
Set pt = rng.PivotTable
Set pc = pt.PivotCache
Set pf = rng.PivotField
On Error GoTo 0

If pt Is Nothing Then Exit Sub

If pf.Orientation <> xlDataField Then
    Set rngDest = Intersect(ActiveCell.EntireRow, ActiveCell.Offset(, ActiveCell.CurrentRegion.Columns.Count + 1))
    On Error Resume Next 'SlicerCache might already exist
    With rng
        If pt.PivotCache.OLAP Then
            Set sc = ActiveWorkbook.SlicerCaches.Add2(pt, .PivotField.CubeField.Name)
        Else:  Set sc = ActiveWorkbook.SlicerCaches.Add2(pt, .PivotField.Name)
        End If
       sc.Slicers.Add SlicerDestination:=ActiveSheet, Top:=rngDest.Top, Left:=rngDest.Left
    End With
    If Err.Number > 0 Then 'SlicerCache already existed. Work out what it's index is
        On Error GoTo 0
        For Each sc In ActiveWorkbook.SlicerCaches
            For Each ptOther In sc.PivotTables
                If ptOther = pt Then
                    bFoundCache = True
                    Exit For
                End If
            Next ptOther
            If bFoundCache Then Exit For
        Next sc
    End If

    varAnswer = MsgBox(Prompt:="Make Slicer control the " & pf.Name & " field in all Pivots on the same sheet?", Buttons:=vbYesNo)
    If varAnswer = vbYes Then
        For Each ptOther In ActiveSheet.PivotTables
            If ptOther.CacheIndex = pt.CacheIndex And ptOther.Parent.Name = pt.Parent.Name Then
                sc.PivotTables.AddPivotTable ptOther
            End If
    End If

Else: MsgBox "You can't add a Slicer to a Values field."
End If
End Sub

In addition, the below code will add the Add Slicer icon to the right-click menu that comes up when you right click on a PivotField:

Option Explicit

Private Sub Workbook_Open()
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Sub AddShortcuts()
    Dim cbr As CommandBar

    Set cbr = Application.CommandBars("PivotTable Context Menu")

    With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
        .Caption = "Add Slicer"
        .Tag = "AddSlicer"
        .OnAction = "AddSlicer"
        .Style = msoButtonIconAndCaption
        .Picture = Application.CommandBars.GetImageMso("SlicerInsert", 16, 16)
    End With

End Sub
Sub DeleteShortcuts()
    Dim cbr As CommandBar
    Dim ctrl As CommandBarControl
    Set cbr = Application.CommandBars("PivotTable Context Menu")

    For Each ctrl In cbr.Controls
        Select Case ctrl.Tag
        Case "AddSlicer"
        End Select
    Next ctrl
End Sub

…meaning whenever I right click on a PivotField I get this:



Clicking on that adds a Slicer to the selected field automatically, plus asks you:

Control all pivots


Hell yes, I do!

Here’s a sample file: