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.
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.
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.
Me.WriteLog
End Sub
In CLogs, I return all the log lines as a big string to write out to the file.
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
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