Keyboard Shortcut Metrics

You all know that I love keyboard shortcuts. There is a limit, though. Some of my custom shortcuts clear the Undo stack, which can be a real pain, so I have to balance the productivity gains of the shortcut against the side effects. Back in January, I wanted to see which shortcuts I’d been using, so I created some code to keep track. The code is below, but first the results.

No surprise to me, pasting special – values tops the list. I paste values by default unless I need to paste something else. I should really just hijack Ctrl+V. If you’re wondering why the count for this one is so low in the first two-week period, it’s because I have severely ingrained muscle memory with Alt+E+S+V to show the Paste Special dialog and select the Values option. I wasn’t really using Ctrl+Shift+V, the shortcut assigned to this macro. I made an effort to use in early February. I’m not sure if I’ve ever posted this code before, so it’s high time.

Sub CopyPasteValues()
   
    gclsAppEvents.AddLog "^+v", "CopyPasteValues"
   
    If TypeName(Selection) = "Range" And Application.CutCopyMode = xlCopy Then
        Selection.PasteSpecial xlPasteValuesAndNumberFormats
    ElseIf Application.CutCopyMode = xlCut Then
        If Not ActiveSheet Is Nothing Then
            ActiveSheet.Paste
        End If
    End If
   
End Sub

You can see that I like to paste the values with the number formats.

If you want to see the code for the other macros in the list, see MakeComma, SelectAdjacentCol, FrozenHome, Wrap Sheets, Formatting Taskpane, Increment Date, ChangeSign, FillSeries.

I can’t find where I ever posted my FillVirtualScreen, CopySum, or GetMappedAddress code, so I guess I should do that in a future post. I was surprised that ShowFormatting wasn’t higher. Also, I thought DecrementDate would have been more used that IncrementDate. And FillSeries only seven times in two months?

To keep track of all this, I created a CLog class and a CLogs class. CLog has LogID, DateTime, Keys, and ProcName getter/setter properties. CLogs is a typical parent class with one exception I’ll show in a bit. In my App class, I added an AddLog and WriteLog procedure.

Public Sub AddLog(ByVal sKeys As String, ByVal sProcName As String)
   
    Dim clsLog As CLog
   
    Set clsLog = New CLog
    clsLog.Keys = sKeys
    clsLog.ProcName = sProcName
    clsLog.DateTime = Now
   
    Me.Logs.Add clsLog
   
End Sub

Public Sub WriteLog()
   
    Dim sFile As String, lFile As Long
   
    If Me.Logs.Count > 0 Then
        sFile = ThisWorkbook.Path & Application.PathSeparator & "UIHelpers.log"
        lFile = FreeFile
       
        Open sFile For Append As lFile
        Print #lFile, Me.Logs.LogFileLines
        Close lFile
    End If
   
End Sub

I had to go to every procedure I wanted to track and add a call to AddLog. Then whenever my App class goes out of scope, the log is written.

Private Sub Class_Terminate()
    Me.WriteLog
End Sub

In CLogs, I return all the log lines as a big string to write out to the file.

Public Property Get LogFileLines() As String
   
    Dim aWrite() As String
    Dim clsLog As CLog
    Dim lCnt As Long
   
    If Me.Count > 0 Then
        ReDim aWrite(1 To Me.Count)
       
        For Each clsLog In Me
            lCnt = lCnt + 1
            aWrite(lCnt) = clsLog.LogFileLine
        Next clsLog
       
        LogFileLines = Join(aWrite, vbNewLine)
    End If
   
End Property

That calls CLog.LogFileLine

Public Property Get LogFileLine() As String
   
    Dim aWrite(1 To 3) As String
   
    aWrite(1) = Me.DateTime
    aWrite(2) = Me.Keys
    aWrite(3) = Me.ProcName
   
    LogFileLine = Join(aWrite, "|")
   
End Property

This file has a bunch of other stuff in it including half-finished ideas, but if you like…

You can download UIHelpers.zip

6 Comments

  1. David C says:

    The custom shortcuts I most often use are Ctrl+m ctrl+q and ctrl+m i.e. fill series shortcuts tailored for time series creations: m for monthly data, q for quarterly and y for yearly.
    Then I could not work without custom fill down and fill right (ctrl+shift+d and ctrl+shit+r) that expand array formulas by simply selecting the last cells of the array and the new additional range over which it has to be extended…

    Thanks for sharing you code.

  2. Chris Macro says:

    Dick, you are ridiculously awesome! It’s amazing what you can learn about yourself when you data mine your own activities. I just might have to do a little experimenting on myself. Great stuff!

  3. Jordan B says:

    To paste values, I find that the most convenient keyboard command is Menu+v. It keeps the feel of ctrl+v, and also keeps the undo stack.

  4. Chris Macro says:

    @Jordan B Isn’t that shortcut only available in the newer versions of Excel? I seem to remember not being able to use menu+v in 2007.

  5. Jon Acampora says:

    Very cool! I might just have to keep track of myself too. I’m pretty sure paste values is one of my most commonly used shortcuts. I put it in the first position on the QAT and use Alt+1 to paste values. I’d love to see how often I use it. Thanks for the code!

  6. […] the Daily Dose of Excel, Dick Kusleika used code that kept track of the keyboard shortcuts that he uses. You can copy his sample code, to see how it […]

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: