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.

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.

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.

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

That calls CLog.LogFileLine

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

You can download UIHelpers.zip

Opening the Addin Dialog like a Pro

Back in the old days when Excel had menus and toolbars, a guy could use Alt+t+i to open the Addins dialog (Tools – Addins). But that would only work if there was an open workbook. No open workbook, no dialog. Now in the days of the Ribbon, the shortcut is Alt+f+t a a Alt+g (File – Options – Addins – Go). You don’t need to have a workbook open, which is nice, but there is a bit of delay between the two “a’s” in the keyboard sequence.

MS did a wonderful thing when they made the old 2003 menu navigation still work in later versions. Even though there’s no longer a Tools menu, you can still use Alt+t+i to open the dialog. Unfortunately you still need to have a workbook open for it to work. I can’t imagine why that is, but it is.

Well, it’s VBA to the rescue. You can show most any dialog with Applicaiton.Dialogs().Show. But showing the Addins dialog returns an error if there is not an active workbook, just like with the old menus. It’s trivial enough to fix, to wit:

Sub ShowAddinDialog()

Dim wb As Workbook

'Dialog won’t show if there’s no workbook showing
If ActiveWorkbook Is Nothing Then
Set wb = Workbooks.Add
End If

'Show addin dialog
Application.Dialogs(xlDialogAddinManager).Show

'Close wb if it was created
On Error Resume Next
wb.Close False

End Sub

That creates a new workbook if needed, then shows the dialog. It keeps track of whether it created a workbook and, if so, closes it without saving. Hardly worth your time to read this post, you say? You already knew about this, you say? Here’s the real magic. Those old 2003 commandbars still lurk behind the scenes in Excel. If you create new ones, they show up on the Add-ins tab. But you can modify the existing one too. I put this little gem in the Auto_Open macro in the same workbook as my ShowAddinDialog procedure.

With Application.CommandBars(1).Controls("Tools").Controls.Add(msoControlButton, , , 1)
.Caption = "&I"
.OnAction = "ShowAddinDialog"
End With

And then to clean it up in Auto_Close.

On Error Resume Next
Application.CommandBars(1).Controls("Tools").Controls("I").Delete

Commandbars(1) is the menu and Controls(“Tools”) is the Tools menu. I add a new control to position 1 on that Tools menu. I don’t need a fancy caption because I can’t see it anyway. I just need a caption with I as the hotkey. Whichever letter follows the ampersand (&) is the hotkey. The built-in addins menu item has a caption of Add-&Ins... making I the hotkey for it. But mine is higher up, so it wins.

In the previous post I referenced above, I add this macro to the QAT. But the muscle memory of Alt+t+i dies hard. Rather than retrain myself like a normal person, I’m embracing my quirks. I can now use Alt+t+i and get the desired results.

Applying NumberFormat Version 4

I’ve been using a macro to apply the comma style for about five years. I’ve even enhanced it. It’s such a simple bit of code that it really never broke – until now. I got a workbook that caused the code to fail because it didn’t have a style named ‘Comma’. I just assumed that style was built in to all workbooks.

I received a workbook in 2003 format (.xls). I Saved As to 2010 format (.xlsx) because that’s what I always do. I even closed and re-opened it because I don’t like it when it says ‘Compatibility Mode’ in the title. Here’s what the Styles gallery looks like compared to the Styles gallery on a new workbook below.


OK, somebody deleted the number format styles. Seems strange, but I’ve seen stranger. Then more strangeness occurred. None of the cells on this unprotected worksheet were locked. And when I add a new worksheet to the workbook, every cell on the new worksheet was unlocked. I don’t recall ever seeing that before. Generally, every cell on a new worksheet is locked and the sheet is unprotected. That way when you protect a worksheet, you get expected behavior.

I still don’t understand why this workbook behaves the way it does, but I do need to change my code to account for it. I toyed with the idea of adding a Comma style if it didn’t exist, but the Comma style is just a number format, so I didn’t see any downside to just applying a number format instead of a style. Also, I’ve thoroughly enjoyed my new toggle feature for PivotItems that switches between zero and two decimal places and applied that same logic to cells.

Sub MakeComma()

Dim pf As PivotField

Const sONEDECIMAL As String = "#,##0"
Const sTWODECIMALS As String = "#,##0.00"
Const sCOMMAONE As String = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
Const sCOMMATWO As String = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"

gclsAppEvents.AddLog "^m", "MakeComma"

If TypeName(Selection) = "Range" Then
On Error Resume Next
Set pf = ActiveCell.PivotField
On Error GoTo 0

