International Keyboard Shortcut Day

Did you know that International Keyboard Shortcut Day is the first Wednesday in November? Considering I just made that up, I’ll bet you didn’t. But I hereby proclaim it so.

IKSD was created to spread awareness that you can become more efficient by knowing and practicing keyboard shortcuts.

How to Celebrate
From 2:30PM to 3:30PM your local time on the first Wednesday in November, don’t use your mouse for any computer activity. There are three levels of participation:

Novice: You’re intrigued by the promise of efficiency, but skeptical the investment will pay off. To observe the day, you’ll move your mouse to the opposite side of your keyboard for that one hour.

Journeyman: You’ve already learned quite a few shortcuts and you just need the discipline to apply them. To observe the day, you’ll use the keyboard in every program except in your browser for that one hour.

Fanatic: You don’t need any convincing that this is the greatest thing since electronic spreadsheets. To observe the day, you will disconnect your mouse for that one hour.

I thought of this on my way to work this morning when I (finally) started listening to Chandoo’s shortcut podcast.

Committing to one hour of keyboard only computing is an investment. As with any investment, there will be costs up front. This will be the most unproductive hour of your year. Everything will take longer than it should as you struggle to find the keyboard way of doing things. But it will pay off. During that hour you will learn one shortcut that will stick and it will pay dividends for rest of your life.

Don’t forget to tell your friends.

Putting together an Excel VBA course

Hi everyone!

I’m putting together an advanced VBA course and I am looking for feedback, as I plan to make this a top-notch training.
If you have a couple of minutes to spare (or perhaps are interested in attending an advanced VBA training in The Netherlands), please fill out my survey

Thank you in advance!

Jan Karel Pieterse

Autohotkey for WordPress Blogs

I’ve added some autohotkey dealies for when I’m writing blog posts. If you’re blog isn’t about Excel and/or doesn’t use the CodeColorer plugin, they won’t be of much use to you.

; AutoHotkey Version: 1.x
; Language:       English
; Platform:       Win9x/NT
; Author:         A.N.Other 
; Script Function:
;	Template script (you can customize this template by editing "ShellNew\Template.ahk" in your Windows folder)

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

GroupAdd, DDoEPosts, Edit Post ‹ Daily Dose of Excel — WordPress - Mozilla Firefox
GroupAdd, DDoEPosts, Add New Post ‹ Daily Dose of Excel — WordPress - Mozilla Firefox
#IfWinActive ahk_group DDoEPosts
    SendInput [cc_vb][/cc_vb]{left 8}
	SendInput [cci_vb][/cci_vb]{left 9}
	SendInput PMW
	SendInput VBE

The first one expands brackets so I can paste in VB code. The second does the same but for inline vb code.

The third and fourth take common abbreviations and expand those so the definition appears when you hover over them. More of those to come, I’ll bet.


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

A Better AutoFilter

Jeff recently wrote about how you can type your filter criterion in a Pivot Table’s page field and it will filter it automagically. That’s awesome. I want the same thing when I filter Tables, so I started doing some experiments. To filter a table, you select the header, press Alt+{DOWN}, e to get to the search box, and type the search term.

Press Enter

I want to type ‘Colorado’ right in the header and have it filter.


How did I accomplish that magic? First I created a class module call CApp. It will be used to house my application level events. Up in the declarations section of CApp, I have this

Private WithEvents mclsApp As Application
Private msOldValue As String

Public Property Let OldValue(ByVal sOldValue As String): msOldValue = sOldValue: End Property
Public Property Get OldValue() As String: OldValue = msOldValue: End Property
Public Property Set App(ByVal clsApp As Application): Set mclsApp = clsApp: End Property
Public Property Get App() As Application: Set App = mclsApp: End Property

The mclsApp variable is declared WithEvents so that VBA exposes all the events of the Application object to me in this module. I’ll be using two of those events, SelectionChange and Change, to determine when to filter. The OldValue variable will hold the header that I’m overtyping so I can put it back. For instance, when I replace State (the column heading) with Colorado (the search term), I need to put the heading back to State.

