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
ElseIf Application.CutCopyMode = xlCut Then
If Not ActiveSheet Is Nothing Then
You can see that I like to paste the values with the number formats.
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
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
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()
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
LogFileLines = Join(aWrite, vbNewLine)
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, "|")
This file has a bunch of other stuff in it including half-finished ideas, but if you like…
You can download UIHelpers.zip