If pf Is Nothing Then
If Selection.NumberFormat = sCOMMATWO Then
Selection.NumberFormat = sCOMMAONE
Else
Selection.NumberFormat = sCOMMATWO
End If
Else
If pf.NumberFormat = sTWODECIMALS Then
pf.NumberFormat = sONEDECIMAL
Else
pf.NumberFormat = sTWODECIMALS
End If
End If
End If

End Sub

Changing the Sign of a Cell

You probably already know the trick for changing the signs on a bunch of cells. But if not, here it is:

  1. Type -1 into an unused cell on your worksheet
  2. Copy that cell
  3. Select the cells whose sign you want to change
  4. Paste Special – Multiply

I do this quite a bit. Recently when I had to enter a pretty long list of numbers, most of which were negative, I decided to enter them with their sign reversed (for ease of entry) and employ this technique. Then I thought that it was just too much work. So I wrote a macro. It doesn’t do exactly what the Copy – Paste Special – Multiply trick does, but arguably does it better.

Private Const msFORMADD As String = ")*-1"
Private Const msFORMST As String = "=("

Sub ChangeSign()

Dim rCell As Range

gclsAppEvents.AddLog "^+n", "ChangeSign"

If TypeName(Selection) = "Range" Then
For Each rCell In Selection.Cells
If CellCanChangeSign(rCell) Then
If rCell.HasFormula Then
If CellFormulaHasSignChange(rCell) Then
rCell.Formula = RemoveFormulaSignChange(rCell.Formula)
Else
rCell.Formula = Replace(rCell.Formula, "=", msFORMST, 1, 1) & msFORMADD
End If
ElseIf IsNumeric(rCell.Value) Then
rCell.Value = -rCell.Value
End If
End If
Next rCell
End If

End Sub

Function CellCanChangeSign(rCell As Range) As Boolean

CellCanChangeSign = rCell.Address = rCell.MergeArea.Cells(1).Address And Not IsEmpty(rCell.Value)

End Function

Function CellFormulaHasSignChange(rCell As Range) As Boolean

CellFormulaHasSignChange = Left$(rCell.Formula, Len(msFORMST)) = msFORMST _
And _
Right$(rCell.Formula, Len(msFORMADD)) = msFORMADD

End Function

Function RemoveFormulaSignChange(ByVal sFormula As String) As String

Dim sReturn As String

sReturn = Left$(sFormula, Len(sFormula) - Len(msFORMADD)) 'remove last characters
sReturn = Replace$(sReturn, msFORMST, "=", 1, 1) 'remove first paren

RemoveFormulaSignChange = sReturn

End Function

is the entry point procedure and is called with Ctrl+Shift+n (more on what AddLog is in a later post). After it determines that there is a range selected (and not a shape, for example), it calls

to make sure it’s OK to move forward. In

I try to deal with merged cells. I don’t use merged cells a ton, so I can never remember how to avoid problems in VBA when dealing with them. I decided that if the cell was the first cell in the merge area, that was good enough. If the cell isn’t merged, its MergeArea is equal to itself, so this would still return True. Also, I don’t want to do anything to empty cells so I make sure that’s not the case.

Once I verify that the cell is good to go, I treat cells with formulas different than cells with values. When you use Paste Special – Multiply, Excel takes whatever formula you have and changes the formula to multiply by -1. For example, =SUM(F6:F14) becomes =(SUM(F6:F14))*-1. I’m not sure why it doesn’t negate the formula with -(formula), but it doesn’t. I made mine match what Excel does.

One problem with changing the signs of formulas is that if you do it twice, then =SUM(F6:F14) becomes =((SUM(F6:F14))*-1)*-1. Of course what else could it do. It’s a multiplying operation, not a toggle. I tried to make mine a toggle at least where I could. If you monkey with the formula between operations, you may get multiple multiplications, but if you just run the code twice it will remove what it did. I’ve tested this nearly a half dozen times, so I’m sure it’s rock solid.

checks that

is at the start of the formula and

is at the end. If they are then

RemoveFormulaSignChange

 removes them and sets the formula back to its original.

For cells with numbers and not formula, the Value is simply negated.

Formatting Pivot Tables Version 3

I’ll try to tone down the hyperbole from the last post. It all started with a simple macro to format the selection with the comma style.

Sub MakeComma()

If TypeName(Selection) = “Range” Then
Selection.Style = “Comma”
End If

End Sub

I loved this little macro. Then I decided that formatting pivot tables was just as tedious as applying styles. I made Version 2, and I saw that it was good.

Sub MakeComma()

Dim pf As PivotField

If TypeName(Selection) = “Range” Then
On Error Resume Next
Set pf = ActiveCell.PivotField
On Error GoTo 0

If pf Is Nothing Then
Selection.Style = “Comma”
Else
pf.NumberFormat = “#,##0.00″
End If
End If

End Sub