To capture that old header value, I use the SheetSelectionChange event. Whenever the selection changes, this procedure is run.

Private Sub mclsApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    Dim rLoHeader As Range
    'See if the target is in the header of a listobject
    On Error Resume Next
        Set rLoHeader = Intersect(Target, Target.ListObject.HeaderRowRange)
    On Error GoTo 0
    'If it's in a header, save the header's column heading
    If Not rLoHeader Is Nothing Then
        Me.OldValue = Target.Value
        'Otherwise, clear the old value
        Me.OldValue = vbNullString
    End If

End Sub

If I’ve select a cell that’s in the header of a ListObject (that’s what VBA calls a Table), then save the value. This is just some test code. It needs far more error proofing, such as making sure only one cell is selected.

Next I use the SheetChange event to monitor if I type a new value in that header. First I disable events so that when I put the old header value back, it doesn’t think I’m trying to filter again.

Private Sub mclsApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim sFilter As String
    Application.EnableEvents = False
    If Len(Me.OldValue) > 0 Then
        'Save the search term for later filtering
        sFilter = Target.Value
        'Change the header value back
        Target.Value = Me.OldValue
        'This shouldn't be necessary, but read on
        Me.OldValue = vbNullString
        'Filter based on the value typed
        Target.ListObject.Range.AutoFilter Target.ListObject.ListColumns(Target.Value).Index, sFilter
    End If
    Application.EnableEvents = True
End Sub

I really don’t mind using the built-in autofilter string of keystrokes when I’m filtering on a string or a number. But dates? That’s another story. I hate autofiltering on dates. If I want to filter the above list on June 22nd, the keystrokes are: Alt+{DOWN}, e, {TAB}{TAB}, {SPACE} to uncheck Select All, {DOWN}{DOWN}{RIGHT} to expand June, 2 2 {SPACE} to get to the second entry that starts with a ‘2’ and check it, {ENTER}.

Stupid. I should be able to get to the search box and type 6/22 and have it filter. But it doesn’t. I though this method would make filtering on dates much better. And I was right.

Did you happen to see the comment in the above code about a particular line not being necessary? I didn’t want to remove OldValue in the SheetChange event because that’s the job of the SheetSelectionChange event. I shouldn’t need to do it. I didn’t need to do it for filtering on strings, but without it, I can’t filter on numbers or dates. For some reason that I couldn’t figure out, the SheetChange event was being called twice. The first time it would filter on ‘6/22/2014′ as expected. Then it would run again (even though I clearly have turned off events) and would filter on ‘Date’ (the column header), which, of course, it can’t find in a column of actual dates.

I even tried to make my own event enabler/disabler, but it didn’t matter. Once I set OldValue to vbNullString, filtering on numbers and dates started working. The event procedure still gets called twice, but it doesn’t try to filter because OldValue isn’t there anymore.

That leaves a potential problem. If I type, say, “Montana” in B1 and enter using Ctrl+Enter rather than just Enter, the selection doesn’t change and OldValue is blank. Now, before selecting any other cells, if I type ‘Colorado’, nothing happens. That’s not a big problem for me because I have my options set to go down on enter and wouldn’t really use Ctrl+Enter in that case. But that doesn’t mean I like it. I don’t.

This hasn’t made it into my PMW yet, but I’d like to see where it can go.

You can download

A Daily Overdose of Arrays

snb, who wrote the detailed piece on dictionaries, has applied the same rigor to arrays.

I knew there would be something in there that I didn’t know (there was plenty). Under the section about identifying the existence of an item in an array, you can use a defined name: The Excel-object ‘Names’

Assign the array to a Name’.
Excel creates an arrayformula, consisting of a string.
The item can be searched for in that string.
Embed the search string in quotation marks.
Embed numerical values in commas.

Names.Add "snb_002", a_sn
msgbox InStr(Names("snb_002").Value, """abcde""")>0
msgbox InStr(Names("snb_002").Value, ",1245,")>0

snb’s VBA for Smarties: Arrays

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.