Keyboard Shortcut Metrics

Keyboard Shortcut Metrics

Majority of people working with computer systems love shortcuts on keyboard. Few of the shortcuts which are custom made can help you to clean Undo Stack, which is quite painful. You have to basically try to balance the shortcut’s gain on productivity and their side effects. You can make a code to track the shortcuts you use. The following article provides you with a code. For instance, take a look at a random result shown below:



You can see from the table that, pasting special is at the top of the list. The count is low during the 1st two weeks as the shortcut Alt+E+S+V was used to pop up the dialog of paste special as against the shortcut  Ctrl+Shift+V which is assigned to the macro. The latter shortcut was put in use in February.

The code is as follows:

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
        End If
    End If

End Sub

The values are pasted in number format. If you are interested in seeing the code for any other macros present in the list then see MakeComma, FillSeries, SelectAdjacentCol, ChangeSign, FrozenHome, Increment Date, Wrap Sheets and Formatting Taskpane

You can create a Clog class as well as a Clogs class to track all of them. Clog has LogID, ProcName getter/setter properties, DateTime and the keys.

CLogs is a parent class with deviation. You can add an AddLog as well as a 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

All you have to do is insert the call to the AddLog.  

Private Sub Class_Terminate()
End Sub

In CLogs, one can return the whole log lines as a huge sequence to write 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

It calls the 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