The other day I was making a pivot table that used the Count aggregation. I don’t use Count anywhere near as much as Sum, but there I was. Showing two decimal places with Count isn’t the worst thing in the world, but I didn’t like it. Counting is done with whole numbers. Nor did I like changing the number format manually. Version 3 was born.

Sub MakeComma()

Dim pf As PivotField

Const sNODECIMALS As String = "#,##0"
Const sTWODECIMALS As String = "#,##0.00"

If TypeName(Selection) = "Range" Then
On Error Resume Next
Set pf = ActiveCell.PivotField
On Error GoTo 0

If pf Is Nothing Then
Selection.Style = "Comma"
Else
If pf.NumberFormat = sTWODECIMALS Then
pf.NumberFormat = sNODECIMALS
Else
pf.NumberFormat = sTWODECIMALS
End If
End If
End If

End Sub

My initial revision checked whether the PivotField.Function property was xlSum or xlCount and applied formatting appropriately. That felt too rigid. Once my fingers are on Ctrl+M, it really doesn’t matter to me how many times I have to press down (within reason of course). So I went with a toggle between two decimals and no decimals. Now I’m happy again.

To assign to a shortcut key, I have these two statements in my Auto_Open and Auto_Close procedures, respectively.

Application.OnKey "^m", "MakeComma"
Application.OnKey "^m"

New and Improved Wrap Sheets Hotkey

Hey, remember back in 2010 when I had that bright idea about a repurposing Ctrl+PgUp and Ctrl+PgDn? Sure you do. The idea was that when I’m at the end of a workbook with a lot of worksheets, it would be easier to hit Ctrl+PgDn and wrap around to the first sheet rather than hold down Ctrl+PgUp until I got to the first sheet. I gave that shortcut the ol’ college try, but in the end I abandoned it. It turns out that I like holding down Ctrl+PgUp to get to the first page. But when I do that and this utility is active, it blows right by the first page and wraps around.

There’s this thing called Fitts’ Law and it indicates some things about infinity

Fitts’ law indicates that the most quickly accessed targets on any computer display are the four corners of the screen, because of their pinning action, and yet, for years, they seemed to be avoided at all costs by designers.

Fitts’ Law deals with pointing devices and targets, but the same principle applies to hotkeys. In Excel, if you hold down Ctrl+PgDn for infinity, you will end up on the last visible sheet of your workbook. That’s powerful because it removes all of your precision responsibility. I don’t care how imprecise you are, you can certainly hit an infinitely wide barn. Contrast that with the File menu in Excel 2010. Press Alt+F and then hold the down arrow for infinity. If the UI designers had been paying attention you would end up on “Exit” (the last item on the list), but you don’t. The cursor just keeps looping through the items on the list.

Even though I abandoned the hotkey, I can’t seem to get it out of my head that it’s a good idea. I’m stubborn like that. What if I could have the best of both worlds? What if I could race to the end of a workbook by holding down Ctrl+PgDn, but still wrap around to the first sheet when I wanted to? I changed the code to add a little delay. I started with 1 second, but determined that 1/2 second works better. Now, if the time between the last time I pressed Ctrl+PgDn and now is greater that 0.5 seconds, sheet activation will wrap around to the first sheet. If it’s less than 0.5 seconds, it’s assumed that I’m looking for infinity and remains on the last worksheet. And it all works the same for Ctrl+PgUp, just in reverse. I start with a module level variable and a module level constant.

Private msnLastWrap As Single
Private Const msnWRAPBUFFER As Single = 0.05

The Single msnLastWrap will keep track of the last time I pressed the hotkey. The rest of the code is the same from the prior post except that I added a couple of If statements to check the time differential and of course to set msnLastWrap.

Sub WrapSheetsUp()

If ActiveSheet.Index = FirstVisibleSheetIndex Then
If Timer - msnLastWrap > msnWRAPBUFFER Then
ActiveWorkbook.Sheets(LastVisibleSheetIndex).Activate
End If
Else
ActiveWorkbook.Sheets(NextVisibleSheetIndex(False)).Activate
End If

msnLastWrap = Timer

End Sub

Sub WrapSheetsDown()

If ActiveSheet.Index = LastVisibleSheetIndex Then
If Timer - msnLastWrap > msnWRAPBUFFER Then
ActiveWorkbook.Sheets(FirstVisibleSheetIndex).Activate
End If
Else
ActiveWorkbook.Sheets(NextVisibleSheetIndex(True)).Activate
End If

msnLastWrap = Timer

End Sub

In WrapSheetsDown when it gets to the last sheet (ActiveSheet.Index = LastVisibleSheetIndex) it checks to see how much time has elapsed (Timer – msnLastWrap). Timer is a VBA function that returns the number of seconds since midnight. Regardless of the results of that test, I assign Timer to my module level variable.

The rest of the code is unchanged, but for completeness, here’s what I have in my Auto_Open and Auto_Close procedures respectively.

Application.OnKey "^{PGUP}", "WrapSheetsUp"
Application.OnKey "^{PGDN}", "WrapSheetsDown"

Application.OnKey "^{PGUP}"
Application.OnKey "^{PGDN}"

And the rest of the procedures needed.

Public Function FirstVisibleSheetIndex() As Long

Dim lReturn As Long
Dim sh As Object

For Each sh In ActiveWorkbook.Sheets
If sh.Visible Then
lReturn = sh.Index
Exit For
End If
Next sh

FirstVisibleSheetIndex = lReturn

End Function

Public Function LastVisibleSheetIndex() As Long

Dim lReturn As Long
Dim i As Long

For i = ActiveWorkbook.Sheets.Count To 1 Step -1
If ActiveWorkbook.Sheets(i).Visible Then
lReturn = i
Exit For
End If
Next i

LastVisibleSheetIndex = lReturn

End Function

Public Function NextVisibleSheetIndex(bDown As Boolean) As Long

Dim lReturn As Long
Dim i As Long

If bDown Then
For i = ActiveSheet.Index + 1 To ActiveWorkbook.Sheets.Count
If ActiveWorkbook.Sheets(i).Visible Then
lReturn = i
Exit For
End If
Next i
Else
For i = ActiveSheet.Index - 1 To 1 Step -1
If ActiveWorkbook.Sheets(i).Visible Then
lReturn = i
Exit For
End If
Next i
End If

NextVisibleSheetIndex = lReturn

End Function

Entering Times into Cells

I have a half-dozen things in my UIHelpers.xlam add-in that I really miss when I’m using someone else’s machine. One of them is this little gem from 2009: Entering Dates. When I have to actually type a date in a cell, my fingers get very angry with me.

I don’t work with times that often, but I’m doing a technical proofread of a book and it means I’m entering stop and start times several times a day (to keep track of how little I’m getting paid on an hourly basis). I enter times like 17:00 and that works well enough. But it’s not keyboard-sexy like my date entry is. So I set about fixing that.

Sub IncrementDate()

On Error Resume Next

If IsDate(ActiveCell.Value) Then
ActiveCell.Value = ActiveCell.Value + 1
ElseIf IsTextTime(ActiveCell.text) Then
ActiveCell.Value = ActiveCell.Value + 1 / 24 / 60
Else
ActiveCell.Value = Date
ActiveCell.NumberFormat = "m/d/yyyy"
End If

End Sub

Sub DecrementDate()

On Error Resume Next

If IsDate(ActiveCell.Value) Then
ActiveCell.Value = ActiveCell.Value - 1
ElseIf IsTextTime(ActiveCell.text) Then
ActiveCell.Value = ActiveCell.Value - 1 / 24 / 60
Else
ActiveCell.Value = Now - Int(Now)
ActiveCell.NumberFormat = "h:mm"
End If

End Sub

Public Function IsTextTime(sInput As String) As Boolean

IsTextTime = IsDate(sInput)

End Function

My first problem was determining if the cell contained a time. The internet says IsDate works equally well for dates and times, and that’s probably true for VB6, but it’s not the case for VBA in Excel.

IsDate doesn't work on times in Excel

I read a post somewhere by Andy Pope that said IsDate will return True on the Text property of cell. If I was going to make an IsTime function, it would look like this:

Public Function IsTime(rCell As Range) As Boolean

IsTime = IsDate(rCell.Text) And Not IsDate(rCell.Value)

End Function

I haven’t tested that extensively but I think it works. I generally don’t like passing ranges into functions that should only require a value. But since I need both Text and Value, it can’t be helped. In the code above, I made a special purpose function that takes a String because I already test for IsDate(.Value) in the calling procedure. It’s a cop out and I don’t like it, but here we are.

In the end, I’m able to control times similarly to how I control dates. Here’s how it works.

Cell is blank Cell is not a date Cell is a time Cell is a date
Ctrl+; Enter today Replace contents with today Increase time by 1 minute Increase date by one day
Ctrl+Shft+; Enter current time Replace contents with current time Descrease time by 1 minute Decreate date by one day

Get a Date’s Integer Value

Have you ever formatted a date as General to get the number, then formatted back to Date? Of course you have, it’s nothing to be ashamed of. I just learned a better way. Not surprisingly, I discovered this tip by accidentally hitting the wrong hotkey.

You know that Ctrl+' (apostrophe) will copy the formula from the cell above into the current cell. It doesn’t copy the value, but the formula. One side effect of that, is that formula of a date is its integer equivalent.

Press Ctrl+' below the date to see the number, then Esc to get rid of it. It doesn’t matter if you have something in the cell already because the Esc cancels whatever you were doing.